Article ID: 247527
Article Last Modified on 6/23/2005
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q247527
BUG #: 56824 (SQLBUG_70)
BUG #: 216236 (SHILOH_BUG)
SYMPTOMS
When you use the Data Transformation Services (DTS) Export/Import wizard, DTS may truncate column strings that are over 255 characters long if all of the following conditions exist:
- The column is a character data type (varchar, char, nvarchar, nchar) and the column length is greater than 255 characters.
- DTS exports the column to a text file.
- Delimited fields are used for exporting.
CAUSE
When you use delimited fields to export data, the OLE provider for text files is used by DTS, DTSFlatFile (dtsffile.dll), which causes the data to truncate on export.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
Here are several methods that you can use to work around this problem:
- Use fixed fields and export to a text file with the DTS Wizard or Package Designer. Both the DTS Export/Import Wizard and the DTS Package Designer provide the option to use fixed fields when you export data to a text file.
To use the DTS Export/Import wizard, you will see the following prompt when you choose the destination text file:Select Destination File Format
You want to select Fixed field - information is assigned into columns of equal width.
To use the DTS Package Designer, you will see the following prompt when you are setting up the destination text file:Select file format
You want to select Fixed field - information is assigned into columns of equal width.
NOTE: Because the use of fixed fields might require more space than delimited fields if your varchar columns are not filled up, this operation may potentially use more space. The fixed field requires more space because space is allocated to the whole width of the column. However, the next option eliminates this problem.
- Use bcp to export the table to a text file. Bcp provides features that allow you to export tables or views to text files by using delimited fields. For more information about the bcp utility, see Microsoft SQL Server 7.0 Books Online.
- Create the table column by using a text data type.
- In SQL Server 2000, an additional workaround exists if the delimited text files must be used. When you use the DTS Import/Export Wizard to export data to a delimited text file, first save the package, and then open the package in the DTS Designer. In the DTS Designer, click Package, and then click Disconnected Edit to open the package in Disconnected Edit mode. Expand Connections, expand the text file connection object, and then expand OLEDB Properties. Finally, change the value of "Max characters per delimited column" to 8,000.
If you create the package by using the DTS object model, add the following line of code where the text file connection is being initialized:
oConnection.ConnectionProperties("Max characters per delimited column") = 8000
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
Additional query words: DTS, Export, delimited, fields
Keywords: kbbug kbfix KB247527
