Data Become truncated while exporting from ODBC

rakesh.nc · Post by **rakesh.nc** » Wed Aug 01, 2007 5:47 am

hi,

In my project i am exporitng data from SQL to DataSet using ODBC Stage.

The problem is while extracting from SQL using ODBC, the data is getting truncated in some of the fields. here is an example

In SQL the value of Char column inst_month is '200601', but in the ODBC Stage when i See ViewData the inst_month is '2006' and in the target also it is storing the same.

may i know why this is happening.

ArndW · Post by **ArndW** » Wed Aug 01, 2007 6:01 am

What have you defined the column as in the ODBC read stage.

rakesh.nc · Post by **rakesh.nc** » Wed Aug 01, 2007 6:30 am

ArndW wrote:What have you defined the column as in the ODBC read stage. ...

i used char datatype.

ArndW · Post by **ArndW** » Wed Aug 01, 2007 3:50 pm

CHAR(4) perhaps?

rakesh.nc · Post by **rakesh.nc** » Wed Aug 01, 2007 10:39 pm

ArndW wrote:CHAR(4) perhaps? ...

no.. it is char(6) , data is truncated even when i changed to char(10).

ArndW · Post by **ArndW** » Wed Aug 01, 2007 10:57 pm

What about your SQL SELECT clause, might you be usnig some conversion or CAST() in there?

rakesh.nc · Post by **rakesh.nc** » Wed Aug 01, 2007 11:01 pm

ArndW wrote:What about your SQL SELECT clause, might you be usnig some conversion or CAST() in there? ...

no.. i am not using any conversion..

ArndW · Post by **ArndW** » Wed Aug 01, 2007 11:21 pm

If you do a "view data" on your ODBC stage does the data show as truncated already? If not, then I would suggest you check your dataset schema to see if it really is CHAR(6) and not something smaller.

rakesh.nc · Post by **rakesh.nc** » Wed Aug 01, 2007 11:31 pm

ArndW wrote:If you do a "view data" on your ODBC stage does the data show as truncated already? If not, then I would suggest you check your dataset schema to see if it really is CHAR(6) and not something smaller. ...

yup it is char(6) in both the ODBC stage and Dataset stage.. it is truncated in the ODBC stage itself..

ArndW · Post by **ArndW** » Thu Aug 02, 2007 12:55 am

I infer from your post that you have ensured that the actual data is 6 character - what if you put a

Code: Select all

SELECT '*' + cast(inst_month as varchar(5)) '*' as inst_month from YourTable

into your job?

rakesh.nc · Post by **rakesh.nc** » Thu Aug 02, 2007 2:15 am

ArndW wrote:I infer from your post that you have ensured that the actual data is 6 character - what if you put a

Code: Select all

SELECT '*' + cast(inst_month as varchar(5)) '*' as inst_month from YourTable[ ...[/quote]


yup..thank you.. now it is possible to extract the data as it as. but

i gave cast(inst_month as varchar(10)) , if i give less than 10 again data is being truncated.

may i know why this happend in ODBC stage? because i have around 150 table to extract.. is there is any other ways to get over from this problem..

ArndW · Post by **ArndW** » Thu Aug 02, 2007 5:49 am

I am sure that the truncation is coming from your job or table definitions and an implicit conversion and not from a bug. Just a thought - do you have NLS enabled (i.e. a double-byte or multi-byte system)?

rakesh.nc · Post by **rakesh.nc** » Thu Aug 02, 2007 6:11 am

ArndW wrote:I am sure that the truncation is coming from your job or table definitions and an implicit conversion and not from a bug. Just a thought - do you have NLS enabled (i.e. a double-byte or multi-byte sys ...

sorry i dint get.. i dont know about NLS.. where i'll get this info.. could u pls tell me?.