SQL Agent Job Backslash Puzzle: Why Both Rows Were Deleted

A SQL Agent job backslash can turn a one row delete into a delete of the whole table. The cause is one character at the end of a comment line.

The Puzzle

A table holds two network paths. A SQL Server Agent job deletes the row for one of them. The job step has three lines. The first line is the delete. The second line is a comment that names the path. The third line is the WHERE clause.

This SQL Agent job backslash case starts with data. The paths in the table end with a backslash, as folder paths do. The first script creates a database named AgentBackslashDemo. It holds the table and a procedure that puts the two rows back.

IF DB_ID(N'AgentBackslashDemo') IS NULL CREATE DATABASE AgentBackslashDemo;
GO
USE AgentBackslashDemo;
GO
DROP TABLE IF EXISTS dbo.ToBeDeleted;
CREATE TABLE dbo.ToBeDeleted (iID int, vPath varchar(100));
GO
CREATE OR ALTER PROCEDURE dbo.ResetRows
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE dbo.ToBeDeleted;
    INSERT INTO dbo.ToBeDeleted (iID, vPath) VALUES (1, '\\FileServer001\MyFolder\'), (2, '\\FileServer001\MyFolder2\');
END;
GO
EXEC dbo.ResetRows;

SELECT iID, vPath FROM dbo.ToBeDeleted ORDER BY iID;

The second script creates one job with documented procedures. The first step deletes the row whose path is the first one. The comment on the second line repeats that path, so the next person knows what the step does. The line breaks are carriage return and line feed, the usual Windows line break.

The second step holds the same text with the lines already joined. It is there for the scan query later. The job has no schedule, so it never runs by itself. The cleanup at the end removes it. Run the script on a test server.

USE msdb;
GO
EXEC dbo.sp_add_job @job_name = N'AgentBackslashJob', @enabled = 1;
GO
DECLARE @command nvarchar(max) = N'DELETE FROM dbo.ToBeDeleted' + NCHAR(13) + NCHAR(10)
    + N'-- Delete from table where path is \\FileServer001\MyFolder\' + NCHAR(13) + NCHAR(10)
    + N'WHERE vPath = ''\\FileServer001\MyFolder\''';
EXEC dbo.sp_add_jobstep @job_name = N'AgentBackslashJob', @step_name = N'Delete one path', @subsystem = N'TSQL', @database_name = N'AgentBackslashDemo', @command = @command;
SET @command = N'DELETE FROM dbo.ToBeDeleted' + NCHAR(13) + NCHAR(10)
    + N'-- Delete from table where path is \\FileServer001\MyFolder\'
    + N'WHERE vPath = ''\\FileServer001\MyFolder\''';
EXEC dbo.sp_add_jobstep @job_name = N'AgentBackslashJob', @step_name = N'Delete one path, joined', @subsystem = N'TSQL', @database_name = N'AgentBackslashDemo', @command = @command;
EXEC dbo.sp_add_jobserver @job_name = N'AgentBackslashJob';

With the Agent service stopped, the call that attaches the job to the server prints a note. Agent cannot be told about the job. The job still exists. Express edition has no Agent at all.

The job should delete one row, because the WHERE clause names one path. When the job runs on a server where Agent is running, the table ends up empty. Both rows are gone. The puzzle asks why. Think about the last character of line two before you read on.

The Answer

In the original case, SQL Server Agent treated a backslash at the end of a line as a line continuation. People who tried it again report the same. Agent removes the backslash and the line break. Then it joins the next line to the current one. The comment on line two ends with the path, and the path ends with a backslash. The WHERE clause on line three becomes part of the comment.

What reaches SQL Server is a delete with no WHERE clause. A delete with no WHERE clause removes every row. The behavior belongs to Agent, not to T-SQL. The test server has no running Agent. So the next script runs the same text in a query window. It runs it once as written and once with the line break removed.

USE AgentBackslashDemo;
GO
EXEC dbo.ResetRows;

DECLARE @sql nvarchar(max) = N'DELETE FROM dbo.ToBeDeleted' + NCHAR(13) + NCHAR(10)
    + N'-- Delete from table where path is \\FileServer001\MyFolder\' + NCHAR(13) + NCHAR(10)
    + N'WHERE vPath = ''\\FileServer001\MyFolder\''';
EXEC (@sql);

SELECT N'Line break kept' AS TextRun, COUNT(*) AS RowsLeft FROM dbo.ToBeDeleted;

EXEC dbo.ResetRows;

SET @sql = N'DELETE FROM dbo.ToBeDeleted' + NCHAR(13) + NCHAR(10)
    + N'-- Delete from table where path is \\FileServer001\MyFolder\'
    + N'WHERE vPath = ''\\FileServer001\MyFolder\''';
EXEC (@sql);

SELECT N'Line break removed' AS TextRun, COUNT(*) AS RowsLeft FROM dbo.ToBeDeleted;

With the line break in place, one row is left. The comment ends at the line break, and the WHERE clause works. With the line break removed, no row is left. The comment swallowed the WHERE clause, and the delete took everything.

The procedure stores the step text exactly as you give it. The scan query below reads that stored text. It lists the T-SQL job steps that have a backslash right before a line break. It was tested on a step created with sp_add_jobstep. It reads only, so it is safe on a production server.

SELECT j.name AS JobName, js.step_id AS StepID, js.step_name AS StepName
FROM msdb.dbo.sysjobsteps AS js
JOIN msdb.dbo.sysjobs AS j ON j.job_id = js.job_id
WHERE js.subsystem = N'TSQL'
  AND (js.command LIKE N'%\' + NCHAR(13) + N'%' OR js.command LIKE N'%\' + NCHAR(10) + N'%')
ORDER BY j.name, js.step_id;

The step still holds the backslash and the line break, so the scan finds it. On your own server, a step in the list deserves a look. Not every match is a bug. A match is a place where a following line could be joined to the one before it.

People who edit a step in the job dialog report that saving the job is enough to join the lines. No run is needed. A step saved that way no longer has a backslash before a line break, so the first scan misses it. The second scan looks for the damage instead. It finds a comment that runs straight into the keyword WHERE.

SELECT j.name AS JobName, js.step_id AS StepID, js.step_name AS StepName
FROM msdb.dbo.sysjobsteps AS js
JOIN msdb.dbo.sysjobs AS j ON j.job_id = js.job_id
WHERE js.subsystem = N'TSQL' AND js.command LIKE N'%--%\WHERE%'
ORDER BY j.name, js.step_id;

Only the second step matches, because it holds the joined text. Add other keywords after WHERE if your steps use them. Then open a saved step in the job dialog and check that its lines are still separate. The same people report that a space, or any other character, after the backslash stops the join.

Safe Ways to Write the Step

The first fix is to keep a backslash from ending any line. Move the path inside the sentence of the comment, or add words after it. A block comment from /* to */ also works, because the line then ends with the closing characters. Or leave the path out of the comment.

The second fix is a guard. A delete that must touch one row can say so. It checks the row count and rolls back when the count is wrong. The script below runs the joined text of the puzzle inside a guard.

EXEC dbo.ResetRows;

DECLARE @guarded nvarchar(max) = N'BEGIN TRANSACTION;' + NCHAR(13) + NCHAR(10)
    + N'DELETE FROM dbo.ToBeDeleted' + NCHAR(13) + NCHAR(10)
    + N'-- Delete from table where path is \\FileServer001\MyFolder\'
    + N'WHERE vPath = ''\\FileServer001\MyFolder\'';' + NCHAR(13) + NCHAR(10)
    + N'IF @@ROWCOUNT > 1 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION;';
EXEC (@guarded);

SELECT N'Guard on joined text' AS TextRun, COUNT(*) AS RowsLeft, @@TRANCOUNT AS OpenTransactions FROM dbo.ToBeDeleted;

The delete removed both rows, saw that the count was 2, and rolled back. Both rows are still there, and no transaction stays open. The guard lines come after the join, so they survive. A guard turns a silent disaster into a step that does nothing.

You could argue that nobody ends a comment with a backslash. Paths do, and a comment that quotes a path ends with one. The pattern is easy to write and hard to see.

What to Remember

A SQL Agent job backslash at the end of a line can merge two lines. Check job steps for a backslash before a line break. Keep comments from ending with a path, and guard every delete that must touch a known number of rows. Do not trust a step because it works on a test server where nobody wrote a path in a comment.

When you finish with the puzzle, run the cleanup script. It removes the job and the database.

USE msdb;
GO
EXEC dbo.sp_delete_job @job_name = N'AgentBackslashJob';
GO
USE master;
GO
IF DB_ID(N'AgentBackslashDemo') IS NOT NULL
BEGIN
    ALTER DATABASE AgentBackslashDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AgentBackslashDemo;
END;

A comment is not harmless text, it is text the tool still reads.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.