December 5, 2007 at 5:40 pm
Hi All,
I have a transactional replication and want to add one column with identity seed 1 and increment 1. Many forums says that if i want to enable automatic identity range handling then i should enable it at the time of creating the publication. is there any way i can do it now, that i have a running transactional replication.
Any help with a bit of detail as to how to do it is really appreciated.
Thanks
December 10, 2007 at 11:06 pm
drop subscriptions to article / drop article / make schema changes / add subscription
exec sp_dropsubscription @publication = 'MyPubName'
, @article = 'MyTable'
, @subscriber = 'all'
exec sp_droparticle @publication = 'MyPubName'
, @article = 'MyTable'
-- make change
-- add table
exec sp_addarticle @publication = N'MyPubName', @article = N'MyTable', @source_owner = N'dbo'
, @source_object = N'MyTable', @destination_table = N'MyTable', @type = N'logbased', @creation_script = null
, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16
, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_MyTable', @del_cmd = N'CALL sp_MSdel_MyTable', @upd_cmd = N'MCALL sp_MSupd_MyTable', @filter = null
, @sync_object = null, @auto_identity_range = N'false'
GO
exec sp_addsubscription @publication = 'MyPubName'
, @article = 'MyTable'
, @subscriber = 'SvrName'
, @destination_db = 'MyDB'
, @sync_type = 'automatic'
Enjoy,
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
December 11, 2007 at 3:27 am
Amit,
If you want to simply add an identity column to your table which will be replicated then you can easily do it with sp_repladdcolumn, however, the subscriptions need reinitializing. Issue the following command in the publisher db:
sp_repladdcolumn @source_object = 'MyTable',
@column = 'NewColumn',
@typetext = ' int identity(1,1)',
@force_reinit_subscription = 1
Please note that the identity value for the current records will be created in an undefined order.
Erik
-- Erik http://blog.rollback.hu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply