Tag Archives: Add Article

Generate script to add multiple table in replication as article

Generate script to add multiple table in replication as article

I have already explained about “How to add article in replication from GUI” and “How to add article in replication from TSQL”. I am a script buddy and as long as possible I wouldn’t like to go for GUI options.

If there would be two or four objects only, we could create add article script manually but what about creating a script for many tables or objects?

Generally I used to select only one small table while setting up replication and once replication is ready with publisher, distributor and subscription, I add remaining objects (mainly tables) via script. So after setting up replication, I may need to add hundreds of table and creating add article script for all is little hectic. I have created one small script to overcome this.


SELECT

DISTINCT 'DECLARE @tableName' + Constraints.table_name+' VARCHAR(255)'

+ ' DECLARE @SchemeName' + Constraints.table_name+' VARCHAR(255)'

+ ' SET @tableName' + Constraints.table_name+'='+ '''' + Constraints.table_name + ''''

+ ' SET @SchemeName' + Constraints.table_name+'='+ '''' + Constraints.constraint_schema + ''''

+ ' exec sp_addarticle'

+ ' @publication = N''AdventureWork2012Pub'', '

+ ' @article =@tableName' + Constraints.table_name+','

+ ' @source_owner =@SchemeName' + Constraints.table_name+','

+ ' @source_object =@tableName' + Constraints.table_name+','

+ ' @type = N''logbased'', '

+ ' @description = null, '

+ ' @creation_script = null, '

+ ' @pre_creation_cmd = N''drop'', '

+ ' @schema_option = 0x000000000803509F,'

+ ' @identityrangemanagementoption = N''manual'', '

+ ' @destination_table =@tableName' + Constraints.table_name+','

+ ' @destination_owner =@SchemeName' + Constraints.table_name+','

+ ' @force_invalidate_snapshot=1 '

FROM

information_schema.table_constraints AS Constraints

JOIN

information_schema.key_column_usage AS KeyColumn

ON

Constraints.table_name=KeyColumn.table_name

AND

Constraints.constraint_name=KeyColumn.constraint_name

WHERE

Constraints.constraint_type='Primary Key'

GO

This script find all tables which has primary key and create add article script for them. Transactional Replication needs to have primary key on table to make it part of replication.

My publication name was “AdventureWork2012Pub” so I have used it in the script, you can have your own publication name there.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Add table in transactional replication from script in SQL Server

Add table in transactional replication from script in SQL Server

I have already written many articles to cover different area of replication. Few days back I have written how to add articles (Table, View, SP etc.) into replication from GUI but today I am going to explain how can we insert table by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

 USE AdventureWorks2012
 GO

DECLARE @TableName VARCHAR(25)
 DECLARE @SchemaName VARCHAR(25)
 DECLARE @PublisherName VARCHAR(25)

SET @TableName='Department'
 SET @SchemaName='HumanResources'
 SET @PublisherName='AdventureWorksPublisher'

exec sp_addarticle
 @publication = @PublisherName,
 @article = @TableName,
 @source_owner = @SchemaName,
 @source_object = @TableName,
 @type = N'logbased',
 @description = null,
 @creation_script = null,
 @pre_creation_cmd = N'drop',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'manual',
 @destination_table = @TableName,
 @destination_owner = @SchemaName,
 @force_invalidate_snapshot=1
 GO

Some more replication related articles:

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.