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
>

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
>

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
>

replication 18483 eroor

hi all...
I have a little problem.
"18483 - could not connect to server 'Newname' because distributor_admin is
not defined as a remote login at the server."
Mehmet,
Try (using 'Newname')
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
If this is not the solution, please let me know if you are using IP
addresses for registered servers in Enterprise Manager.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication

Hi
I have setup transaction replication, I defined publisher. Now I have new
server
Where I run create script for database and table from publisher server.
First time on my subscriber server's table has rows, I can’t restore from
backup because of time (it took about 8 hrs. to restore).
After setting up replication, my new subscriber got no rows, how tried
tranactional,snapshot replication data is populated on subscriber.
Advice me how to proceed with restoring db on my subscriber m/c.
Thanks
Kalyan
Advice me how to proceed without restoring db on my subscriber m/c.
"Kalyan" wrote:

> Hi
> I have setup transaction replication, I defined publisher. Now I have new
> server
> Where I run create script for database and table from publisher server.
> First time on my subscriber server's table has rows, I can’t restore from
> backup because of time (it took about 8 hrs. to restore).
> After setting up replication, my new subscriber got no rows, how tried
> tranactional,snapshot replication data is populated on subscriber.
>
> Advice me how to proceed with restoring db on my subscriber m/c.
> Thanks
> Kalyan
>

Replicating users and privileges...

Hi,
In my publisher database I have defined an user with restricted privileges.
This user is "used" by the user interface application to connect to the
database. How can I replicate this user and restrictions to the subscriber?
Should I script this on a post-snapshot script? Is any other automatic
solution?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
Faustino,
there's no automatic solution for this, as we can't replicate system tables.
You could use :
a pre-snapshot script,
a post-snapshot script,
sp_addscriptexec,
DTS,
linked servers.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)