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

ID: Q201871

The information in this article applies to:

Microsoft SQL Server version 6.5

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 version 6.5.

MORE INFORMATION

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

Q190690 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 : SSrvRep kbbug6.50
Version : winnt:6.5
Platform : winnt
Issue type : kbbug