Paul what you have will work but can cause some issues. I have compiled a list of the following scripts that we use all the time to drop tables out of your replicated dbs. Some of our replicated dbs are in the 600+ GB range across the wan so disabling replication 100% is out of the question.

--First I stop the distribution and log reader

--I then remove and rows corresponding to the article from distribution

-- I have these in stored proc form but am going to send them in non-proc form

delete from distribution..msrepl_transactions where xact_seqno in (select xact_seqno from distribution..msrepl_commands where article_id = (select artid from medical..sysarticles where name =' '))

delete from distribution..msrepl_commands where article_id = (select artid from medical..sysarticles where name =' ')

exec sp_dropsubscription @publication=' ', @subscriber='all'

exec sp_droparticle @publication=' '

exec sp_refreshsubscriptions @publication=' '

--The article is now removed from replication and any work on it can be done

To add the article back is where your scripts may have issues. Since we only use transactional replication it is the only place where I have seen the issue. The script

exec sp_addarticle @publication=' ', @force_invalidate_snapshot = 0

With transactional replication the above script creates the 3 procs for insert, update, and delete. For some reason MS has capped the length of those proc names if you do not manually specify them. We found this out due to having tables with similar names. IE. ah_table_name and ah_table_name_history. For some reason when we dropped the articles for modification and readded without specifying a name for the insert, update, and delete procs the sprocs for the two tables were given the same name.

To fix this issue I wrote the following sproc for adding articles to replication. It is still in its infant stage and is crude but gets the job done. If you have any questions please email me at josh@joshuatipton.com.

CREATE PROCEDURE dba_AddReplicationArticle

@article_name sysname,

@pub sysname = 'medical',

@article_owner sysname = 'dbo'

AS

declare @upd_cmd nvarchar(255), @del_cmd nvarchar(255), @ins_cmd nvarchar(255)

set @ins_cmd = 'CALL sp_MSins_'+@article_name

set @del_cmd = 'CALL sp_MSdel_'+@article_name

set @upd_cmd = 'MCALL sp_MSupd_'+@article_name

exec sp_addarticle @publication = @pub, @article = @article_name, @source_owner = @article_owner, @source_object = @article_name, @destination_table =@article_name,

@type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',

@ins_cmd = @ins_cmd, @del_cmd = @del_cmd, @upd_cmd = @upd_cmd, @filter = null, @sync_object = null,

@auto_identity_range = N'false'

if @@error <> 0

begin

print('Failure adding article')

return

end

else

begin

exec sp_refreshsubscriptions @publication=@pub

exec sp_reinitsubscription @publication=@pub, @article=@article_name, @for_schema_change= 1, @subscriber='all'

end

GO

--You then restart the logreader and distribution agent and start the snapshot process. The table(s) will be sent across as soon as the snapshot agent is complete.