Replication Error after Upgrade (7.0-2000)

Scenario: 3 servers - one Subscriber, one Distributor, one Publisher. Transactional replication; immediate updating subscribers disallowed. Each article in its own publication... independent agents.
Subscriber & Publisher = SQL 7.0
Distributor recently upgraded to 2000, SP3a.
Issue: new publications created at publisher fail to initialize snapshot agent properly. Error: "Another snapshot agent for the publication is running". Agent then fails after total retries (default is 10... does not matter what I set it to).
All existing publications work fine; all existing subscriptions work fine.
So what's the deal?
It appears that once you upgrade the Distributor(s), you have to upgrade the Publisher(s) immediately thereafter if you wish to create new publications. I have not seen that documented however... anyone know?
Thanks!
X
This is documented somewhat. Check out
http://msdn.microsoft.com/library/de...grade_45ir.asp
It doesn't say do this immediately though.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Scenario: 3 servers - one Subscriber, one Distributor, one Publisher.
Transactional replication; immediate updating subscribers disallowed. Each
article in its own publication... independent agents.
> Subscriber & Publisher = SQL 7.0
> Distributor recently upgraded to 2000, SP3a.
> Issue: new publications created at publisher fail to initialize snapshot
agent properly. Error: "Another snapshot agent for the publication is
running". Agent then fails after total retries (default is 10... does not
matter what I set it to).
> All existing publications work fine; all existing subscriptions work fine.
> So what's the deal?
> It appears that once you upgrade the Distributor(s), you have to upgrade
the Publisher(s) immediately thereafter if you wish to create new
publications. I have not seen that documented however... anyone know?
> Thanks!
> X
|||Thanks for the response Hilary.
I read that article; it doesn't specify that publishers MUST be upgraded to 2000 once the Distributor has been upgraded. That's the issue here: we have an upgraded Distributor, but we cannot upgrade the Publisher for some time. In the meantime, we need to
create new publications. We are unable to do so however, due to the error I outlined. It APPEARS that once you upgrade your Distributor, you have to upgrade your Publisher before you can create new publications. That doesn't seem right, but I have yet to
find evidence of its validity.
FWIW: one of the DBAs here reported the same error after applying SPs/hotfixes on the Distributor w/o applying same to Publisher.
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||FYI: the following URL shows the configuration I outlined as a 'valid replication topology' http://msdn.microsoft.com/library/de...limpl_4joy.asp
However, it doe not indicate that new publications created on the 7.0 Publisher w/ 2k Distributor and 7.0 Subscriber(s) will fail. On the contrary - it leads one to believe that the new publications will be in 70 compatability mode, as the replication top
ology defaults to the lowest level in a mixed environment.
Odd then, that I get this error. (btw, we have recycled the servers)
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||Let me try to repro this tonight.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> Thanks for the response Hilary.
> I read that article; it doesn't specify that publishers MUST be upgraded
to 2000 once the Distributor has been upgraded. That's the issue here: we
have an upgraded Distributor, but we cannot upgrade the Publisher for some
time. In the meantime, we need to create new publications. We are unable to
do so however, due to the error I outlined. It APPEARS that once you upgrade
your Distributor, you have to upgrade your Publisher before you can create
new publications. That doesn't seem right, but I have yet to find evidence
of its validity.
> FWIW: one of the DBAs here reported the same error after applying
SPs/hotfixes on the Distributor w/o applying same to Publisher.[vbcol=seagreen]
> "Hilary Cotter" wrote:
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Each[vbcol=seagreen]
snapshot[vbcol=seagreen]
not[vbcol=seagreen]
fine.[vbcol=seagreen]
upgrade[vbcol=seagreen]
|||Were you able to reproduce the error?
Many thanks!!
X
"Hilary Cotter" wrote:

