Microsoft KB Archive/251187

= FIX: Logreader Fails When It Processes an Update with the Trace Flag 8202 Set ON =

Article ID: 251187

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q251187



BUG #: 57332 (SQLBUG_70)



SYMPTOMS
If the list of conditions that follow are true for a publication, the logreader agent fails:


 * Trace flag 8202 is set ON.


 * A published article contains text or image columns.


 * The updated column is not a primary key or the text/image column itself.

The storage engine fails to log the previous Non-null text/image value and the logreader agent fails with these errors:

Error 18759 on Service Pack 1 (SP1):

The process could not execute 'sp_replcmds' on 'Publisher'

Replication failure.File 'r\SPHINX|NTDBMS\srvrepl\src\pfmt.cpp',line 443.

Error 18773 on builds after hotfix 753 (SQL Server 7.0 Bug #54663, see Q247870 for more details) and SQL Server 7.0 Service Pack 2 (SP2):

The process could not execute 'sp_replcmds' on 'Publisher'.

Could not locate text information records for column id during command construction.



CAUSE
The SQL Server 7.0 engine does not store the previous text or image value(s), so there is no TEXT_INFO log record for the logreader to reconstruct the INSERT command.



WORKAROUND
Use XCALL for the @upd_cmd command when you add an article with a text/image column in order to force a log entry in TEXT_INFO records.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
Use this code to reproduce the behavior: --Turn on -T8202 DBCC TRACEON (8202, -1)

-- Create publishing db DB1 Use master CREATE DATABASE DB1 GO

-- Create table T1 Use DB1 CREATE TABLE [dbo].[T1] (   [c1] [int] NOT NULL Primary Key,    [c2] [int] NULL,    [c3] [text] NULL ) GO

Insert into T1 values (1,1,'test1') go Insert into T1 values (2,2,'test2') go

exec sp_dboption DB1, 'published', true go

-- Create Publication P1 on table T1 exec sp_addpublication @publication = N'P1', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of P1 database from Publisher SHANGHAI.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 72 go exec sp_addpublication_snapshot @publication = N'P1',@frequency_type = 8, @frequency_interval = 64, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225200, @active_end_time_of_day = 0 GO

exec sp_addarticle @publication = N'P1', @article = N'T1', @source_owner = N'dbo', @source_object = N'T1', @destination_table = N'T1', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000073, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_T1', @del_cmd = N'CALL sp_MSdel_T1', @upd_cmd = N'MCALL sp_MSupd_T1', @filter = null, @sync_object = null GO

-- Update a non-PK column without giving the value for the text column: Use DB1 update T1 set c2 = 3 where c1 = 1 go RESULT: With Service Pack 1, the logreader returns error 18759:

The process could not execute 'sp_replcmds' on 'SHANGHAI'

Replication failure.File 'r\SPHINX|NTDBMS\srvrepl\src\pfmt.cpp',line 443.

After build 753 (for SQL Server 7.0 Bug #54663), the logreader returns error 18773:

The process could not execute 'sp_replcmds' on 'SHANGHAI'.

Could not locate text information records for column 3 during command construction.