Re: Tx replication works in 2000 but not in 2005?
- From: "Aaron M. Lowe" <alowe@xxxxxxx>
- Date: Mon, 1 May 2006 16:09:39 -0500
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
"Aaron M. Lowe" <alowe@xxxxxxx> wrote in message
news:uC5Cl1TbGHA.4424@xxxxxxxxxxxxxxxxxxxxxxx
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--
"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%233%23UOyEbGHA.4424@xxxxxxxxxxxxxxxxxxxxxxx
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
"Aaron M. Lowe" <alowe@xxxxxxx> wrote in message
news:uQcaWU5aGHA.4972@xxxxxxxxxxxxxxxxxxxxxxx
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
"Aaron M. Lowe" <alowe@xxxxxxx> wrote in message
news:OXCHpqzaGHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
I'm not sure what you mean by "enable logging", as I didn't realize
there was additional logging available for Replicaion. Here's the
entire message:
Command attempted:
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"
(Transaction sequence number: 0x0012862700006C8D00F900000000, Command
ID: 171)
Error messages:
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error
number: 156)
Get help: http://help/156
Actually that's the second message I got. The first one I received
from a different article and removed that specific article from the
publication to see if it would work, but then I got the same error
again.
--AML--
"Paul Ibison" <Paul.Ibison@xxxxxxxxxxxxx> wrote in message
news:OZSF%23uwaGHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
Aaron,
I'd like to repro this myself out of interest, but before that pls can
you enable logging to try to see which article is causing the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
.
- Follow-Ups:
- Re: Tx replication works in 2000 but not in 2005?
- From: Paul Ibison
- Re: Tx replication works in 2000 but not in 2005?
- References:
- Re: Tx replication works in 2000 but not in 2005?
- From: Aaron M. Lowe
- Re: Tx replication works in 2000 but not in 2005?
- Prev by Date: Re: Change distribution server
- Next by Date: replication error HRESULT 0x80004005 (28022)
- Previous by thread: Re: Tx replication works in 2000 but not in 2005?
- Next by thread: Re: Tx replication works in 2000 but not in 2005?
- Index(es):
Relevant Pages
|
