You receive syntax error messages when you use transactional replication in SQL Server 2005

Article translations Article translations
Article ID: 935563 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

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
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
You may experience this issue in the following two scenarios.

Scenario 1

The 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 2

The article is created based on a table. All non-primary key columns of the table are not updatable. These columns include the following columns:
  • Identity columns
  • Timestamp columns
  • Computed columns
Additionally, you use concurrent snapshot processing, and the publication is read-only transactional publication. For example, the article is created based on the following tables:
  • create table tab1(col1 int primary key, col2 as (col1 + 1))
  • create table tab1(col1 int primary key, col2 int identity)
  • create table tab1(col1 int primary key, col2 timestamp)
Note This problem does not occur when you use replication in Microsoft SQL Server 2000.

CAUSE

This problem occurs because the Snapshot Agent generates an invalid command. Therefore, the Distribution Agent cannot deliver the snapshot files.

WORKAROUND

Workaround for scenario 1

To work around this problem, set the Convert TIMESTAMP to BINARY setting to True for the article. To do this, follow these steps:
  1. On the computer that is running SQL Server 2005, start SQL Server Management Studio.
  2. In Object Explorer, expand the computer that contains your publication, expand Replication, and then expand Publications.
  3. Right-click the publication name, and then click Properties.
  4. In the Properties dialog box, click Articles.
  5. Select an article, click Article Properties, and then click Set Properties of Highlighted Table Article. The Article Properties dialog box appears.
  6. In the Distinction Object section of the properties list, set the Convert TIMESTAMP to BINARY setting to True.
Additionally, disable the 0x08 schema option to work around this problem. To do this, use one of the following methods:
  • When you run the sp_addarticle system stored procedure to create an article, set the @schema_option parameter to exclude the schema option 0x08. For more information about the @schema_option parameter, visit the following Microsoft Developer Network (MSDN) Web site:
    http://msdn2.microsoft.com/en-us/library/ms173857.aspx
  • Run the following statement against the publication database:
    update sysarticles set schema_option = schema_option - (schema_option & 8) * 8  where artid = [<Your_Article_ID>]
Note When you set the Convert TIMESTAMP to BINARY setting to True, the underlying behavior is the same as the behavior that occurs when you disable the 0x08 schema option.

Workaround for scenario 2

To work around this problem, use one of the following methods.

Method 1

Change 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:
  • native
  • character
Do not set the value of the sync_method parameter to concurrent or concurrent_c.

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:
update syspublications set sync_method = <0 or 1>
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 2

When you call the sp_addarticle stored procedure directly to add the article, use the following criteria to set the @schema_option parameter:
  • If you have an identity column that does not have a primary key, exclude schema option 0x4.
  • If you have a timestamp column that does not have a primary key, exclude schema option 0x8.
Note No corresponding schema option exists for computed columns. Therefore, this workaround does not apply to computed columns.

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:
update sysarticles set schema_option = schema_option - (schema_option & 8) * 8  where artid = [<Your_Article_ID>]
update sysarticles set schema_option = schema_option - (schema_option & 4) * 4  where artid = [<Your_Article_ID>]

Method 3

Add one or more updatable columns to the publishing table.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

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:
UPDATE  <Table Name>  SET
WHERE <Search Condition>
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.

Properties

Article ID: 935563 - Last Review: November 20, 2007 - Revision: 1.2
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Keywords: 
kbexpertiseadvanced kbsql2005repl kbtshoot kbprb KB935563

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com