|
Home > Archive > MS SQL Server Replication > July 2005 > Replicated Table cannot alter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Replicated Table cannot alter
|
|
| SQL Replication Guy 2005-07-20, 9:23 am |
| I have setup transactional replication between two servers and it is working
just fine.
When I perform alter table and add column it works just fine but when I
alter table and disable trigeer it doesnt let me do saying table is been used
by replication.
Any advise...
| |
| Paul Ibison 2005-07-20, 11:23 am |
| Do you want your new column to be replicated? If so, you'll need to use
sp_repladdcolumn. As for the trigger, you could drop the article from the
publication, add the trigger then add the article back and reinitialize it.
this can be done on an article basis rather than the whole sunscription
reinitialized if you use sp_dropsubscription and sp_droparticle as below.
It might be possible to edit the flag on the table in the system tables and
add the trigger then reedit the flag (using replinfo to 0 in sysobjects for
the particular table then rtesetting back to the original value) however
this'll need testing and you'll probably have to stop all agents while you
do this.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
exec sp_dropsubscription @publication = 'tTestFNames'
, @article = 'tEmployees'
, @subscriber = 'RSCOMPUTER'
, @destination_db = 'testrep'
exec sp_droparticle @publication = 'tTestFNames'
, @article = 'tEmployees'
Add trigger
exec sp_addarticle @publication = 'tTestFNames'
, @article = 'tEmployees'
, @source_table = 'tEmployees'
exec sp_addsubscription @publication = 'tTestFNames'
, @article = 'tEmployees'
, @subscriber = 'RSCOMPUTER'
, @destination_db = 'testrep'
| |
| SQL Replication Guy 2005-07-20, 1:23 pm |
| thanks for the reply, my problem here is disabling trigger and enabling
trigger on replicated table is performed as part of one of the stored
procedure that will be executed periodically every day. The only way so far i
was able to execute the stored proc is by commenting that alter table disable
trigger line...
Any advise...
"Paul Ibison" wrote:
> Do you want your new column to be replicated? If so, you'll need to use
> sp_repladdcolumn. As for the trigger, you could drop the article from the
> publication, add the trigger then add the article back and reinitialize it.
> this can be done on an article basis rather than the whole sunscription
> reinitialized if you use sp_dropsubscription and sp_droparticle as below.
>
> It might be possible to edit the flag on the table in the system tables and
> add the trigger then reedit the flag (using replinfo to 0 in sysobjects for
> the particular table then rtesetting back to the original value) however
> this'll need testing and you'll probably have to stop all agents while you
> do this.
>
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
> exec sp_dropsubscription @publication = 'tTestFNames'
> , @article = 'tEmployees'
> , @subscriber = 'RSCOMPUTER'
> , @destination_db = 'testrep'
>
> exec sp_droparticle @publication = 'tTestFNames'
> , @article = 'tEmployees'
>
> Add trigger
>
> exec sp_addarticle @publication = 'tTestFNames'
> , @article = 'tEmployees'
> , @source_table = 'tEmployees'
>
> exec sp_addsubscription @publication = 'tTestFNames'
> , @article = 'tEmployees'
> , @subscriber = 'RSCOMPUTER'
> , @destination_db = 'testrep'
>
>
>
| |
| Paul Ibison 2005-07-20, 8:24 pm |
| Probably the simplest method is to have the trigger check a flag (record in
a lookup table) that is updated by the stored procedure call.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|
|
|
|
|