SQL Execute failed

Latha1919 · Post by **Latha1919** » Wed Sep 12, 2007 10:33 pm

Hi,

I am having an issue with one of the sub job in a controller job. Sub job is logging Warning messages as below. Its logging warning messages only but not causing any job abort.

emailLoad..emailLoadProcess.emailLoad: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO survey_email(email_to, email_from, email_cc, email_bcc, email_subject, email_charset, email_body, ci_survey_requestor_name, ci_sce_name, ci_cc_name, ci_project_survey_close_date) VALUES (?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC Driver Manager] Invalid argument value

email_to = "jenny.south@examhub.exch.cspss.com"
email_from = "eclient@eds.com"
email_cc = "jeremy.south@examhub.exch.cspss.com,mark.kizzar@examhub.exch.cspss.com,tim.harvey@examhub.exch.cspss.com"
email_bcc = "ServExcProductionSupport@exsc01.exch.cspss.com"
email_subject = "CSPSS Survey Request Notification"
email_charset = "iso-8859-1"
email_body =
ci_survey_requestor_name = "Anitha Sharma"
ci_sce_name = "Jenny South"
ci_cc_name = "Rajiv Joshi"
ci_project_survey_close_date = NULL

Please advise me on this. One more thing. Same project is available on another server, hence tried processing the same data. Data is processed w/o any warning messages.

Appreciate your help.
Thanks,

ray.wurlod · Post by **ray.wurlod** » Wed Sep 12, 2007 11:37 pm

Supply a value for email_body.

chulett · Post by **chulett** » Thu Sep 13, 2007 12:29 am

Was wondering about that. What kind of value would leave the entry completely blank like that? It's not null or it would say NULL like it does for the date. An empty string would show a pair of double-quotes I would think.

Or is this some kind of copy/paste error?

ray.wurlod · Post by **ray.wurlod** » Thu Sep 13, 2007 1:32 am

... or use Debugger or stage tracing to figure out what value was sent to email_body column.

There used to be a bug in ODBC (2.0 from memory) where too long a string for the max length of a VarChar would exhibit this kind of reporting behaviour.

However, on closer inspection, I note that the error message comes from the ODBC Driver Manager, so we may be barking up the wrong tree completely - you will also need to test whether the particular DSN is correctly configured.

Latha1919 · Post by **Latha1919** » Thu Sep 13, 2007 11:11 pm

When I execute this query in Query analyzer, its throwing error. However, if I modify the double quotes around the strings with single quotes, its working. Is this anything to do with double quotes??

chulett · Post by **chulett** » Thu Sep 13, 2007 11:37 pm

Obviously.

Databases seem to prefer single quoted strings.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 13, 2007 11:53 pm

That's probably because both the SQL CAE Access Group standards and the ODBC standards specify single-quoted string literals.

chulett · Post by **chulett** » Fri Sep 14, 2007 6:51 am

Well, there you go - it's one of them pesky standards. :D

Latha1919 · Post by **Latha1919** » Wed Sep 26, 2007 3:19 pm

That's probably because both the SQL CAE Access Group standards and the ODBC standards specify single-quoted string literals.

How this issue needs to be handled?

The same project is successfully running on Windows 2000 server, no warning messages & no insert fail statements at all. We are finding the issue only on Win 2003 server. Any suggestions.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Sep 26, 2007 4:18 pm

All complaints to billg@microsoft.com then. DataStage engineers can not be responsible for differences between the way the two Microsoft products work.

Latha1919 · Post by **Latha1919** » Wed Sep 26, 2007 4:31 pm

I agree with you, but is there a work around for this issue?