Microsoft KB Archive/201871

= BUG: Distribution Task Fails with "Incorrect syntax near '{'" =

Article ID: 201871

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q201871



BUG #: 18279 (SQLBUG_65)



SYMPTOMS
While replicating tables with datetime columns and custom stored procedures, the distribution task may fail with the following error:

Incorrect syntax near '{'.

This problem happens only with the MDAC 2.0 components that include SQL Server ODBC driver version 3.60 and ODBC Driver Manager version 3.510.3022.



CAUSE
A custom stored procedure can be used in an article for insert, update, and delete commands. This error occurs when the distribution task tries to execute a buffer that contains a mix of regular commands (insert, update, or delete) and custom stored procedure calls involving datetime columns.

The underlying cause for the problem is a problem with the ODBC driver version 3.60.0319, which is included with MDAC version 2.0. The driver passes on the datetime data incorrecty to SQL Server.



WORKAROUND
To work around this problem, set the CommitBatchSize (-c parameter) of the distribution task to 1. This sends individual transactions in separate batches and may avoid the problem.

If the error still occurs with the -c parameter set to 1, this indicates that a single transaction contained more than one command. In such cases, if you use custom stored procedures on any article, create a custom stored procedure for all statements (delete, insert, and update) for the article. Transactions that affect multiple tables make it necessary for all tables involved in a single transaction to have all three custom stored procedures.

If the above two workarounds are not feasible, update the MDAC components to MDAC 2.1. This version of MDAC is available with SQL Server 6.5 Service Pack 5 and SQL Server 7.0.



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



MORE INFORMATION
This issue often occurs when following the instructions in the following article in the Microsoft Knowledge Base:

190690 INF: How to Set Up Replication on Tables with an Identity Column

Although this article requires only a custom stored procedure for inserts, you should also create custom stored procedures for deletes and updates.

Additional query words: repl open database connectivity data access components date time col cols st proc sproc procs sprocs

Keywords: kbbug kbpending KB201871

-

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

© Microsoft Corporation. All rights reserved.