> Let me try to repro this tonight.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> to 2000 once the Distributor has been upgraded. That's the issue here: we
> have an upgraded Distributor, but we cannot upgrade the Publisher for some
> time. In the meantime, we need to create new publications. We are unable to
> do so however, due to the error I outlined. It APPEARS that once you upgrade
> your Distributor, you have to upgrade your Publisher before you can create
> new publications. That doesn't seem right, but I have yet to find evidence
> of its validity.
> SPs/hotfixes on the Distributor w/o applying same to Publisher.
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> Each
> snapshot
> not
> fine.
> upgrade
>
>
|||No, I was unable to get the error. What error did you get, and what service
packs were you running?
Where you using an Administrative Link password?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:C359EA3E-0ED4-417C-B1A9-F4F015253BD0@.microsoft.com...[vbcol=seagreen]
> Were you able to reproduce the error?
> Many thanks!!
> X
>
> "Hilary Cotter" wrote:
upgraded[vbcol=seagreen]
we[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
upgrade[vbcol=seagreen]
create[vbcol=seagreen]
evidence[vbcol=seagreen]
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Publisher.[vbcol=seagreen]
disallowed.[vbcol=seagreen]
is[vbcol=seagreen]
does[vbcol=seagreen]
work[vbcol=seagreen]
know?[vbcol=seagreen]
sql

replication and user triggers

Hello,
The SQL Server manual states you need special handling
when you have a user trigger (at publisher only in my
config) and immediate updating:
"If both the user-defined trigger and the immediate
updating trigger apply an update to the same row and you
have not included a subroutine for special case handling,
the transaction could terminate. Without special handling,
the update process continues in a loop with each trigger
update firing the other trigger until the maximum nesting
level (32) is reached and the transaction terminates. "
This is my problem. MS gives a solution in the online
manual, which I cannot get to work:
DECLARE @.retcode int, @.trigger_op char(10)
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.tablename sysname, @.trigger_op OUTPUT
IF @.retcode = 1 RETURN
A sample would be much appreciated, as the help does not
include the @.tablename parameter
for "sp_check_for_sync_trigger".
Tx
Todd
Todd,
I think this documentation is incorrect (as it is in a page on BOL -
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\rep
lsql.chm::/reploptions_4pdf.htm).
In the code for the system procedure you're calling, the correct argument
list is:
create proc sp_check_for_sync_trigger
(
@.tabid int,
@.trigger_op char(10) = NULL OUTPUT
)
You can get the tabid using the object_id() function. The output argument
returns ins, upd or del, depending on where this is called from.
HTH,
Paul Ibison
|||Paul, thank you.

>--Original Message--
>Todd,
>I think this documentation is incorrect (as it is in a
page on BOL -
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\rep
>lsql.chm::/reploptions_4pdf.htm).
>In the code for the system procedure you're calling, the
correct argument
>list is:
>create proc sp_check_for_sync_trigger
>(
> @.tabid int,
> @.trigger_op char(10) = NULL OUTPUT
>)
>You can get the tabid using the object_id() function. The
output argument
>returns ins, upd or del, depending on where this is
called from.
>HTH,
>Paul Ibison
>
>.
>
|||Hello,
I'm still having problems with the trigger continually
calling itself. I know this has something to do with
replication, because the triggers work fine before
enabling replication. I have a set of nested triggers, one
on a BANNER_TAB table, and the second on a BANNER_PARENT
table. The BANNER_TAB update trigger causes the
BANNER_PARENT trigger to fire, but then BANNER_PARENT gets
caught in a continous loop until it reaches the max 32
loops. The BANNER_PARENT update trigger is updating a
field that is part of the primary key. Here's my triggers,
any suggestions?
/*--*/
create trigger smis.tU_BANNER_TAB on smis.BANNER_TAB after
update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_TAB UPDATE'
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.numrows integer
declare @.old_banner_key integer
declare @.new_banner_key integer
select @.old_banner_key=banner_key from deleted
select @.new_banner_key=banner_key from inserted
if @.new_banner_key <> @.old_banner_key
begin
begin transaction
print 'Updating TRADE table banner key'
update smis.trade set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating SITE_PRIORITY table banner key'
update smis.site_priority set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating DOTSUMMARY table banner key'
update smis.dotsummary set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BAN_ATT table banner key'
update smis.ban_att set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating ANALOGUE table banner key'
update smis.analogue set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BANNER_PARENT table banner key'
update smis.banner_parent set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating STORE_MEASURE table banner key'
update smis.store_measure set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating PROPOSED table banner key'
update smis.proposed set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating ANA_STATUS table banner key'
update smis.ana_status set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating CONTRACT table banner key'
update smis.contract set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
commit transaction
end
if update(date_open)
begin
begin transaction
--if (select ban_status from deleted) = 'ACT'
--begin
-- synchronize first store_measure record with
date_open
if (select count(*) from smis.store_measure as
sm, inserted as i
where sm.banner_key=i.banner_key and
sm.date_updat=
(select min(date_updat)from
smis.store_measure
where banner_key=i.banner_key) and
sm.date_updat=i.date_open) = 0
begin
print 'Updating STORE_MEASURE table for
date_open'
update smis.STORE_MEASURE set
date_updat=b.date_open
from smis.store_measure sm,
smis.banner_tab b, inserted as i
where b.banner_key=i.banner_key
and b.banner_key=sm.banner_key
and sm.date_updat=(select min(date_updat)
from smis.store_measure
where banner_key=i.banner_key)
end
-- synchronize date_updat in banner_parent
if (select count(*)from smis.banner_parent as
bp, inserted as i
where bp.banner_key=i.banner_key and
bp.ban_parent_dt=
(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.date_open) = 0
begin
print 'Updating BANNER_PARENT table for
date_open'
update smis.banner_parent set
ban_parent_dt=b.date_open,date_updat=getdate()
from smis.banner_parent bp,banner_tab
b,inserted i
where b.banner_key=i.banner_key
and b.banner_key=bp.banner_key
and bp.ban_parent_dt=(select min
(ban_parent_dt) from smis.banner_parent
where banner_key=i.banner_key)
end
--end
commit transaction
end
print 'END BANNER_TAB UPDATE'
end
GO
/*--*/
create trigger smis.tU_BANNER_PARENT on smis.BANNER_PARENT
for update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_PARENT UPDATE'
print 'nest level = ' + cast(@.@.nestlevel as char(4))
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.cnt as smallint
-- test if the original date_updat is equal to the first
entry date_updat
select @.cnt = count(*) from smis.banner_parent bp
inner join inserted i on
bp.banner_key=i.banner_key where
bp.ban_parent_dt=(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.ban_parent_dt
if (@.cnt = 1)
begin
print 'There is only one parent company record --
resetting it to match the banner date open'
update smis.banner_parent set
ban_parent_dt=b.date_open
from smis.banner_parent bp, smis.banner_tab b,
inserted i2
where b.banner_key=bp.banner_key
and b.banner_key=i2.banner_key
and bp.ban_parent_dt=i2.ban_parent_dt
end
print 'END BANNER_PARENT UPDATE'
end
GO
|||Todd,
my suspicion is that there is an error in the stored procedure provided:
sp_check_for_sync_trigger. This procedure will work on the subscriber but
not the publisher. Even though it works on hte subscriber, the maximum
nesting level is reached on the publisher so the update still fails.
I have just tested this. In the code I noticed that it looks for names
starting with 'trg_MSsync_upd'; this type of trigger name applies to the
subscriber, but on the publisher the name is more like
"sp_MSsync_upd_trig_tTrigger_". I believe you'll have to create your own
version of this procedure and call it yourself to have this work. This is
actually quite easy if you look at the code, as you just need another three
queries built like the three provided, but with modified trigger names.
HTH,
Paul Ibison