Pay Attention to What Script...To Generates

Introduction

I hate it when stuff doesn't go as expected.

Background

So there I was (at work), performing some maintenance on a SQL Server 2008R2 database, which required me to generate a script that creates a job (from an existing job) on the server. This particular job contains four package steps, followed by three SQL query steps. I generated and ran the script, changed some dtsx package names, and then went to test the job. Imagine my surprise when the job failed on the first SQL query step (which I did NOT change).

SQL Server generated the the script, and this is supposed to be the SQL query executed by the step in question (and does NOT look like the query as it appeared in the job I was essentially copying):

update DBName1.dbo.Table1 set Current_DMHRSI='True'
FROM DBName2.dbo.Table2 
where convert(numeric,[month])+convert(numeric,[year])*100>(select
convert(numeric,Max(convert(numeric,[month])+convert(numeric,[year])*100)-10
0) from DBName2.dbo.Table2) 
--  and department_id=@Department_RP
  and[skill type] in (1,2)
  and [Task Service Type]='AVAILABLE'
--  and [dmis id] in
('0117','5443','6119','7135','7136','7323','0109') 

Notice that line four appears to end with "10", and line five starts with "0". Well, it doesn't take a rocket scientist to figure out what the script generator did. The problem appears to be that the script generater breaks lines of its own volition at column 77 or so if it doesn't find a space or hard linefeed at or before that character position. There are other problems in the generated script regarding some of the comments (it broke a comment line mid-line, again generating invalid SQL code. After adding spaces between operators and in parameterized function calls, and making sure each line ended with a space, this is what the script generator gave me. (Admittedly, I also took steps to make the lines as short as was reasonable, but still, I shouldn't have had to do any of this.)

update DBName1.dbo.Table1 
set Current_DMHRSI = 'True' 
FROM DBNAme2.dbo.Table2 
where convert(numeric, [month]) + 
convert(numeric, [year]) * 100 > 
( 
select convert(numeric, 
Max(convert(numeric, [month]) + 
convert(numeric, [year]) * 100) - 100) 
from DBNAame2.dbo.Table2 
) 
--  and department_id = @Department_RP  
  and[skill type] in (1,2) 
  and [Task Service Type] = 'AVAILABLE' 
-- and [dmis id] in 
-- ('0117','5443','6119','7135','7136','7323','0109') 
and [National Identifier] = SSN; 

Tips

0) Don't be a super-optimizer thinking it's going to make your query faster. Put spaces between operators and after commas in functions that take multiple parameters.

1) ALWAYS check queries generated by the script to menu item to make sure it didn't put an arbitrary linebreak in the middle of your expressions.

2) My personal preference is to put all of my queries into stored procedures, and then execute the stored procedure from the job (step). If that had been done in this instance, this would not have come up.

History

• 22 Sep 2017 - Initial posting.