FIX: Distribution Agent Fails with Syntax Error with Compensating Commands for Queued Updating Subscribers (294970)

SYMPTOMS

Queued updating Subscribers is a new option in SQL Server 2000. Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring an active network connection to the Publisher. Because queued updating allows modifications to the same data at multiple locations, there may be conflicts when data is synchronized at the Publisher. When you create a publication by using queued updating, a conflict resolver instructs the Queue Reader Agent how to handle different versions of the same row encountered during synchronization. By default, the Publisher wins conflict resolver is set.

When the conflict resolution is set to Publisher wins.., transactional consistency is maintained based on the data at the Publisher. The conflicting transaction is rolled back at the Subscriber from which it initiated. The Queue Reader Agent detects a conflict and compensating commands are generated and propagated to the Subscriber by posting them in the distribution database. The Distribution Agent then applies the compensating commands to the Subscriber that originated the conflicting transaction. The compensating actions update the rows on the Subscriber to match the row on the Publisher.

While applying the compensating commands, the Distribution Agent encounters this error message:

Message: Line 1: Incorrect syntax near '<some sql statement>'.

CAUSE

The code to generate compensating commands also splits the command when it exceeds eight (8) kilobytes (KB). The way the split is performed for a string column when its value is NULL results in the creation of an extra quote. The extra quote may cause a syntax error when the compensating command is applied by the Distribution Agent.

RESOLUTION

A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:

   File name  
   -----------

   s80230i.exe