FIX: Logreader Fails When It Processes an Update with the Trace Flag 8202 Set ON (251187)
The information in this article applies to:
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.
REFERENCESFor additional information on using Trace Flag 8202, click the article number below
to view the article in the Microsoft Knowledge Base:
160181 INF: Trace Flag to Replicate UPDATE as DELETE/INSERT Pair
| Modification Type: | Major | Last Reviewed: | 3/14/2006 |
|---|
| Keywords: | kbBug kbfix KB251187 |
|---|
|