Microsoft KB Archive/247527

= FIX: DTS May Truncate Characters When You Export a Table Column of Character Data Type to a Text File =

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 </li></ul>

<div class="status_section">

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.