FIX: Logreader Fails to Replicate When Updating Non-NULL Text Fields with Empty Space

• Microsoft SQL Server version 7.0

SYMPTOMS

Logreader stops delivering replication transactions to the distribution database. It reports the following message even if there are transactions in the log pending replication:

No replicated transactions are available.

CAUSE

The sp_replcmds stored procedure fails to recognize UPDATE transactions when they include an empty space change ('') to a non-NULL text field. The Logreader process uses sp_replcmds to locate the commands marked for replication. After this error is encountered, no further transactions are replicated by the Logreader. The publication must be unsubscribed and resubscribed before replication will continue.

WORKAROUND

To work around this problem, use white space (' ') to update non-NULL text fields.

If you cannot use this workaround but you use custom stored procedures (default) at the subscriber, you can use the following workaround instead:

1. Unsubscribe your publications.



2. Change each article. Locate the Articles Properties, Command tab. Click to clear the Send Stored Procedure Parameters In Binary Format option .



3. Resubscribe the publications.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: repl log reader nonnull nonull

Keywords          : SSrvRep kbbug7.00 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: June 8, 1999