Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    11

    Unanswered: Problem with Alter table disable trigger all

    Hi,

    I am replicating few production databases and I am running into a problem with "Alter table tblname disable trigger all". I get the following error: Cannot alter the table 'tblname' because it is being published for replication. I am aware that when you want to add or drop a column, you need to use some system stored procedures to do so. But how can I disable the triggers before running some queries/statement on a table that is being published to a subscriber?

    I really apreciate you help,

    Regards,

    Sas

  2. #2
    Join Date
    Mar 2004
    Posts
    2
    It seems you have to use sp_dropsubscription and then sp_droparticle to discontinue replication of the table, do you alter, then use sp_addarticle and sp_addsubscription to add continue replication. If there is an easier way, I'd be interested to know as well.

    http://www.databasejournal.com/featu...le.php/1477441

  3. #3
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    UPDATE sysobjects SET replinfo=0 WHERE xtype='U' AND name='YOUR TABLE'
    It would disable the table in the publication.
    When u r done with ur work, Add the table back into Publication through Enterprise-Manager.
    However sometimes it goes messed-up and SQL doesn't allow to add the table back into publication. In that case u may need to reinialize the subscriptions followed by the SNAPSHOT.

    Regards!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •