Data Truncates While Writting to DB(SQL Server)

kumar_s · Post by **kumar_s** » Mon Aug 21, 2006 9:19 pm

View the input data in a test viewer(Hex editior).
Check whether you got some special character as end of record.
Also check the setting in the sequintial satage, about recored delimeter.

eze_ke · Post by **eze_ke** » Tue Aug 22, 2006 7:51 am

Hi

I checked the Input Data,which is fine.No special charaters
Also tryed hard coding the values in transformer stage but still i get the truncated data in table

all last charcters of character fields are being truncated

please suggest

kcbland · Post by **kcbland** » Tue Aug 22, 2006 9:28 am

What is the purpose of the Transformer stage? Can you add a Sequential output and verify the results are correct?

eze_ke · Post by **eze_ke** » Tue Aug 22, 2006 9:59 am

i get the out put in the sequential file as expected (no truncation)
its only when iam trying to load in SQL Server (Table)

kduke · Post by **kduke** » Tue Aug 22, 2006 1:49 pm

Are you sure that one is not a nvarchar?

eze_ke · Post by **eze_ke** » Mon Aug 28, 2006 3:04 pm

i still have the same problem Data Truncation while loading into SQL Server please suggest

eze_ke · Post by **eze_ke** » Tue Aug 29, 2006 1:28 pm

when i use DS Server Job iam able to Load with out any truncation
iam using SQL Server 7.0(Dynamic RDBMS Stage)
please Suggest

eze_ke · Post by **eze_ke** » Wed Aug 30, 2006 6:04 am

Hey guys no one has suggestions nor has come accross like this

please Suggest

ArndW · Post by **ArndW** » Wed Aug 30, 2006 6:10 am

Is the definition CHAR(7) that you have from the DS job or have you done a describe on the actual Database tables? Also, if you increase your table definition in the DDL does the truncation still happen (just one position later)?

ray.wurlod · Post by **ray.wurlod** » Wed Aug 30, 2006 5:58 pm

eze_ke wrote:Hey guys no one has suggestions nor has come accross like this

please Suggest

This is not a premium support site. Folks post as and when they can. Don't push. If you want urgent answers, sign up for premium service with your support provider, and learn the true cost of urgent.

splayer · Post by **splayer** » Sat Sep 02, 2006 4:38 pm

eze_ke, for my installation of SQL Server, I am having the same problem but I am using DRS stage. It is happening with one clumn, state_cd. It is a 2 character column. No matter what combination of data types I try on SQL Server side or DRS stage side (Datastage), the 2nd character gets cut off. I even tried adding blank characters to the right. Same problem!

splayer · Post by **splayer** » Sun Sep 03, 2006 12:06 pm

Ok. I think I have found a solution eventhough it is really iffy. DRS stage cuts off the last character from character columns. ODBC Enterprise stage cuts off the first character. Right before the DRS stage, you have to add a transformer where you have add a character to the right of the character column which get cutoff. You also need to increase the length of the character columns by 1. This worked for me. This same technique does NOT work for the ODBC Enterprise stage. Also, you cannot use BigInt during integer column load. You have to int.

lstsaur · Post by **lstsaur** » Sun Sep 03, 2006 12:51 pm

Hi,
Ascential told me that ODBC Enterprise Stage is limited to four databases: DB2, Informix, Oracle, Teradata. They wouldn't support using other databases period!

ray.wurlod · Post by **ray.wurlod** » Sun Sep 03, 2006 2:49 pm

I wonder if that's documented anywhere?

lstsaur · Post by **lstsaur** » Sun Sep 03, 2006 4:00 pm

yes. As stated in the codbccon.pdf:

Overview
The functionality of the ODBC Enterprise Stage for DataStage
Enterprise Edition is to extend the capabilities of DataStage to
communicate with external data sources. Currently, connectivity to
external data sources from the parallel canvas of DataStage is limited
to four databases: DB2, Informix, Oracle, and Teradata.