Add and Change tables in Merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 29 Dec 2006 18:16 GMT

We have a merge replication setup in SQL 2000 that synchs with 2 laptops. I
need to add a new table to the database that will be included in the new
subscription as well as a change (adding a column) to a table already in the
publication. What are the steps I need to take to get the schema changes to
the 2 laptops? Thanks.

David

Reply to this Message

Paul Ibison - 29 Dec 2006 18:48 GMT

David - the new table can be added through the EM gui. Then run the snapshot
agent which will snapshot all articles but the synchronization will just
take the new article. For the new column, you can run sp_repladdcolumn.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

David - 29 Dec 2006 20:48 GMT

Thanks Paul. Will the laptop users get the new snapshots automatically or
will I have to do something special? They are using MSDE. Thanks.

David

> David - the new table can be added through the EM gui. Then run the
> snapshot agent which will snapshot all articles but the synchronization
> will just take the new article. For the new column, you can run
> sp_repladdcolumn.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Paul Ibison - 29 Dec 2006 21:27 GMT

David - they'll get the new snapshotted table the next time they
synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

David - 29 Dec 2006 20:50 GMT

Also, I have 2 publications. One has about 50% of the tables as articles and
the other has about 20-30 views and stored procs. And 1 of the tables has a
triigger update. Does any of this change your recommendation? Thanks.

David

Reply to this Message

Paul Ibison - 29 Dec 2006 21:30 GMT

David - I'm not too sure why this is being mentioned - but perhaps I'm
missing something :). From what you mentioned, you're just adding a new
table and a column. Perhaps you're saying that the procedural code will
itself need modifying? In that case I usually use sp_addscriptexec to
propagate it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

David - 29 Dec 2006 22:29 GMT

Paul - I was mostly concerned with the new trigger as it will be on a table
other than the one being added to the articles.

David

> David - I'm not too sure why this is being mentioned - but perhaps I'm
> missing something :). From what you mentioned, you're just adding a new
[quoted text clipped - 3 lines]
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

Paul Ibison - 29 Dec 2006 22:58 GMT

OK - in that case you could add this using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message