Hi there,

I have a replication between servers that is failing with the above error
code... can anyone give some insight into why this is happening? I'm
replicating from a SQL 2000 server to a SQL 2000 cluster. The verbose log is
too big to dump here so I have only supplied the end bit of the log.

Thanks in advance!


go
drop procedure [sp_MSupd_tbl_RelatedArticle_RA]
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_tbl_RelatedArticle_RA') drop proc
[sp_MSupd_tbl_RelatedArticle_RA]
go
create procedure [sp_MSupd_tbl_RelatedArticle_RA]
@c1 int,@c2 int,@c3 datetime,@c4 varchar(100),@c5 datetime,@c6
varchar(100),@pkc1 int,@pkc2 int
,@bitmap binary(1)
as
if substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,1) & 2 = 2
begin
update [tbl_RelatedArticle_RA] set
[RA_AR_ID] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [RA_AR_ID]
end
,[RA_AR_ID_Related] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
[RA_AR_ID_Related] end
,[RA_RowAddedDateTime] = case substring(@bitmap,1,1) & 4 when 4 then @c3
else [RA_RowAddedDateTime] end
,[RA_RowAddedUserName] = case substring(@bitmap,1,1) & 8 when 8 then @c4
else [RA_RowAddedUserName] end
,[RA_RowUpdatedDateTime] = case substring(@bitmap,1,1) & 16 when 16 then @c5
else [RA_RowUpdatedDateTime] end
,[RA_RowUpdatedUserName] = case substring(@bitmap,1,1) & 32 when 32 then @c6
else [RA_RowUpdatedUserName] end
where [RA_AR_ID] = @pkc1 and [RA_AR_ID_Related] = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [tbl_RelatedArticle_RA] set
[RA_RowAddedDateTime] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else
[RA_RowAddedDateTime] end
,[RA_RowAddedUserName] = case substring(@bitmap,1,1) & 8 when 8 then @c4
else [RA_RowAddedUserName] end
,[RA_RowUpdatedDateTime] = case substring(@bitmap,1,1) & 16 when 16 then @c5
else [RA_RowUpdatedDateTime] end
,[RA_RowUpdatedUserName] = case substring(@bitmap,1,1) & 32 when 32 then @c6
else [RA_RowUpdatedUserName] end
where [RA_AR_ID] = @pkc1 and [RA_AR_ID_Related] = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
drop procedure [sp_MSdel_tbl_RelatedArticle_RA]
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_tbl_RelatedArticle_RA') drop proc
[sp_MSdel_tbl_RelatedArticle_RA]
go
create procedure [sp_MSdel_tbl_RelatedArticle_RA] @pkc1 int,@pkc2 int
as
delete [tbl_RelatedArticle_RA]
where [RA_AR_ID] = @pkc1 and [RA_AR_ID_Related] = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
\\HAYMRK-COM-09\replication\unc\HAYMRK-COM-09_HaymarketMotoring2004_Q3_Hayma
rketmotoring2004_5b7a8c4d\20060405172206\tbl_ArticleImage_AI_49.sch[4/5/2006
7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: drop table
"tbl_RelatedArticle_RA"



[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: CREATE TABLE
"tbl_RelatedArticle_RA" (
"RA_AR_ID" "int" NOT NULL ,
"RA_AR_ID_Related" "int" NOT NULL ,
"RA_RowAddedDateTime" "datetime" NOT NULL ,
"RA_RowAddedUserName" "varchar" (100) NOT NULL ,
"RA_RowUpdatedDateTime" "datetime" NOT NULL ,
"RA_RowUpdatedUserName" "varchar" (100) NULL
)

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: drop procedure
"sp_MSins_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: if exists (select
* from sysobjects where type = 'P' and name =
'sp_MSins_tbl_RelatedArticle_RA') drop proc
"sp_MSins_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: create procedure
"sp_MSins_tbl_RelatedArticle_RA" @c1 int,@c2 int,@c3 datetime,@c4
varchar(100),@c5 datetime,@c6 varchar(100)

AS
BEGIN


insert into "tbl_RelatedArticle_RA"(
"RA_AR_ID", "RA_AR_ID_Related", "RA_RowAddedDateTime",
"RA_RowAddedUserName", "RA_RowUpdatedDateTime", "RA_RowUpdatedUserName"
)

values (
@c1, @c2, @c3, @c4, @c5, @c6
)


END

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: drop procedure
"sp_MSupd_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: if exists (select
* from sysobjects where type = 'P' and name =
'sp_MSupd_tbl_RelatedArticle_RA') drop proc
"sp_MSupd_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: create procedure
"sp_MSupd_tbl_RelatedArticle_RA"
@c1 int,@c2 int,@c3 datetime,@c4 varchar(100),@c5 datetime,@c6
varchar(100),@pkc1 int,@pkc2 int
,@bitmap binary(1)
as
if substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,1) & 2 = 2
begin
update "tbl_RelatedArticle_RA" set
"RA_AR_ID" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "RA_AR_ID"
end
,"RA_AR_ID_Related" = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
"RA_AR_ID_Related" end
,"RA_RowAddedDateTime" = case substring(@bitmap,1,1) & 4 when 4 then @c3
else "RA_RowAddedDateTime" end
,"RA_RowAddedUserName" = case substring(@bitmap,1,1) & 8 when 8 then @c4
else "RA_RowAddedUserName" end
,"RA_RowUpdatedDateTime" = case substring(@bitmap,1,1) & 16 when 16 then @c5
else "RA_RowUpdatedDateTime" end
,"RA_RowUpdatedUserName" = case substring(@bitmap,1,1) & 32 when 32 then @c6
else "RA_RowUpdatedUserName" end
where "RA_AR_ID" = @pkc1 and "RA_AR_ID_Related" = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "tbl_RelatedArticle_RA" set
"RA_RowAddedDateTime" = case substring(@bitmap,1,1) & 4 when 4 then @c3 else
"RA_RowAddedDateTime" end
,"RA_RowAddedUserName" = case substring(@bitmap,1,1) & 8 when 8 then @c4
else "RA_RowAddedUserName" end
,"RA_RowUpdatedDateTime" = case substring(@bitmap,1,1) & 16 when 16 then @c5
else "RA_RowUpdatedDateTime" end
,"RA_RowUpdatedUserName" = case substring(@bitmap,1,1) & 32 when 32 then @c6
else "RA_RowUpdatedUserName" end
where "RA_AR_ID" = @pkc1 and "RA_AR_ID_Related" = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: drop procedure
"sp_MSdel_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: if exists (select
* from sysobjects where type = 'P' and name =
'sp_MSdel_tbl_RelatedArticle_RA') drop proc
"sp_MSdel_tbl_RelatedArticle_RA"

