Replication Triggers on replicated tables.

Howdy all. I set up Immediate Updating replication on AdventureWorks on 2005
Person.Address and AddressType tables. When this type of replication is
created, a replication trigger is created on the Publisher. However, this
replication trigger fires off the other (User) trigger on the table when a
row is updated, and they continue to fire each other off. I caught all this
in Profiler so Im sure this is whats happening. Anyways, the following
message is then displayed:
Maximum Stored Proc, function, trigger, or view nesting level exceeded
(limit 32).
Here are the triggers:
ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on [Person].[Address]
for update not for replication as
declare @.rc int
select @.rc = @.@.ROWCOUNT
if @.rc = 0 return
if update (msrepl_tran_version) return
update [Person].[Address] set msrepl_tran_version = newid() from
[Person].[Address], inserted
where [Person].[Address].[AddressID] = inserted.[AddressID]
ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Address]
SET [Person].[Address].[ModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
END;
Someone must have encountered this before and have a workaround?
TIA, ChrisR
use set trigger order to have replication fire at the end or make your
triggers not for replication.
Can we see the table schema and triggers?
Hilary Cotter
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
"ChrisR" <ChrisR@.foo.com> wrote in message
news:uN6YxlRMHHA.3556@.TK2MSFTNGP03.phx.gbl...
> Howdy all. I set up Immediate Updating replication on AdventureWorks on
> 2005 Person.Address and AddressType tables. When this type of replication
> is created, a replication trigger is created on the Publisher. However,
> this replication trigger fires off the other (User) trigger on the table
> when a row is updated, and they continue to fire each other off. I caught
> all this in Profiler so Im sure this is whats happening. Anyways, the
> following message is then displayed:
>
> Maximum Stored Proc, function, trigger, or view nesting level exceeded
> (limit 32).
>
> Here are the triggers:
>
> ALTER trigger [Person].[sp_MSsync_upd_trig_Address_1] on
> [Person].[Address] for update not for replication as
> declare @.rc int
> select @.rc = @.@.ROWCOUNT
>
> if @.rc = 0 return
> if update (msrepl_tran_version) return
> update [Person].[Address] set msrepl_tran_version = newid() from
> [Person].[Address], inserted
> where [Person].[Address].[AddressID] = inserted.[AddressID]
>
>
> ALTER TRIGGER [Person].[uAddress] ON [Person].[Address]
> AFTER UPDATE NOT FOR REPLICATION AS
> BEGIN
> SET NOCOUNT ON;
>
> UPDATE [Person].[Address]
> SET [Person].[Address].[ModifiedDate] = GETDATE()
> FROM inserted
> WHERE inserted.[AddressID] = [Person].[Address].[AddressID];
> END;
>
> Someone must have encountered this before and have a workaround?
>
>
> TIA, ChrisR
>
>
>
>

Replication to DMZ...something's missing....HELP!!

I've followed the steps in an attempt to replicate to & from the DMZ sql server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?
JLS,
is the client alias the same name as the servername?
Can you try establishing client connectivity using query analyser on the subscriber to the sql server?
Can you ping the publishing sql server from the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanx for answering. It seems the problem was with the firewall port & that has been fixed, but now I get a Login failed for user SA.
I am not using SA for this publication / subscription. I am using Sql Authentication & have setup a new account on each box called dmzrepl.
I also tried to do a linked server query & receive the exact same message about sa login failing.
What in the world am I missing?
jls
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:e5tvXzH7FHA.1864@.TK2MSFTNGP12.phx.gbl...
JLS,
is the client alias the same name as the servername?
Can you try establishing client connectivity using query analyser on the subscriber to the sql server?
Can you ping the publishing sql server from the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
I think you're almost there. Have a look at this article for the next stage: http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||YIKES! I was afraid you were going to point me in that direction....
I found this in my searching & tried it, to no avail. I am still receiving sa login failed even after following the workaround.
Any other ideas?
jls
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:eAA0tpI7FHA.3136@.TK2MSFTNGP09.phx.gbl...
JLS,
I think you're almost there. Have a look at this article for the next stage: http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I don't believe this will work. MSDTC needs RPC ports open for inbound
traffic on both sides of your DMZ.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ue1ctaF7FHA.3880@.TK2MSFTNGP12.phx.gbl...
I've followed the steps in an attempt to replicate to & from the DMZ sql
server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change
something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I
input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?
|||JLS,
please see Hilary's post. Looks like you need to open up RPC over TCP\IP and add another rule to the firewall. These links will hopefully help:
http://support.microsoft.com/default.aspx?kbid=841251
http://www.windowsitpro.com/Article/...412/13412.html
Paul Ibison
|||Paul / Hilary,
Thanx for the answers, I appreciate it. With the answer being to open RPC ports, I think we will rethink this project, as that will open a security hole we don't want open.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uhvxlPM7FHA.444@.TK2MSFTNGP11.phx.gbl...
I don't believe this will work. MSDTC needs RPC ports open for inbound
traffic on both sides of your DMZ.
Hilary Cotter
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ue1ctaF7FHA.3880@.TK2MSFTNGP12.phx.gbl...
I've followed the steps in an attempt to replicate to & from the DMZ sql
server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change
something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I
input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?

Replication says success, but tables not showing in EM?

Hello All,
I am trying to replicate data from a SQL Server (7.0) to another SQL
Server using a one-way immediate push subscription. After executing,
there are no errors in the Snapshot or Push agents, but two of the
tables are missing in the subscription database. Where did they go?
I can find the tables listed in the publication->articles tab and in
the snapshot logs on the publisher. The tables are also listed on the
subscriber database under 'Database Roles Properties' --> Permissions.
Thank You For Your Time And Help,
Nate
Hi Nate,
You may want to check whether subscriptions for the two missing tables were
really created by calling sp_helpsubscription at the publisher. If not,
manually add them by calling sp_addsubscriptions with explicit article names
and rerun snapshot + distribution agents.
-Raymond
"Nate" <nathandeneau@.braintrade.biz> wrote in message
news:1142037215.646558.84890@.i40g2000cwc.googlegro ups.com...
> Hello All,
> I am trying to replicate data from a SQL Server (7.0) to another SQL
> Server using a one-way immediate push subscription. After executing,
> there are no errors in the Snapshot or Push agents, but two of the
> tables are missing in the subscription database. Where did they go?
>
> I can find the tables listed in the publication->articles tab and in
> the snapshot logs on the publisher. The tables are also listed on the
> subscriber database under 'Database Roles Properties' --> Permissions.
>
> Thank You For Your Time And Help,
> Nate
>
|||Everything looks as it should after calling sp_helpsubscription - the
two tables are listed.
|||This looks really strange. If you check the history messages of the snapshot
agent, do you see files for the two missing tables generated? And if you
check the distribution agent history, do you see that the files for the two
missing tables applied? If the tables are relatively small, you may be able
to fix things up by reinitializing the subscription, regenerate the snapshot
and reapply it. You may also want to watch out for processes outside of
replication that may have dropped the tables at the subscriber. What are the
sync_type values of subscriptions to the missing tables?
-Raymond
sql