Data Truncation in ODBC Enterprise stage.

ds_infy · Post by **ds_infy** » Tue Dec 21, 2010 1:47 pm

Hi All,

I have a job, which extract data from MSSQL database server using ODBC enterprise stage. In the extract query I have field called NAME which is varchar (50) in the table and in the job I have defined it as varchar without any length. Now the issue is the data in this column is getting truncated. I am able to view only the first 26 character of the actual data.

Can anyone provide any pointers/inputs will be really helpful? Is there any environment setting that needs to be modified to resolve this issue?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 21, 2010 3:33 pm

Do you have any good reason for using unbounded VarChar? If not, use VarChar(50) and you will guarantee sufficient storage for all characers in the string.

ds_infy · Post by **ds_infy** » Wed Dec 22, 2010 12:13 am

ray.wurlod wrote:Do you have any good reason for using unbounded VarChar? If not, use VarChar(50) and you will guarantee sufficient storage for all characers in the string.

I have tried with VarChar(50) option. But still issue is same.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 22, 2010 2:23 am

Then you're going to have to find out where the truncation is occurring. For example, is there a SUBSTRINGS function in the extraction SQL? Is there some point in the job where the field is a VarChar(26)? DataStage will NOT exhibit this behaviour unless it is programmed to do so.

ds_infy · Post by **ds_infy** » Wed Dec 22, 2010 5:54 am

ray.wurlod wrote:Then you're going to have to find out where the truncation is occurring. For example, is there a SUBSTRINGS function in the extraction SQL? Is there some point in the job where the field is a VarChar(26)? DataStage will NOT exhibit this behaviour unless it is programmed to do so.

Hi There is no substring function used in this SQl. It is a direct extract of that name column from the table. I have verified that job, nowhere its varchar(26). I am getting this truncated data even if i try to view data directly from the ODBC enterprise stage.

chulett · Post by **chulett** » Wed Dec 22, 2010 7:42 am

Is your "varchar(50)" in SQLServer 50 bytes or 50 characters? You may have a 'byte semantics' issue with a multi-byte character set. What is/are the charactersets involved here?