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):
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):
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:
After build 753 (for SQL Server 7.0 Bug #54663), the logreader returns error 18773:
REFERENCES
For 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
Keywords: kbbug kbfix kbfaq KB251187
