Microsoft KB Archive/260320

= BUG: DTS Transfer May Fail with &quot;Incorrect syntax...&quot; Error Message =

Article ID: 260320

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q260320



BUG #: 57283 (SQLBUG_70)



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 &quot;MS ODBC Driver for SQL Server&quot; (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:


 * Save the package for later execution.
 * Open the package in the Package Designer and double-click the arrow between source and destination.
 * On the Advanced tab of the DTS Package Properties dialog box, click to clear the the Fast Load Option.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



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

</li></ol>

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.

Keywords: kbbug kbpending KB260320

-

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

© Microsoft Corporation. All rights reserved.