[4/5/2006 7:19:08 PM]HAYMKVSQL1\HAYMKVSQL1.Autocar2004_Q3: create procedure
"sp_MSdel_tbl_RelatedArticle_RA" @pkc1 int,@pkc2 int
as
delete "tbl_RelatedArticle_RA"
where "RA_AR_ID" = @pkc1 and "RA_AR_ID_Related" = @pkc2
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598

Applied script 'tbl_RelatedArticle_RA_37.sch'
Last transaction timestamp: 0x0001d3d400000136000100000001
Transaction seqno: 0x0001d3d400000136000100000001
Command Id: 17
Partial: 0
Type: 2
Command: drop table [tbl_Transmission_TN]
GO

Already did this - also these two servers are on the same ethernet subnet
(LAN)... do you think I should reboot the cluster? I'm going to run profiler
on the subscriber today. Also do you think a specific table could be causing
it to crash? I tried to script the creation of the tables structure from one
server to the other and got an error saying too many rows or something along
these lines for a specific table? There are about 48 tables in total that
are being replicated. I created a new publication just with these two tables
that i think may be the issue and it fails too. I'm going to try and run the
main publication today by leaving out these two tables and see if it runs.
Should i be setting these high values by creating my own profile or is
appending the various parameters to the end of the run command good enough?
If i run the 48 table publication to a new testDB the snap shot executes
fine but the transactional replication times out again which is weird.

Does SQL use the windows O/S buffer when copying data - just asking because
i know windows is rubbish at copying large amounts of data between servers.
maybe i should break the copy up into chunks? Maybe the table is quite large
and the subscriber is waiting for the copy process to begin (i.e. waiting
for buffering) although i might be barking up the wrong tree?

For a work around I am running a manual DTS of the data from server to
server - not ideal but keeps a few people off my back. This takes about
30-40 mins to complete.

Is it possible that a specific table could be causing this? Although I would
not expect the snap shot to run either then...

Thanks

[quoted text, click to view]