Here is what I do below:
I wrap it all in 2 procs, 1 does the dropping, and the other does the
adding. The adding one loops through all the columns automatically with
some exception handling for skipping timestamps etc. It works well and
quickly. The last thing is to run a special ms proc on source machine and
paste the results onto target machine and execute. We use it daily on a
Terabyte db with transactional replication running all the time, and it has
never failed us, and fields/tables can be added/tweaked very quickly.
If you want the actual procs or help with them, let me know.
Here is the basic idea:
Step 1- Wait till replication is quite. Run these steps
declare @databasename nvarchar(64),
@table nvarchar(64)
select @databasename = @DB
select @table = @Tbl
exec sp_dropsubscription @publication= @databasename , @subscriber = 'all',
@article = @table
exec sp_droparticle @databasename, @table
Step 2-
--* Adding the article synchronization object
exec sp_addarticle @publication = @databasename, @article = @table,
@source_owner = N'dbo', @source_object = @table,
@destination_table = @table, @type = N'logbased',
@creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3, @status = 24,
@vertical_partition = N'true',
@ins_cmd = @pub_ins_cmd, @del_cmd = @pub_del_cmd,
@upd_cmd = N'SQL',
@filter = null, @sync_object = null, @auto_identity_range
= N'false'
Step 3-
--* ADD ONE OF THESE FOR EACH COLUMN in the table that you are including in
replication
--*exec sp_articlecolumn @publication = @databasename, @article = @table,
@column = '<add the column name here>', @operation = N'add'
Step 4-
--* Run this
EXEC sp_articleview @publication = @databasename, @article = @table,
@view_name = @sync_view
Step 5 -
--* ================ SUBSCRIPTION ADD START ==========================
exec sp_addsubscription @publication = @databasename,
@article = @table,
@subscriber = @subscribername,
@destination_db = @databasename,
@sync_type = N'none',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor'
Step 6 -
Print 'Now be sure to do the following final step:'
Print 'Go To Tools -- Options -- Results Tab'
Print 'Set Default Results Target= "Results To Text"'
Print 'Set Results Output Format= "Column Aligned"'
Print 'Set Maximum characters per column="8192"'
Print ' '
Print 'execute sp_scriptpublicationcustomprocs "yourDBName"'
Print 'copy results, and Paste then execute over onto Destination Server'
Print 'this guarantees that the replication insert/update/delete scripts are
identical in both places'
Print 'execute sp_scriptpublicationcustomprocs ''' + @DB + ''''
Christopher Campbell
-----Original Message-----
From: Jonathan Baker
[mailto:mssqldba-ezmlmshield-x36400610.[Email address protected]
Sent: Thursday, November 29, 2007 3:15 PM
To: LazyDBA Discussion
Subject: Dropping items from replication
I am attempting to drop a couple of views that are published articles
for transaction replication.
I would like to do this without having to drop the subscription first
(sp_droparticle won't allow). Anyone know of a work around for this?
Rebuilding the snapshot to recreate my subscription could take quite
awhile.
Jon
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page