Post reply

How to replicate CREATE/DROP table in transactional replication ?

  • June 17, 2014 at 12:46 pm

    #286670

    Version : SQL Server 2008 R2 (SP2) - 10.50.4286.0 (X64) Enterprise Edition

    Type : Transactional, Push

    Initialized from: backup.

    I can replicate only ALTER TABLE. But need also DROP and CREATE. If I try to create, it simply does not replicate. If I try to drop, I get a message "Cannot drop the table 'dbo.xxxx' because it is being used for replication."

    What changes should I make to my replication?

    Thanks

  • June 17, 2014 at 12:59 pm

    #1722461

    CREATE - once created add it to the articles published and run the snapshot agent. It *should* just snapshot the new table

    DROP - remove the table from the articles published and then drop it normally.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 17, 2014 at 1:16 pm

    #1722479

    We make production deployment at least once a week. So for each deployment I need to update my script to add or remove tables from articles ? And since it is initialized from backup, not snapshot, I need to run backup/restore and 2 system S.P. to re-initialize replication?

    And what about stored procedures, views? The same as for tables?

  • June 17, 2014 at 1:53 pm

    #1722500

    You should be able to use the snapshot agent as it's just a couple of tables, but test and see.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply