Error while loading in to SQL SERVER table

rgr · Post by **rgr** » Tue Sep 03, 2013 8:12 am

Hi,
We are loading the data from seq file to SQL server table and getting the below error randomly when we run the job again after failure with out any changes this is going to run fine. I am not sure what could be the Issue. please some body let me know what could be the reason for this random error and let me know if any more details needed.

SQLSTATE=22008, DBMS.CODE=241
[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

Thanks,
Rgr

ArndW · Post by **ArndW** » Tue Sep 03, 2013 9:16 am

While I cannot address the sporadic problem, I would recommend doing your string-to-date or string-to-time conversion explicitly in your DataStage job and only sending a date/time/datetime to SQL server. In your job you can use the IsValid() function to ensure that your source string is indeed valid instead of letting a default conversion outside of DataStage detect the problem.

By doing this you might also find the source of your bad data and sporadic problem.

chulett · Post by **chulett** » Tue Sep 03, 2013 9:16 am

You need to detail for us your job design. Specifically wondering how you are handling any date/time data types - are you reading them as strings and converting them in the Transformer? Doing any validity checks?

ray.wurlod · Post by **ray.wurlod** » Tue Sep 03, 2013 2:42 pm

To use ODBC the string needs to be in ODBC-legal format, specifically CCYY-MM-DD for dates. If, for example, you are reading a string that contains a timestamp and trying to implicitly convert it into a date, you will get this kind of error.

rgr · Post by **rgr** » Wed Sep 04, 2013 8:07 am

Thank You all, Currently I am using default conversions with out any valid checks. I will make the changes to job to use validity checks.

chulett · Post by **chulett** » Wed Sep 04, 2013 10:17 am

We still haven't come anywhere close to solving the issue of why after it fails it reruns successfully with the same data. Adding in explicit conversions and validity checks could help narrow that down, be sure to come back once you've done that and let us know where things stand.

rgr · Post by **rgr** » Wed Sep 11, 2013 8:36 am

I have made changes to job currently job is running fine but not sure when this random conversion error is going to popup. for now I'll close this post and open new if If I receive this error again. Thank you all for your time.