Click here to Skip to main content
14,037,547 members
Click here to Skip to main content
Add your own
alternative version

Stats

3.4K views
Posted 22 Sep 2017
Licenced CPOL

Pay Attention to What "Script...To" Generates

, 22 Sep 2017
Rate this:
Please Sign up or sign in to vote.
It doesn't always happen the way you expect.

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.
     

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

#realJSOP
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.190424.1 | Last Updated 22 Sep 2017
Article Copyright 2017 by #realJSOP
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid