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 Topology Question

I have three locations each with multiple SQL 2000 databases that need to
stay in constant sync with the other locations. Which is the best type of
replication? Merge Replication or 2-way Transactional Replication?
TIA,
Kevin
Probably peer to peer in SQL 2005. However bi-directional transactional
would probably be best. Keep in mind you are locked into a schema with this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>I have three locations each with multiple SQL 2000 databases that need to
>stay in constant sync with the other locations. Which is the best type of
>replication? Merge Replication or 2-way Transactional Replication?
> TIA,
> Kevin
>
|||Schema issues are the reason I posted this question in the first place. In
my test scenario, I have 3 servers doing merge replication back and forth to
each other. Each server subscribes to the other two. Everything was working
fine until I tried to add a column for testing. I got the following error
message:
Schema replication failed because database X on server X is not the original
Publisher of table
Any ideas?
Kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> Probably peer to peer in SQL 2005. However bi-directional transactional
> would probably be best. Keep in mind you are locked into a schema with
> this.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
|||Do you have a hierarchy? IE Server A publishes to Server B which publishes
to Server C? Is so you must make the schema change at the top of the
hierarchy.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Schema issues are the reason I posted this question in the first place. In
> my test scenario, I have 3 servers doing merge replication back and forth
> to each other. Each server subscribes to the other two. Everything was
> working fine until I tried to add a column for testing. I got the
> following error message:
> Schema replication failed because database X on server X is not the
> original Publisher of table
> Any ideas?
> Kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||I do not have a heirarchy. The topology I would like to use is a sort of
triangle, with no one server being the master and all transactions shared.
Thanks,
kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Do you have a hierarchy? IE Server A publishes to Server B which publishes
> to Server C? Is so you must make the schema change at the top of the
> hierarchy.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
|||You are talking about a multi-master topology which merge replication does
not support. You will need to use peer-to-peer in SQL Server 2005 for this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:eemppbHTHHA.1036@.TK2MSFTNGP03.phx.gbl...
>I do not have a heirarchy. The topology I would like to use is a sort of
>triangle, with no one server being the master and all transactions shared.
> Thanks,
> kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Kevin - just checking why you don't set up one merge publisher and 2 merge
subscribers. Is it because we don't have alternative sync partners anymore
and you can't guarantee connectivity with the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes Paul, I need sort of a triangle setup with each node working
independently and then sharing information with the other two.
Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
quick look at the replication options on our 2005 boxes on Friday and didn't
see anything about Peer-to-Peer.
Kevin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Kevin - just checking why you don't set up one merge publisher and 2 merge
> subscribers. Is it because we don't have alternative sync partners anymore
> and you can't guarantee connectivity with the publisher?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||It is only available on the Enterprise Editions of SQL Server 2005.
You will be able to get it working using pure bi-directional transactional
replication but will probably have to do some filtering to get it to work
correctly.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e08ed4qTHHA.600@.TK2MSFTNGP05.phx.gbl...
> Yes Paul, I need sort of a triangle setup with each node working
> independently and then sharing information with the other two.
> Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
> quick look at the replication options on our 2005 boxes on Friday and
> didn't see anything about Peer-to-Peer.
> Kevin
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
>
|||Kevin,
what you are proposing is not in conflict with the merge setup, as you
mention the data is exchanges with the other 2 nodes. If this is a
prerequisite, then the connectivity must exist and the merge setup remains
an option. It doesn't perform any where near as well as peer to peer (only
enterpriose as you mentioned
http://msdn2.microsoft.com/en-us/library/ms143761.aspx) , but has the
advantage of easily adapting to schema changes and conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

replication Timestamp Field

Hi,
I like to know if i change the datatype from timestamp data type to binary(8) will it cause any interruption in application to run. And is there a way to replicate timestamp field to reporting databse without changing its datatype and data in timestamp field remains same
thx
DaveWell, since we don't know your application, and have no idea what kind of
events might cause "any interruption in application," your best bet is to
TRY IT in an isolated environment.
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:BD2EC7D2-4981-4455-B6FD-FC76B8780EFA@.microsoft.com...
> Hi,
> I like to know if i change the datatype from timestamp data type to
binary(8) will it cause any interruption in application to run. And is
there a way to replicate timestamp field to reporting databse without
changing its datatype and data in timestamp field remains same
> thx
> Dave|||Are you talking about changing it in the source (production) or the destination (report) server?
If source, then be very careful. Changing it will make it a "passive" binary(8). I.e., SQL Server
will not change the value for the row each time it is updated. And all your applications concurrency
mechanism will break down. This is assuming that the app actually uses the timestamp column for
concurrency.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:BD2EC7D2-4981-4455-B6FD-FC76B8780EFA@.microsoft.com...
> Hi,
> I like to know if i change the datatype from timestamp data type to binary(8) will it cause any
interruption in application to run. And is there a way to replicate timestamp field to reporting
databse without changing its datatype and data in timestamp field remains same
> thx
> Dave|||Yes Application does use it in aid to updating that table and that is on production server only.
i am talking about to change timestamp to binary on destination (Report ) server for transactional replication to work,
But temporarily just in case if production server fails then i need to change that binary(8) column to Timestamp and restore it on production server.
i did some test on pubs db. creating a Timaestamp and Binary column. so in the first screen shot
Name Test_Column_1_in_TS Test_Column_2_in_BIN
1) A 0x00000000000010E1 NULL
2) B 0x00000000000010E2 NULL
3) C 0x00000000000010E3 NULL
4) D 0x00000000000010E4 NULL
5) E 0x00000000000010E5 NULL
6) F 0x00000000000010E6 NULL
7) G 0x00000000000010E7 NULL
8) H 0x00000000000010E8 NULL
After Swapping the type of test columns 1 and 2 (similar scenario after setting up replication)
Name Test_Column_1_in_BIN Test_Column_2_in_TS
1) A 0x00000000000010E1 0x00000000000010E9
2) B 0x00000000000010E2 0x00000000000010EA
3) C 0x00000000000010E3 0x00000000000010EB
4) D 0x00000000000010E4 0x00000000000010EC
5) E 0x00000000000010E5 0x00000000000010ED
6) F 0x00000000000010E6 0x00000000000010EE
7) G 0x00000000000010E7 0x00000000000010EF
8) H 0x00000000000010E8 0x00000000000010F0
now considering the scenario of production server fails/unrecoverable so restoring from report server
after changing the data type
Name Test_Column_1_in_BIN Test_Column_2_in_TS
1) A 0x00000000000010F1 0x00000000000010E9
2) B 0x00000000000010F2 0x00000000000010EA
3) C 0x00000000000010F3 0x00000000000010EB
4) D 0x00000000000010F4 0x00000000000010EC
5) E 0x00000000000010F5 0x00000000000010ED
6) F 0x00000000000010F6 0x00000000000010EE
7) G 0x00000000000010F7 0x00000000000010EF
8) H 0x00000000000010F8 0x00000000000010F0
this is how values are changing. and it also reflects the transactional changes made in the rows. so i think value will change but it will maintain the order in which rows r being changed... do u guys agree'
thx
Dave

Replication stopping.

I am with a problem, therefore I criated a replication of the transacional type to instantaneo and passes some days and stop and it does not replicated more appears the message: "no replicated transactions are available" it is necessary to reinicar snapsh
ot.
Standard SQL 2000 + SP3a
Somebody can help me?
Carla,
sounds like a problem with the log reader.
Can you stop the log reader agent and use sp_replcmds to see if it returns
any rows or an error message.
Regards,
Paul Ibison
|||No replicated transactions are available means 1 of two things
1) all transactions are distributed to the subscriber, ie no updates have been made on the publisher recently
2) as Paul points out the log reader agent has stopped or is hung. To check to see if the log reader agent is hung run a sp_repltrans to see if anything is returned. If something is returned, run it again to see if the results set decreases. If it increas
es your log reader agent is hung, if it decreases or changes your log reader agent is working fine.
Sp_browsereplcmds is good to detect distribution agent problems, but is expensive to run.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Carla" wrote:

> I am with a problem, therefore I criated a replication of the transacional type to instantaneo and passes some days and stop and it does not replicated more appears the message: "no replicated transactions are available" it is necessary to reinicar snap
shot.
> Standard SQL 2000 + SP3a
> Somebody can help me?
sql

replication merge not put instantaneous time out

replication type merge
suscription two(a-b)
function suscription (a)
not function suscription (b)
What this is the problem !! Help
error: replication merge not put instantaneous time out
thanks!
Christian,
please can you post up the complete error text from the merge agent, and
also list any distinctions between subscriber a and b.
TIA,
Paul Ibison

Replication in SQL Server 2000

Hi,
I have Server A(Publishing Server) and Server B(Subscribed Server)
which are in replication. The replication type used is "Transactional
Push Model".
My requirement is to
1. Drop Table1 which is available in both Server A and Server B
2. Drop Table2 which is available in Server A and not in Server B
I have to do this without stopping replication across servers.
Kindly share any information that could be useful.
Thank you,
Praveen
Praveen,
in your post you don't mention which table(s) is involved in replication. If
I assume that tables 1 and 2 are both replicated, then this is strange as it
means server B hasn't initialized yet. Anyway, in general you need to run
sp_droparticle. However this only is acceptable if there aren't any
subscribers to an article, so you run sp_dropsubscription to remove the
subscriptions to a particular article, then sp_droparticle. After that you
delete the table as per usual (drop table table1 etc).
HTH,
Paul Ibison