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.
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.
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:
- Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class
- SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery
- SQL SERVER – Relating Unrelated Tables – A Question from Reader
- SQL SERVER – Puzzle – Why Such a Complicated Execution Plan for a Single Row, Single Table Query?
- SQL SERVER – SSMS 17.7 Observation – Two Miscellaneous Category in Database Property Options
Puzzle – Datatime to DateTime2 Conversation in SQL Server 2017
Reference: Pinal Dave (https://blog.sqlauthority.com)
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
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.
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
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.