Article ID: 935563 - View products that this article applies to.
When you use transactional replication in Microsoft SQL Server 2005, you may receive the following error messages:
Error message 1
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Error message 2
You may experience this issue in the following two scenarios.
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Scenario 1The publication of the transactional replication contains an article that is created based on a table. The table has a primary key that is created on a timestamp column.
Scenario 2The article is created based on a table. All non-primary key columns of the table are not updatable. These columns include the following columns:
This problem occurs because the Snapshot Agent generates an invalid command. Therefore, the Distribution Agent cannot deliver the snapshot files.
Workaround for scenario 1To work around this problem, set the Convert TIMESTAMP to BINARY setting to True for the article. To do this, follow these steps:
Workaround for scenario 2To work around this problem, use one of the following methods.
Method 1Change replication publication from concurrent snapshot processing to nonconcurrent snapshot processing.
Note When you use this method, you cannot update the publishing table when the snapshot is being generated.
To do this, set the value of the sync_method parameter to one of the following values when you call the sp_addpublication stored procedure directly to add the publication:
Note You cannot use SQL Server Management Studio to set this value.
If you already have the publication, you can change this setting by running the following statement on your publication database:
Note In this statement, <0 or 1> represents the value of the sync_method parameter. If the original value of the sync_method parameter is 3, change the value to 0. If the original value is 4, change the value to 1.
After you complete this workaround, you must regenerate the snapshot. Additionally, you must reinitialize the subscription.
Method 2When you call the sp_addarticle stored procedure directly to add the article, use the following criteria to set the @schema_option parameter:
If the publication is already created, you can directly disable schema option 0x04 or schema option 0x08. To do this, run the following Transact-SQL statements:
Method 3Add one or more updatable columns to the publishing table.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This problem occurs because of a change from SQL Server 2000 to SQL Server 2005. By default, a timestamp column is replicated as a binary (8) data type in SQL Server 2000. In SQL Server 2005, a timestamp column is replicated directly by default.
The custom procedure generation logic cannot handle a scenario in which a timestamp column is part of the primary key when the timestamp column is replicated in SQL Server 2005. Additionally, transactional replication cannot work correctly because the time stamp values are different between the publisher and the subscriber. However, transactional replication assumes that primary key values are the same at the publisher and at the subscriber.
For tables in which all non-primary key columns are not updatable, the following script is generated in the snapshot file:
Because no columns can be updated, no column update statements exist between the SET clause and the WHERE clause. This behavior causes a syntax error.
Article ID: 935563 - Last Review: November 20, 2007 - Revision: 1.2