Problems changing data in a table

Some data was erroneouely changed in a table earlier this month. The
table used to track documents that have been released for consumption.
The field I wasnt to change is called modified_date and of course
contains dates. Whenever I change the field manually and move to the
next record, the value updates to todays date. If I try an update
statement, no changes are made. I was advised this may be a trigger but
I am unable to remove the trigger due to "cannot alter the table
<table_name> because it is being published for replication". Im fairly
new to SQL Server 2000 and it feels as though I am at a dead end. The
publication is a snapshot.
My questions is, is there an easy way to change the values in the
field?
Thanks
You could drop the subscription(s), drop the article , alter the table to
disable the trigger then do the reverse, eg:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees disable trigger triggername
update tEmployees .....then the reverse (add the article and add the
subscription)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, thanks for your reply. I just browsed across your artcle on
SQLServerCentral. Can I get clarification on a couple of items?
@.publication is the name of the publication
@.article is the name of the table
@.subscriber - not sure what this is
@.destination_db is pretty much self explanitory
Also Im not sure how to get the name of the trigger. If I go to enable
the trigger, can iI use the all parameter to re-enable it?
Thanks
Paul Ibison wrote:
> You could drop the subscription(s), drop the article , alter the table to
> disable the trigger then do the reverse, eg:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> alter table tEmployees disable trigger triggername
> update tEmployees .....then the reverse (add the article and add the
> subscription)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||sp_helptrigger will give you the trigger name. You could use sp_helptext to
check it is the one you thought.
@.subscriber is just the name of the subscriber, as it appears in the
distributor properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)