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

Question

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

James123

SSC Veteran

Points: 284
More actions
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

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

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

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 1

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

aruna.jyothi Grasshopper Points: 13 More actions · Answer 2

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.

James123 SSC Veteran Points: 284 More actions · Answer 3

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

MohammedU SSCoach Points: 19644 More actions · Answer 4

Check the following article...

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

MohammedU
Microsoft SQL Server MVP

James123 SSC Veteran Points: 284 More actions · Answer 5

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