Data Become truncated while exporting from ODBC
Moderators: chulett, rschirm, roy
Data Become truncated while exporting from ODBC
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.
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.
regards,
Rakesh
Rakesh
What have you defined the column as in the ODBC read stage.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
CHAR(4) perhaps?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
What about your SQL SELECT clause, might you be usnig some conversion or CAST() in there?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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..
regards,
Rakesh
Rakesh
I infer from your post that you have ensured that the actual data is 6 character - what if you put a into your job?
Code: Select all
SELECT '*' + cast(inst_month as varchar(5)) '*' as inst_month from YourTable
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
ArndW wrote:I infer from your post that you have ensured that the actual data is 6 character - what if you put aCode: 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..
regards,
Rakesh
Rakesh
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)?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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?.
regards,
Rakesh
Rakesh