Post reply

Add Tables to Replication

  • April 14, 2011 at 2:09 am

    #143797

    Hi,

    How to had more tahn 100 tables to an existing replication?

    Now i am using sp_addarticle for each table. Is ther any other way?

    TIA

  • April 14, 2011 at 7:41 am

    #1311587

    I think you could use the GUI and select the tables with check marks.

    Is that what you're asking ?

  • April 14, 2011 at 7:58 am

    #1311608

    Try with

    Exec sp_MSforeachdb

    Not sure about it have a try.

    Thanks
    Parthi

  • April 14, 2011 at 8:46 am

    #1311661

    Ratheesh.K.Nair (4/14/2011)

    Hi,

    How to had more tahn 100 tables to an existing replication?

    Now i am using sp_addarticle for each table. Is ther any other way?

    TIA

    100 more tables? If your ending up replicating most of your database you may want to look into other types of data availability like mirroring and log shipping.

    If the extra 100 tables are not your full DB then yeah, add them through the GUI it doesnt take that long.

  • April 15, 2011 at 6:03 pm

    #1312398

    Why don't you try to identify the tables from sysobjects

    and use a cursor to use sp_addarticle for each of this tables.

    The other thing that you can do is to use MS word Mail Merge

    You add one table script it out and using mailmerge it will generate the scripts for all the other tables that you need.

    Run the script on the server and you are done.

  • April 16, 2011 at 3:38 am

    #1312423

    From experience, adding tables to a publication through the GUI is a bad idea. IT can trigger a force_reinitialisation.

    Use sp_addarticle. You will also need to re-add any subscriptions to refresh as the subscriptions are at the article level and simply adding the article does not add it to the subscriber.

    Once you've added the articles and subscription, generate a new snapshot. This will include ONLY the changes you have made to the publication.

  • April 19, 2011 at 2:04 am

    #1313205

    Ratheesh.K.Nair (4/14/2011)

    Hi,

    How to had more tahn 100 tables to an existing replication?

    Now i am using sp_addarticle for each table. Is ther any other way?

    TIA

    So many articles! Can you explain for what purpose it is being replicated? May be then we can help you in a better way!

  • July 21, 2011 at 2:23 pm

    #1356737

    I would suggest to use the system stored procedure sp_addarticle and sp_addsubscription to add your tables to your replication. You can put your 100 or more table names to a string separated each name by a comma and then write a WHILE loop to build the SQL statements to add those tables.

    --------------------------------------------------------------
    DBA or SQL Programmer? Who Knows. :unsure:

Viewing 8 posts - 1 through 7 (of 7 total)

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