Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Replication Statement Delivery Options - Part 1

By Andy Warren,

One of the more interesting features of transactional replication is the assortment of configuration options it has, and in particular the options that control how changes are propagated to the subscribers. Before we can learn how and when to use those options we need to understand how the defaults work, which is the focus of this article.

I've set up a simple database called ReplTest with one table called SomeNewTable, then created a transactional publication called TEST and added a single subscriber. Our starting point is the properties dialog of the publication with articles selected on the left, the SomeNewTable table highlighted, and then selecting 'Set Properties of Highlighted Table Article' from the list under the Article Properties button

By default we will get the delivery options shown below. These are all stored procedures that are created on the subscriber as part of the snapshot process. If you have multiple subscribers the options apply to each of them; there is no supported way to customize the delivery per subscriber other than that to have separate publications.

Before we start changing those, let's look at what exists on the subscriber.

Insert Procedure

ALTER procedure [dbo].[sp_MSins_dboSomeNewTable] 
  @c1 varchar(150),@c2 varchar(50),@c3 varchar(50)
as 
begin 
insert into [dbo].[SomeNewTable]( 
 [EmailAddress]
,[FirstName]
,[LastName]
 )
values ( 
 @c1
,@c2
,@c3
 ) 
end

Updated Procedure

ALTER procedure [dbo].[sp_MSupd_dboSomeNewTable] 
 @c1 varchar(150) = null,@c2 varchar(50) = null,@c3 varchar(50) = null,@pkc1 varchar(150)
,@bitmap binary(1)
as
begin
if ( substring(@bitmap,1,1) & 1 = 1 )
begin
update [dbo].[SomeNewTable] set 
 [EmailAddress] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [EmailAddress] end
,[FirstName] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [FirstName] end
,[LastName] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [LastName] end
where [EmailAddress] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
else
begin
update [dbo].[SomeNewTable] set 
 [FirstName] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [FirstName] end
,[LastName] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [LastName] end
where [EmailAddress] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
end

Delete Procedure

ALTER procedure [dbo].[sp_MSdel_dboSomeNewTable] 
  @pkc1 varchar(150)
as 
begin 
delete [dbo].[SomeNewTable]
where [EmailAddress] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end 

Of the three procedures above the insert procedure is easy to follow, it's just a good clean insert statement. You'll note that there is no error checking. If the insert fails for any reason (row already exists, columns don't match, etc) the distribution agent will report a failure. If we look at the delete statement we see it is doing a simple primary key based delete and that it checks the rowcount - if the rowcount is zero or any 'real' error occurs the distribution agent will report a failure. The update procedure is the most complex of the three. It gets passed a value for each column in the table along with a bitmap that indicates which columns were really changed. It then branches based on whether the primary key is being updated. Then based on the bitmap it will set each column to either the passed in value or the existing value when it finally runs the update. Here again the distribution agent fails if any error is found, including an update that doesn't find a matching row on the subscriber.

Here is the error that gets reported:

There are a couple points that will be interesting to power users. The first is that these procedures are executed once per row on each subscriber. If you insert, update, or delete 1000 rows on the publisher, these stored procedures will get called 1000 times. The second is that you can change these procedures manually and as long as you preserve the calling signature (same parameter names and same data types), what you do with the passed data or how you do it is entirely up to you. For example, I've worked on several projects where we had a requirement to not replicate deletes to the subscribers. One way to accomplish this is to just modify the delete procedure as follows:

ALTER procedure [dbo].[sp_MSdel_dboSomeNewTable] 
  @pkc1 varchar(150)
as 

--dont process deletes on this subscriber
return

The only worry about changing the subscriber procedures is that they will get overwritten if you run a new snapshot. Rather than try to remember to apply the revised procedure after a snapshot - which might not happen for years - the best thing to do is create a post snapshot script that will apply your change to the subscriber automatically. If you modify the procedure and cause an error it just forces the distribution agent to stop. Fix the problem and replication will resume exactly where it left off with no loss of data.

Next time we'll look at changing our article properties to use some of the other delivery options.

 

Total article views: 3996 | Views in the last 30 days: 11
 
Related Articles
FORUM

how to delete the unwanted publisher and subscriber

how to delete the unwanted publisher and subscriber

FORUM

Which Stored Procedure changed

Which Stored Procedure changed

FORUM

changes made at the subscriber should not changed after Synchronization

changes made at the subscriber should not changed after Synchronization

ARTICLE

Dynamic creation of Insert, Update, Delete Stored procedures

Automates the creation of INSERT, UPDATE & DELETE stored procedure of a table

FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones