SQL SERVER - Character missing in Datasatge

parameswar · Post by **parameswar** » Fri Oct 12, 2007 8:32 am

Hi,

We are extracting data from SQL Server using odbc stage. In datastage we are getting data , but from few fields it is truncating one or Two characters. If we will extract data by appending one or two character, we are getting correct data in datastage

Example: Instead of RES_CODE if we will retrive as below data is coming fine otherwise it is truncating last one character . similarly in case of two missing character we have to append two spaces.
LTRIM(RTRIM(RES_CODE + ' ' )) AS RES_CODE

What might be the reason for it? Can we retrive data without appending spaces in field ?

Thanks,
Parameswar

chucksmith · Post by **chucksmith** » Fri Oct 12, 2007 12:21 pm

Could this be a data type issue, such as moving a CHAR column to a VARCHAR column?

parameswar · Post by **parameswar** » Sat Oct 13, 2007 5:05 am

chucksmith wrote:Could this be a data type issue, such as moving a CHAR column to a VARCHAR column?

Hi,

Even if I am changing datatype from char to varchar , I am missing one character from last of the column

. Is there any other reason for this truncation...

Thanks,
Parameswar

ray.wurlod · Post by **ray.wurlod** » Sat Oct 13, 2007 9:05 am

Is this through ODBC and, if so, what is the total size of the row? There is a tunable limit (MAXFETCHBUFF) to row size in Data Direct ODBC drivers.

parameswar · Post by **parameswar** » Sun Oct 14, 2007 9:31 pm

ray.wurlod wrote:Is this through ODBC and, if so, what is the total size of the row? There is a tunable limit (MAXFETCHBUFF) to row size in Data Direct ODBC drivers.

Hi Ray,

I am using ODBC to extract data from SQL server. Last characters are truncationg from few fields, not from all fields. Could you please help me how to know the limit of MAXFETCHBUFF if I can change the default value and will try.

Thanks,
Parameswar

ray.wurlod · Post by **ray.wurlod** » Sun Oct 14, 2007 10:31 pm

The default value of MAXFETCHBUFF is 8192 (bytes). I am not aware of any limit. However, if characters are being truncated from multiple fields, it is not MAXFETCHBUFF that is at fault.

Can you enable ODBC tracing, and determine what SQL is being sent to the database server and what data are being sent from the database server? Tip: restrict the number of rows process, lest your trace file becomes too huge.