BUG: DTS Transfer May Fail with "Incorrect syntax..." Error Message (260320)

SYMPTOMS

A Data Transformation Services (DTS) transfer may fail with either of the following error messages when you copy data from a table in SQL Server 7.0 into a table in SQL Server 6.5 and you use a database name that has more than six characters at the destination server (that is, the SQL Server 6.5 server):

Incorrect syntax near the keyword 'convert'.

-or-

Unclosed quote before the character string ',xxxxxxx)'.
Line y: Incorrect syntax near 'database'.

WORKAROUND

To work around this problem, try any one of the following:

Change the database name at the destination server (that is, the SQL Server 6.5 server). The name should have fewer than seven characters.

-or-
Use the "MS ODBC Driver for SQL Server" (instead of the MS OLE DB Provider for SQL Server) for the destination (SQL Server 6.5 server) and source (SQL Server 7.0 server).

-or-
Perform the following steps:
1. Save the package for later execution.
2. Open the package in the Package Designer and double-click the arrow between source and destination.
3. On the Advanced tab of the DTS Package Properties dialog box, click to clear the the Fast Load Option.

MORE INFORMATION

Steps to Reproduce the Problem

On a SQL Server 7.0 server, create a database and name it Source.

Use the following CREATE TABLE statement to create the table in the Source database:

CREATE TABLE [dbo].[Test] (
   [a] [int] NULL ,
   [b] [int] NULL ,
   [c] [int] NULL ,
   [d] [int] NULL ,
   [e] [int] NULL ,
   [f] [int] NULL ,
   [g] [int] NULL ,
   [h] [int] NULL ,
   [i] [int] NULL,
   [j] [int] NULL )

On a SQL Server 6.5 server, create a database and name it Destination.

Use the following CREATE TABLE statement to create the table in the Destination database:

CREATE TABLE dbo.test(
   a int NULL CONSTRAINT DF_a DEFAULT (0),
   b int NULL CONSTRAINT DF_b DEFAULT (0),
   c int NULL CONSTRAINT DF_c DEFAULT (0),
   d int NULL CONSTRAINT DF_d DEFAULT (0),
   e int NULL CONSTRAINT DF_e DEFAULT (0),
   f int NULL CONSTRAINT DF_f DEFAULT (0),
   g int NULL CONSTRAINT DF_g DEFAULT (0),
   h int NULL CONSTRAINT DF_h Default (0),
   i int NULL CONSTRAINT DF_i DEFAULT (0),
   j int NULL CONSTRAINT DF_j DEFAULT (0))

In the DTS Wizard, select the following options:
Source: Database Source on SQL Server 7.0
Destination: Database Destination on SQL Server 6.5
Option: Copy Table(s) from the Source Database
Option: Transform (.) and Delete rows in destination table
Option: Run immediately

NOTE: If the destination database on SQL Server 6.5 has a name with fewer than seven characters (for example, if the destination database is named Test), then the transfer will work.