sql server replication: Tx replication works in 2000 but not in 2005? -- sql server replication

Re: Tx replication works in 2000 but not in 2005?

Aaron M. Lowe
4/29/2006 8:59:07 AM

Here's the contents of EXT_ID_HIST_21.sch on the distributor: But again, if
I remove this article it will happen with another article as well.

drop Table [dbo].[EXT_ID_HST]
go
SET ANSI_PADDING ON
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EXT_ID_HST](
[EXT_ID_TYP] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EXTERN_ID] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ID_NUM] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[START_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL,
[REC_UPDATE] [datetime] NULL,
[REC_UPD_SRC] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PK_TIMESTAMP] [timestamp] NOT NULL
)

GO
SET ANSI_NULLS ON

go

SET QUOTED_IDENTIFIER ON

go

SET QUOTED_IDENTIFIER ON

go

if object_id(N'[sp_MSins_dboEXT_ID_HST]', 'P') > 0
drop proc [sp_MSins_dboEXT_ID_HST]
go
if object_id(N'dbo.MSreplication_objects') is not null
delete from dbo.MSreplication_objects
where object_name = N'sp_MSins_dboEXT_ID_HST'
go
create procedure [sp_MSins_dboEXT_ID_HST]
@c1 char(10),@c2 char(16),@c3 char(16),@c4 datetime,@c5 datetime,@c6
datetime,@c7 char(10)
as
begin
insert into [dbo].[EXT_ID_HST](
[EXT_ID_TYP]
,[EXTERN_ID]
,[ID_NUM]
,[START_DATE]
,[END_DATE]
,[REC_UPDATE]
,[REC_UPD_SRC]
)
values (
@c1
,@c2
,@c3
,@c4
,@c5
,@c6
,@c7
)
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article',
'AllowsNull') is not null
exec ('insert dbo.MSreplication_objects (object_name, publisher,
publisher_db, publication, article, object_type) values (
+ N''sp_MSins_dboEXT_ID_HST'' ,
N''OBFSCIDTEST'' , N''ICARD'' , N''Replica'' , N''EXT_ID_HST'' ,''P'')')

go
if object_id(N'[sp_MSins_dboEXT_ID_HST_msrepl_ccs]', 'P') > 0
drop proc
[sp_MSins_dboEXT_ID_HST_msrepl_ccs]
go
create procedure [sp_MSins_dboEXT_ID_HST_msrepl_ccs]
@c1 char(10),@c2 char(16),@c3 char(16),@c4 datetime,@c5 datetime,@c6
datetime,@c7 char(10)
as
begin
if exists ( select * from [dbo].[EXT_ID_HST]
where
)
begin
update [dbo].[EXT_ID_HST] set
[EXT_ID_TYP] = @c1
,[EXTERN_ID] = @c2
,[ID_NUM] = @c3
,[START_DATE] = @c4
,[END_DATE] = @c5
,[REC_UPDATE] = @c6
,[REC_UPD_SRC] = @c7
where
end
else
begin
insert into [dbo].[EXT_ID_HST](
[EXT_ID_TYP]
,[EXTERN_ID]
,[ID_NUM]
,[START_DATE]
,[END_DATE]
,[REC_UPDATE]
,[REC_UPD_SRC]
)
values (
@c1
,@c2
,@c3
,@c4
,@c5
,@c6
,@c7
)
end
end

go
if object_id(N'[sp_MSupd_dboEXT_ID_HST]', 'P') > 0
drop proc [sp_MSupd_dboEXT_ID_HST]
go
if object_id(N'dbo.MSreplication_objects') is not null delete from
dbo.MSreplication_objects where object_name = N'sp_MSupd_dboEXT_ID_HST'
go
create procedure [sp_MSupd_dboEXT_ID_HST]
@c1 char(10) = null,@c2 char(16) = null,@c3 char(16) = null,@c4 datetime =
null,@c5 datetime = null,@c6 datetime = null,@c7 char(10) = null,@pkc1
timestamp
,@bitmap binary(1)
as
begin
update [dbo].[EXT_ID_HST] set
[EXT_ID_TYP] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else
[EXT_ID_TYP] end
,[EXTERN_ID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
[EXTERN_ID] end
,[ID_NUM] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [ID_NUM]
end
,[START_DATE] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else
[START_DATE] end
,[END_DATE] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else
[END_DATE] end
,[REC_UPDATE] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else
[REC_UPDATE] end
,[REC_UPD_SRC] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else
[REC_UPD_SRC] end
where [PK_TIMESTAMP] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article',
'AllowsNull') is not null
exec ('insert dbo.MSreplication_objects (object_name, publisher,
publisher_db, publication, article, object_type) values (
+ N''sp_MSupd_dboEXT_ID_HST'' , N''OBFSCIDTEST'' ,
N''ICARD'' , N''Replica'' , N''EXT_ID_HST'' ,''P'')')

