January 10, 2003 at 8:20 am
I am wondering if any of you have seen something similar to what we discovered yesterday.
We have some data pumps that move data from SQL Server 2000 tables to a text file. The problem is that if the data type for the column is varchar or char and there are more than 255 characters in that column only 255 get placed into the text file. The destination column size shows the correct character length (in this case 1530). The source is OLE DB. We are using a query for the source info and the column is not modified. When we preview the data to be pumped we can see all the data in this column, however when we execute the data pump it only pumps 255 characters.
Another column that happens to have a data type of text pumps more than 255 characters.
The only solutions we found was to cast the varchar column as text or pump the data to an excel spread sheet instead of a text file.
Is there some server wide setting or MDAC setting that could do this? Or has SQL Server always functioned this way (I doubt this but have to ask)?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 10, 2003 at 11:38 am
I think it must a retriction with the text driver with delimited files. If you change to fixed field you should get all your data. Probably not much use if you require delimited data!!!
Other than this you can try BCP.
Edited by - davidburrows on 01/10/2003 11:46:00 AM
Far away is close at hand in the images of elsewhere.
Anon.
January 12, 2003 at 2:17 pm
It's a known issue, fixed in SP2. See KB 247527
FIX: DTS May Truncate Characters When You Export a Table Column of Character Data Type to a Text File
http://support.microsoft.com/default.aspx?scid=kb;en-us;247527
Make sure ypou apply SP2 to your client machines used to build DTS packages as well as just the server.
As David suggested BCP will work, or if you want a DTS solution try this-
DTS Bulk Export Task
http://www.sqldts.com/default.aspx?6,102,237,0,1
Darren Green
SQLDTS.com | SQLIS.com | Konesans Ltd
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply