Specify How Changes Are Propagated for Transactional ArticlesTransactional replication allows you to specify how data changes are propagated from the Publisher to Subscribers. For each published table, you can specify one of four ways that each operation (INSERT, UPDATE, or DELETE) should be propagated to the Subscriber:
By default, transactional replication propagates changes to Subscribers through a set of stored procedures that are installed on each Subscriber. When an insert, update or delete occurs on a table at the Publisher, the operation is translated into a call to a stored procedure at the Subscriber. The stored procedure accepts parameters that map to the columns in the table, allowing those columns to be changed at the Subscriber. To set the propagation method for data changes to transactional articles, see Set the Propagation Method for Data Changes to Transactional Articles. The three procedures that replication creates by default for each table article are:
The <tablename> used in the procedure depends on how the article was added to the publication and whether the subscription database contains a table of the same name with a different owner. Any of these procedures can be replaced with a custom procedure that you specify when adding an article to a publication. Custom procedures are used if an application requires custom logic, such as inserting data into an audit table when a row is updated at a Subscriber. For more information about specifying custom stored procedures, see the how to topics listed above. If you specify the default replication procedures or custom procedures, you also specify call syntax for each procedure (replication selects defaults if you use the default procedures). The call syntax determines the structure of the parameters provided to the procedure and how much information is sent to the Subscriber with each data change. For more information, see the section "Call Syntax for Stored Procedures" in this topic. Considerations for Using Custom Stored ProceduresKeep the following considerations in mind when using custom stored procedures:
Call syntax for stored proceduresThere are five options for the syntax used to call the procedures used by transactional replication:
Each method differs in the amount of data that is propagated to the Subscriber. For example, SCALL passes in values only for the columns that are actually affected by an update. XCALL, by contrast, requires all columns (whether affected by an update or not) and all the old data values for each column. In many cases, SCALL is appropriate for updates, but if your application requires all the data values during an update, XCALL allows for this. CALL Syntax
SCALL Syntax
MCALL Syntax
XCALL Syntax
The following procedures are the default procedures created for the Vendor Table in the Adventure Works sample database. --INSERT procedure using CALL syntax create procedure [sp_MSins_PurchasingVendor] @c1 int,@c2 nvarchar(15),@c3 nvarchar(50),@c4 tinyint,@c5 bit,@c6 bit,@c7 nvarchar(1024),@c8 datetime as begin insert into [Purchasing].[Vendor]( [VendorID] ,[AccountNumber] ,[Name] ,[CreditRating] ,[PreferredVendorStatus] ,[ActiveFlag] ,[PurchasingWebServiceURL] ,[ModifiedDate] ) values ( @c1 ,@c2 ,@c3 ,@c4 ,@c5 ,@c6 ,@c7 ,@c8 ) end go --UPDATE procedure using SCALL syntax create procedure [sp_MSupd_PurchasingVendor] @c1 int = null,@c2 nvarchar(15) = null,@c3 nvarchar(50) = null,@c4 tinyint = null,@c5 bit = null,@c6 bit = null,@c7 nvarchar(1024) = null,@c8 datetime = null,@pkc1 int ,@bitmap binary(2) as begin update [Purchasing].[Vendor] set [AccountNumber] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [AccountNumber] end ,[Name] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Name] end ,[CreditRating] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [CreditRating] end ,[PreferredVendorStatus] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [PreferredVendorStatus] end ,[ActiveFlag] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [ActiveFlag] end ,[PurchasingWebServiceURL] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [PurchasingWebServiceURL] end ,[ModifiedDate] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [ModifiedDate] end where [VendorID] = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end go --DELETE procedure using CALL syntax create procedure [sp_MSdel_PurchasingVendor] @pkc1 int as begin delete [Purchasing].[Vendor] where [VendorID] = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end go |
sql | SQL Server > sqlsrv | replication >