go
if object_id(N'[sp_MSdel_dboEXT_ID_HST]', 'P') > 0
drop proc [sp_MSdel_dboEXT_ID_HST]
go
if object_id(N'dbo.MSreplication_objects') is not null
delete from dbo.MSreplication_objects where object_name =
N'sp_MSdel_dboEXT_ID_HST'
go
create procedure [sp_MSdel_dboEXT_ID_HST]
@pkc1 timestamp
as
begin
delete [dbo].[EXT_ID_HST]
where [PK_TIMESTAMP] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article',
'AllowsNull') is not null
exec ('insert dbo.MSreplication_objects (object_name, publisher,
publisher_db, publication, article, object_type) values (
+ N''sp_MSdel_dboEXT_ID_HST'' ,
N''OBFSCIDTEST'' , N''ICARD'' , N''Replica'' , N''EXT_ID_HST'' ,''P'')')

go
if object_id(N'[sp_MSdel_dboEXT_ID_HST_msrepl_ccs]', 'P') > 0
drop proc
[sp_MSdel_dboEXT_ID_HST_msrepl_ccs]
go
create procedure [sp_MSdel_dboEXT_ID_HST_msrepl_ccs]
@pkc1 timestamp
as
begin
delete [dbo].[EXT_ID_HST]
where [PK_TIMESTAMP] = @pkc1
end

go

[quoted text, click to view]

Re: Tx replication works in 2000 but not in 2005?

Hilary Cotter
4/30/2006 12:00:00 AM

oh, k, got it.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Re: Tx replication works in 2000 but not in 2005?

Aaron M. Lowe
5/1/2006 11:36:23 AM

After some additional testing it seems that the problem lies with the
timestamp. Once I removed the articles (14 of them) that had timestamps in
them I was able to run the replication perfectly.

I'm going to try publishing the article one at a time as well as using a
filter to see if I can verify that is the problem. (Of course I'll still
need to come up with a solution :)

Thanks for your help,

--AML--

[quoted text, click to view]

Re: Tx replication works in 2000 but not in 2005?

Aaron M. Lowe
5/1/2006 4:09:39 PM

Well just thought I"d let you guys know (thanks for your help) that this is
due to a scripting bug in the snapshot agent. It is specific to timestamp
fields that are primary keys, which the two articles I was having problems
with had.

MS has said that they don't have a fix currently, but the work around is to
change the primary key to a non timestamp field.

Thanks for your help.

Aaron Lowe

[quoted text, click to view]

Re: Tx replication works in 2000 but not in 2005?

Paul Ibison
5/3/2006 12:00:00 AM

Aaron - thanks for the update and 'inside info'.
Cheers,
Paul

[quoted text, click to view]

RE: Tx replication works in 2000 but not in 2005?

Raymond Mak [MSFT]
5/18/2006 11:57:01 AM

Hi Aaron,

In case you are still reading this thread, the problem that you encountered
really has to do with a change of default behavior from SQL2000 to SQL2005 to
replicate timestamp column as timestamp instead of binary(8), and the fact
that the custom procedure generation logic simply cannot handle the case
where a timestamp column is part of the primary key when it is replicated as
timestamp. Taking a few steps back to look at the situation at a higher
level, transactional replication simply cannot provide any reasonable
behavior if a timestamp column in a primary key is replicated as a timestamp
column since the timestamp values will be different between the publisher and
the subscriber and transactional replication is based on the assumption that
primary key values are the same at the publisher and the subscriber. Luckily,
you can work around the problem by disabling the 0x08 schema option (or
setting the 'Convert timestamp to binary' option to true in the article
property sheet), this will basically give you the same behavior as you got
from SQL2000.

-Raymond

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.