Troubleshooting DTS Problems


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

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles