Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Showing posts with label contains. Show all posts
Showing posts with label contains. Show all posts
Wednesday, March 21, 2012
Replication and User Defined Trigger
Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Tuesday, March 20, 2012
Replication and User Defined Trigger
Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Monday, February 20, 2012
Replicating Stored Procedure Alterations
Hi,
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
Subscribe to:
Posts (Atom)