Server Jobs appear to be dropping quotes from code

[jdmiceli]

Hi all!

Our Int system appears to have developed an issue with quotes around date parameters, in this case the #ProcessDate#. When cycles are running, many of these server jobs are failing with syntax errors that we have not experienced often.

I have about 400 server jobs that have the same bit of code in them for auditing purposes that has been running just fine for over a year, until recently. The original code looks like this:

Code: Select all

set ansi_nulls on ;
set ansi_warnings on ;

insert into #StgDatabaseName#.#SrcSchemaName#.ldr_cycle_status values
(#BatchNumber#, 'DS #TableName# Extract Started', getdate(), #ProcessDate#, #CompanyNumber#)

The #ProcessDate# parameter gets set in the job control shell script and it's code looks like this:

Code: Select all

`cat "${TmpStep1}"| sed -e "s/ProcessDate.*/ProcessDate=\"${ProcessDate}\"/" > "${TmpStep2}"`

This would couch the #ProcessDate# between double quotes and when it came into DataStage those double quotes would be interpretted as single quotes when the RDS made the connection to SQL Server 2000 to add the audit row. I have never understood why that happens, it just worked so I figured 'do not fix what is not broken.' Until recently this has worked fine.

We have been having continued issues with -14 errors (resource time-outs) and a change was suggested by our admins to fix this. The change was implemented and now every job that has the audit code above fails. The quotes are now gone from the date, which is considered an error coming into SQL Server. I then went through and hard coded single quotes around all references to the #ProcessDate# in the audit code, recompiled and cleaned up the environment. Upon rerunning, I get the same errors.

One of the error logs looks like this:

Code: Select all

Parse_FileSKeys_ADM_USER.23_ADM_USER.src_bin_ADM_USER: Executing SQL statement 'set ansi_nulls on ; set ansi_warnings on ;  

insert into bsd_int_prep.dbo.ldr_cycle_status values (1167, 'DS ADM_USER Extract Started', getdate(), 2009-10-05 16:56:37.643, 23) 

Note the missing quotes around the value for #ProcessDate#. Those single quotes were hard coded in, but at runtime were dropped. I do believe that the branded ODBC driver was updated on this box because we are preparing to upgrade SQL Server 2000 to SQL Server 2008. I suspect that some default setting has changed, but no one seems to be able to tell me what it was, what it should be or how to fix it. I had thought it might be the QuotedID setting in the '.odbc.ini' file, but Prod and Int systems both have the same settings (QuotedID=No).

Our production box is running the same code as this one was before the 'improvements' and is not failing at all. I also do not believe the new driver has been installed on that server.

I would appreciate any input you may have and thanks in advance!

[chulett]

We have been having continued issues with -14 errors (resource time-outs) and a change was suggested by our admins to fix this. The change was implemented and now every job that has the audit code above fails.

Am I missing something or do you not mention what the 'fix' was that was suggested and implemented?

[jdmiceli]

Hi Craig,

I knew you would be the one to ask that question.

I wrote this up late last night and I did not have access to our DS Admins to find out the specifics of the change that was made. I fully intend to get it and post it up, but they are all in company core meetings until the end of the day.

I promise I'll post that info as soon as I can.