Need Help

thespecialist · Post by **thespecialist** » Thu Oct 04, 2007 6:06 pm

I am getting the following warning and the Job is aborting. It was running fine until yesturday, very strange -

This is a straight forward Job

SEQ File ______________>Transformer _________> SEQ File
|
|
ODBC Stage(SQL Server)------|

Following is the Warning message:

TABLERetrieval..Transformer_22.NotProcessed: DSD.BCIGetByKey call to SQLExecute failed.
SQL statement:SELECT TABLE.INS_AGRMNT_NO, TABLE.ISSUING_COMPANY_NO, TABLE.POLICY_NO, substring(convert(char(10),TABLE.POLICY_EFCTV_DT,110),7,4) + substring(convert(char(10),TABLE.POLICY_EFCTV_DT,110),1,2) + substring(convert(char(10),TABLE.POLICY_EFCTV_DT,110),4,2) INPUT_PED, TABLE.POLICY_EFCTV_DT, TABLE.BOOKED_DT, TABLE.CONTRACT_NO, TABLE.CONTRACT_EFF_DT, TABLE.IR_LOS_PYMT_PLN_CD, TABLE.CAPTIVE_IN, TABLE.CREDIT_FEE_AM, TABLE.INVOICE_DUE_DT, TABLE.NON_DED_ESCROW_AM, TABLE.DED_ESCROW_AM, TABLE.BILLING_OPTION_CD, TABLE.BILLING_NM, TABLE.STREET_ADR_TX, TABLE.STREET_ADR2_TX, TABLE.CITY_NM, TABLE.STATE_CD, TABLE.ZIP_CD, TABLE.BHS_IND_PRCSSD_CD, TABLE.BHS_IND_PRCSSD_TS, TABLE.BHS_BADR_PRCSSD_CD, TABLE.BHS_BADR_PRCSSD_TS, TABLE.CREATE_USER_ID, TABLE.CREATE_TS, TABLE.UPDATE_USER_ID, TABLE.UPDATE_TS,TABLE.BILLING_CONTACT FROM TABLE WHERE TABLE.BHS_IND_PRCSSD_CD is NULL AND (ISSUING_COMPANY_NO = ? AND POLICY_NO = ? AND POLICY_EFCTV_DT = ?) ORDER BY TABLE.UPDATE_TS
SQLSTATE=22008, DBMS.CODE=241
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

Help will be greatly appreciated
Thanks in advance

ArndW · Post by **ArndW** » Thu Oct 04, 2007 7:21 pm

What is the type and contents of the column "POLICY_EFCTV_DT" in your job?

thespecialist · Post by **thespecialist** » Thu Oct 04, 2007 8:49 pm

ArndW

"POLICY_EFCTV_DT" is a datetime field in SQL SERVER Data base and timestamp(23,3) in DataStage, I am creating a column called "INPUT_PED" for populating the POLICY_EFCTV_DT in YYYYMMDD format , its a VARCHAR( 8 ) field in DataSatge.

Thanks

ArndW · Post by **ArndW** » Thu Oct 04, 2007 9:03 pm

I don't know if sql server can do a "Convert(datetime ,'20071005')", that would explain your problem.

DeepakCorning · Post by **DeepakCorning** » Mon Oct 08, 2007 1:56 pm

As far as I know the 110 style changes the date to "mm-dd-yy" format and all the substrings that you are using according to this format look wrong. Try using 101.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 08, 2007 3:00 pm

You also need some help in constructing appropriate subjects for your threads, which will prove useful to future searchers.