Add stored procedure in transactional replication by script in SQL Server

Add stored procedure in transactional replication by 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 and Add Table in transactional Replication by script but today I am going to explain how can we insert stored procedure by script in already established transactional replication. I will be using the same transactional replication I have set up earlier.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

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

SET @ProcName=’uspUpdateEmployeeHireInfo’
SET @SchemaName=’HumanResources’
SET @PublisherName=’AdventureWorksPublisher’

EXEC sp_addarticle
@publication = @PublisherName,
@article = @ProcName,
@source_owner = @SchemaName,
@source_object = @ProcName,
@type = N’proc schema only’,
@description = N”,
@creation_script = N”,
@pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001,
@destination_table = @ProcName,
@destination_owner = @SchemaName,
@status = 16,
@force_invalidate_snapshot=1
GO[/sourcecode]

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)
  • Add table in transactional replication from script in SQL Server (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.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Add stored procedure in transactional replication by script in SQL Server”

Comments are closed.