SQL SERVER – SQL Agent Job and Backslash – Strange Behavior

Today, I am going to discuss a very interesting scenario which I encountered while working with one of the largest European financial technology organizations Comprehensive Database Performance Health Check earlier last week. I would be open with you that I have not heard about this behavior since I started my journey with SQL Server. Today’s blog post is about SQL Agent Job and Backslash.

SQL SERVER - SQL Agent Job and Backslash - Strange Behavior backslash-800x265

I was surprised to see this behavior. Here are the steps to reproduce this behavior.

Step 1

Create a database and table in it.

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO
CREATE TABLE ToBeDeleted(iID INT, vPath VARCHAR(100))
INSERT INTO ToBeDeleted VALUES(1,'\\FileServer001\MyFolder\')
INSERT INTO ToBeDeleted VALUES(2,'\\FileServer001\MyFolder2\')
-- verify the rows
Select * from ToBeDeleted

Step 2

Create a job in SQL Agent having below T-SQL script. Job name given was “MyJob”.

DELETE FROM MyTestDB..ToBeDeleted
-- Delete from table where path is \\FileServer001\MyFolder\
WHERE vPath= '\\FileServer001\MyFolder\'

Step 3

Run the job manually.

What do you expect to happen when job executes? It should delete ONE row where the path is matching?

You would be surprised to see actual results. You would see BOTH rows deleted from the table.

Here is the proof.

SQL SERVER - SQL Agent Job and Backslash - Strange Behavior Backslash

SELECT * FROM MyTestDB..ToBeDeleted
GO
EXECUTE msdb..sp_start_job 'MyJob'
GO
WAITFOR DELAY '00:00:05' -- waiting for 5 seconds to job to complete.
GO
SELECT * FROM MyTestDB..ToBeDeleted
GO

Do you know why this happened and what is the role of backslash? Comment and let me know your thoughts.

Comprehensive Database Performance Health Check is my MOST popular service. Customers opt for this service when they face performance issues with SQL Server. Here are six-part blog post series I have written based on my last 10 years of experience helping with Comprehensive Database Performance Health Check.

Here are a few additional blog posts which you may find interesting:

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class
Next Post
Winners and Solution – DELETE Qualified Rows From Multiple Tables

Related Posts

4 Comments. Leave new

  • Backslash break the comment part in multi line , so the Where clause is included in the comment part. In this way, the DELETE , will remove all records

    Reply
  • Great observation here.

    sabin is correct. In an SQLAgent job, a backslash at the end of a line will negate the carriage return/newline and pull the line below it up as part of that line. I experimented a bit and found out something. If you add a character after the backslash, even a space, then the backslash doesn’t cause this Strange Behavior. Also you don’t have to run the job for this to happen. Just saving the job will cause the effect. I guess if you need to put a comment in a job then either don’t use special characters in the comment or use the proper commenting like /** your comment **/.
    And always include a space at the end to make sure. After you save the job, open it back up and make sure the text is still formatted the way you intended.

    Reply
  • Yes the backslash at the end merges the below where cluase , if you want proof then execute the job for the first time. You will see that both rows are deleted , then go tojob monitor and see the step you added, you will find that the where cluase got commented due to backslash at the end in comment part

    Reply
  • This is exactly the behavior of a multiline statement in visual basic or in powershell. When adding a backslash, we are giving the instruction that the line is not finished yet.

    Reply

Leave a Reply

Menu