Post reply

How to add a new table into existing replication in SQL 2000

  • May 14, 2008 at 4:19 am

    #95932

    Hi guys,

    I would like to add a new static table into an existing replication article in SQL 2000. I have the CREATE and UPDATE scripts all sorted but I'm a little stuck on how to include this new table in the sp_addarticle command.

    Any help would be greatly appreciated.

    Thanks,

    James

  • May 16, 2008 at 8:07 am

    #816513

    Hi James,

    As far as I'm aware you would just need the procedure sp_addArticle with the publication and article (plus any attributes that are not standard in the publication)

    Having added the article you would just run the snapshot agent and that will snapshot just the new table.

    You will also have to add subscriptions to that new article

    sp_addsubscription [ @publication = ] 'publication'

    [ , [ @article = ] 'article']

    [ , [ @subscriber = ] 'subscriber' ]

    [ , [ @destination_db = ] 'destination_db' ]

    Having created the snapshot and the subscriptions, you can run the distribution agents for those subscriptions (I'm assuming you are adding the article to exisitng subscriptions) and that will distribute the new table. You haven't said what type of replication, I have assumed transactional.

    HTH

    Graeme

  • May 18, 2008 at 4:46 pm

    #817053

    Hi Graeme,

    I am also having the same issue as James.

    Ours is Merge Replication.

    Just I would liek to add a simple table for ex: EmpNo & EmpName.

    If you don't mind cna you tell me the step by step please.

    I am a kid in Replciaiton issues.

    Thanks in advance.

    Jyothi.

  • May 30, 2008 at 9:50 am

    #822433

    Hi Graeme,

    Thanks very much for that. I've only just returned to this project today and it works a treat.

    I do however have a 'cannot drop the table 'XXXX' because it is being used for replication message returned when I run the agent. Any ideas?

    Thanks,

    James

  • May 30, 2008 at 2:19 pm

    #822549

    Check the following article...

    http://www.replicationanswers.com/AddColumn.asp

    MohammedU
    Microsoft SQL Server MVP

  • June 11, 2008 at 9:13 am

    #827337

    Thanks for your help with this, guys. The solution was:

    USE MyDB

    PRINT 'Adding MYTABLE table to replication publication in ' + db_name() + '...'

    --Add new article to publication

    EXEC sp_addarticle @publication = N'My Replication', @article = 'MYTABLE', @source_table = 'MYTABLE'

    GO

    -- Refresh subscription

    PRINT 'Refreshing replication subscription in ' + db_name() + '...'

    EXEC sp_refreshsubscriptions @publication = N'My Replication'

    GO

    PRINT 'Replication updated'

    PRINT '-----------------------------------------------------'

    Cheers,

    James

Viewing 6 posts - 1 through 5 (of 5 total)

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