Data Truncation Warning

urshit_1983 · Post by **urshit_1983** » Tue Aug 22, 2006 4:22 pm

Explain in detail so we can get idea. wot u want exactly ?

Is the string a part of any of ur field and you want to retain the whole string ??

chulett · Post by **chulett** » Tue Aug 22, 2006 4:39 pm

You are getting a Data Truncation warning from DataStage or SQL Server? Can you post the actual warning?

thumsup9 · Post by **thumsup9** » Tue Aug 22, 2006 4:40 pm

I want to avoid the Warning message. My problem is, Even though I am doing a substring and a trim, I am getting data truncation warning. Why is this happening, am I missing many things here..

Is the string a part of any of ur field and you want to retain the whole string ??

Yes, I would like to retain this unless its not possible..

Thanks,

thumsup9 · Post by **thumsup9** » Tue Aug 22, 2006 4:43 pm

chulett wrote:You are getting a Data Truncation warning from DataStage or SQL Server? Can you post the actual warning?

TestConsumer..stgxConsumer.lnkInsertConsumer: DSD.BCIPut call to SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated

Chulet-- pls let me know if more information is needed

kris007 · Post by **kris007** » Tue Aug 22, 2006 5:10 pm

Which stage are you using to connect to SQLServer?

chulett · Post by **chulett** » Tue Aug 22, 2006 5:19 pm

Your issue is on the SQL Server side. The size of the data in the job itself isn't an issue or you would get a different warning. Meaning, you've defined it as a Varchar(255) in the job and the data there isn't greater than 255 characters.

So, that could mean a couple of things. The actual target field in SQL Server is smaller than that, so the database catches the error not the job. The other thing might be an NLS issue, where single characters in your source become multiple characters. What are your NLS_LANG settings in DataStage versus your target database?

DSguru2B · Post by **DSguru2B** » Tue Aug 22, 2006 7:05 pm

The issue is certainly at the database end. It allows a certain amount of bytes but more is being inserted. Its just a warning and i believe the data is getting inserted. Check the table and see at what point it chops off the rest of the data.

kommven · Post by **kommven** » Tue Aug 22, 2006 7:41 pm

Try

Code: Select all

Oconv(description,"MCP")[0,200]

and increase the value till you get error and fix it...