Transactional Replication

Transactional replication is a feature of SQL Server that replicates committed changes to the publisher database to one or more subscriber databases. Transactional replication is useful when you need data that is up to date in different locations. In this video Scott Golightly shows how to set up transactional replication. First the publisher is configured, then the distributor, next publications with articles are created, and finally a subscriber is created. The video shows making changes to the publisher database and having those changes appear at the subscriber.

Add new stored procedure in transactional replication by TSQL script in SQL Server

This script explains how we can insert a new stored procedure by script in an already established transactional replication.

USE AdventureWorks2012

GO

DECLARE @ProcName VARCHAR(25)

DECLARE @SchemaName VARCHAR(25)

DECLARE @PublisherName VARCHAR(25)

SET @ProcName='buildProcEmployeeDetails'

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