April 16, 2003 at 5:47 am
Hi,
I have created merged replication. How can I change database structure without having to recreate replication job again? (Is it possible to create replication script and just run this script after I've changed db structure? If yes, how?)
Thanks.
dusan
April 16, 2003 at 7:03 am
You wan't be able to change the db structure until you drop the publication. You need to drop the publication and change the merge publish property with the sp_replicationdboption stored procedure. Then change the db structure, and recreate the publication.
April 16, 2003 at 7:10 am
You can do add/drop using sp_repladdcolumn/drop column.
Andy
April 16, 2003 at 7:28 am
How can I create replication script. Just in case...
dusan
April 16, 2003 at 8:39 am
In Enterprise Manager, select the server, then from the Tools menu choose Replication -> Generate SQL Script. Do this on both the publisher and the subscriber.
Jay Madren
Jay Madren
April 16, 2003 at 10:35 am
Yes you can change the table structure with sp_repladdcolumn, but not the db structure.
If you want, to avoid syncronizing again with a snapshot, you can re syncro withhout the snapshot. But you should pause the publishers, to avoid new transactions to take place.
April 22, 2003 at 1:02 am
Got a little how to for you. Create a table you want to add to your publication. Afterwards issue something like that (depending the options you want to use and assuming you use merge replication):
EXECUTE sp_addmergearticle
@publication = 'your_publication',
@article = 'new_table',
@source_owner = 'dbo',
@source_object = 'new_table',
@type = 'table',
@description = null,
@column_tracking = 'true',
@pre_creation_cmd = 'drop',
@creation_script = null,
@schema_option = 0x000000000000CFF1,
@article_resolver = 'your_resolver',
@subset_filterclause = null,
@resolver_info = 'your_resolver_info',
@vertical_partition = 'false',
@destination_owner = 'dbo',
@auto_identity_range = 'false',
@verify_resolver_signature = 0,
@allow_interactive_resolver = 'true',
@fast_multicol_updateproc = 'true',
@check_permissions = 0,
@force_invalidate_snapshot = 1
This will add the new table seamlessly to your publication. You won't have to do anything else. Note: you should always invalidate the snapshot if you do schema changes.
To add a single column to a table that already is published, use the following (without creating the column first):
EXECUTE sp_repladdcolumn
@source_object = 'existing_table',
@column = 'new_column',
@typetext = 'your_type (including nullability/checks/foreign keys in standard SQL syntax)',
@publication_to_add = 'existing_publication',
@force_invalidate_snapshot = 1
@force_reinit_subscription = 0
Hope this can shed some light. Best regards,
Chris.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply