Tag Archives: Articles

Replication – Script – Dropping a table from an existing publication in SQL Server

In this article I will share the script that I use while dropping a table from an existing publication.
Below is the script that helps us achieve the same.

use [databasename]--Name of the DB in replication
go
exec sp_dropsubscription @publication=N'NameOfPublication',
@article=N'TestTable',--name of the table to be dropped
@subscriber=N'all'
go
use [databasename]
go
exec sp_droparticle @publication = N'NameOfPublication',
@article = N'TestTable',--name of the table to be dropped
@force_invalidate_snapshot = 1
go

Technical reference:
The details for the stored procedure sp_dropsubscription can be found in the below link
http://technet.microsoft.com/en-us/library/ms184385.aspx

Replication – Script – How to add a table to an existing publication in SQL Server

In this article i will share the script that I use when I add a table to an existing publication in SQL Server.
The below script would help us to add any table to an existing publication. The most important thing about adding a table to replication is that the table must have a primary key.

Below is the TSQL script to achieve the same.

use [databasename]--Name of the table involved in Replication
Go
exec sp_addarticle @publication = N'NameOfPublication',
@article = N'TestTable', @source_owner = N'dbo',
@source_object = N'TestTable', 
@type = N'logbased',@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008035DDB,
@force_invalidate_snapshot = 1,
@identityrangemanagementoption = N'manual',
@destination_table = N'TestTable',
@destination_owner = N'dbo', @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboTestTable',
@del_cmd = N'CALL sp_MSdel_dboTestTable',
@upd_cmd = N'SCALL sp_MSupd_dboTestTable'
Go

More details on the used stored procedure [sp_addarticle] can be found on the below mentioned technet article

http://technet.microsoft.com/en-us/library/ms173857.aspx

%d bloggers like this: