DBS-070402 : String data, right truncation

dataservices job design
1

I’m having trouble understanding the reason for this warning. This is not loading anything to the target as I’m using Bulk Loader Truncate option.

Here is the warning message:

ODBC data source warning message for operation <bcp_sendrow>: <[Microsoft][ODBC SQL Server Driver]String data, right
truncation>, SQLState <22001>.

Warning: Bulk loading into table failed with input row data <data…>

Regards,
anandr

anand.ramamoorthy (BOB member since 2008-09-09)

2

You have a varchar(1) but your input data is a string of length >1 like ‘abcd’. Hence the column(!) value gets truncated.

Truncating the table means to empty it so it has no rows. :yesnod:

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thanks Werner. I dont think I have explained the problem well the last time around. I understand what truncation of a table is and that is exactly what I want to do. The data flow has a query transform that filters records and there are roughly 7 to 8 records expected to be loaded into the target table.

The debugger also shows that the expected 7 or 8 records pass through the query transform and is ready for insertion int the database table. And I have checked all the fields going out of query transform and into the target table. All have matching field size and datatype. But still I’m ending up with this warning. If its truncating the value of a field due to sizing, then the record should reflect in the target with a truncated string. But I cannot see any records.

Is there any other possible or known reason for this in regards to Business Objects working with SQL Server?

Regards,
anandr

anand.ramamoorthy (BOB member since 2008-09-09)

4

[quote:5fb6d96901=“anand.ramamoorthy”]If its truncating the value of a field due to sizing, then the record should reflect in the target with a truncated string.
[/quote]

In my experience with both ODBC and many native drivers, that is not the case. Instead of inserting the truncated string the driver throws an error, similar to what you received. DI is not generating this error, the ODBC driver is and DI is simply reporting it back to you.

This can happen when the database is expecting a string of length 14 (ASCII) and you pass it a string of length 14 (UTF-8) with extended characters. That problem can be resolved at the DB level by changing the type of the field to properly reflect the data you wish to store.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

5

Thanks Ernie. Seems to be more of a Bulk Load issue. If I disable Bulk Load option and use the normal load “delete data from table before loading” option, then the job executes perfectly and I get what I expect in the target table.

Btw, the incoming data is in ASCII format (both code pages are the same) and the same applies to the SQL Server database.

And we are using varchar in the DI Query transform output as well as SQL Server table.

Cheers,
anandr

anand.ramamoorthy (BOB member since 2008-09-09)

6

in bods 4.0, column with datetime are not populated to template tables displaying error,

ODBC data source <UKSOKDWDBDV01\ATP> error message for operation : <[Microsoft][SQL Server Native Client 10.0]String data, right truncation>.

rajnia1 (BOB member since 2011-11-17)