Troubleshooting DTS Problems

October 21, 2002


Should you have problems with Data Transformation Services (DTS), review this troubleshooting checklist to find potential solutions.


1. If you receive an error that the license for the installation of SQL Server does not permit the use of DTS to transform data, switch SQL Server to "per-seat" mode.

This error means that you cannot perform distributed operations (for example, replication, data transformation or heterogeneous query services) between MSDE or SQL Server Desktop and the Standard/Enterprise version of SQL Server if it has been installed in "per server" mode.

This is from the SQL Server 7.0 END-USER LICENSE AGREEMENT:

Note Regarding Interaction between Microsoft SQL Server and
Microsoft Data Engine ("MSDE").
If you use MSDE to access or use the services of Microsoft
SQL Server (for example, replication, data transformation or
heterogeneous query services), you must use Microsoft SQL Server in
Per Seat Mode (that is, each unique Device accessing SQL Server must
have a Client Access License dedicated to it).

Note Regarding Use of SQL Server Desktop.
You may only install and use the SQL Server Desktop component of
Microsoft SQL Server on each Device to which a valid Client Access
License for the Server Software has been dedicated. If you use
SQL Server Desktop to access or use the services of any version
of Microsoft SQL Server (for example, replication, data
transformation or heterogeneous query services), you must use
such Microsoft SQL Server in Per Seat Mode.


2. If you can run a DTS package but cannot schedule it, verify that the account the SQLServerAgent service runs under has access to network resources.

When you try to schedule a DTS package, the appropriate job will run under the user account that the SQLServerAgent service is running under. For example, if the SQLServerAgent service runs under the LocalSystem account, you cannot schedule a DTS package as the LocalSystem account does not have network access.

Note. Before scheduling a DTS package, the Schedule service must first be started.

To start the Schedule service on Windows NT, choose:
Start -> Settings -> Control panel -> Services
(choose Startup "Automatic" type) and Start.

To start the Schedule service on Windows 2000, choose:
Start -> Settings -> Control panel -> Administration -> Services
(choose Startup "Automatic" type) and Start.


3. If you get the error: 'DTS Wizard Error - CoCreateInstance Class not Registered', apply the latest service pack or add MAXDOP(1) to the query, which will prevent the query from being parallelized.

This error happens with parallel queries only. It means that one thread of the query is waiting for a message packet from another. To work around this, you should apply the latest service pack or add MAXDOP(1) to the query in order to prevent it from being parallelized.


4. If a DTS package stored in the repository is not listed, try to download the DTS package from the sysdtspackages system table into file on disk for future use.

The text of the DTS package is stored in the PackageData image field of the sysdtspackages system table in the msdb database. To save the DTS package in file, you can use the textcopy.exe utility. See this FAQ question for more details: My DTS package is stored inside SQL Server. Now I cannot open it.


5. Verify that you are using the latest SQL Server service pack.

To check which SQL service pack are you running, see this link: How can I check what SQL service pack am I running?


6. If you receive an error when exporting a database from SQL Server 7.0 to SQL Server 6.5 using the DTS Export Wizard, try to create a destination database before attempting the transfer.

This error arises because DTS incorrectly sends SQL Server 7.0 CREATE DATABASE syntax to SQL Server 6.5.


7. If you receive a 'not enough memory' error when attempting to import a text file larger than the available memory on a Windows 9x client, try to separate the text file into several small files or use a client computer running Windows NT or Windows 2000.

This error arises because DTS Import Wizard calls the command MapViewOfFile in order to display the structure of the file being imported. If the client computer is running Windows 9x, the MapViewOfFile call will attempt to reserve a contiguous area of memory large enough to map the entire file.

So, you can recieve a 'not enough memory' error when attempting to import a text file larger than the available memory on a Windows 9x client.


8. If your database contains space in its name and the DTS Designer does not show columns in build query, try to rename the database to eliminate the space(s) in the database name.

This is a SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.


9. When you use the "Copy table(s) from the source database" option of the SQL Server 7.0 DTS Import Wizard to import tables with a timestamp column, DTS fails.

To work around this problem, you can use the "Use a query to specify the data to transfer" option of the DTS Import Wizard and exclude the timestamp column, you can use the "Transfer objects and data between SQL Server 7.0 databases" option of the DTS Import Wizard and specify which table you want to transfer, or you can define the destination table to use binary(8) for the datatype for the column which will contain the timestamp.


10. The DTS Import/Export Wizard may truncate column strings that are over 255 characters long when exporting columns to a text file and delimited fields are used for exporting.

To work around this problem, you can use fixed fields instead of delimited fields, you can use the bcp utility instead of DTS, or you can create the table column by using a text data type.


11. If the DTS package created on MDAC 2.5/2.6 fails on a computer with a downlevel MDAC (for example, version 2.1), install MDAC 2.5 (or later) on the computer that is to run the DTS package.


12. The DTS Query Builder does not correctly build an SQL statement that begins with the SQL Server inline comment '--'.

To work around this problem, avoid using the inline comments with the DTS Query Builder. Use the '/*...*/' style of comments instead of the '--' inline comments.


13. The SQL Server 7.0 DTS package with an ActiveX script can be truncated to approximately 32,400 characters when saved to a local repository.

To work around this problem, save the DTS package to a file or to SQL Server instead of to the local repository. This is another SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.


14. When you use the "Transfer objects and data between SQL Server 7.0 databases" option of the SQL Server 7.0 DTS Export Wizard, data in columns of a table with datatypes of char or varchar larger than 4000 characters will not be copied.

To work around this problem, use the "Copy table(s) from the source database" option of the SQL Server 7.0 DTS Export Wizard instead of the "Transfer objects and data between SQL Server 7.0 databases".


15. The DTS Import/Export Wizard fails if an Excel file was open during the import/export operation.

To work around this problem, close the Excel file before the import/export operation.


16. You can get a connection busy error if you transfer data using 'Microsoft ODBC Driver for SQL Server' and attempt to preview a stored procedures.

To work around this problem, use 'Microsoft OLE DB Provider for SQL Server' instead of 'Microsoft ODBC Driver for SQL Server'.


» See All Articles by Columnist Alexander Chigrik









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers