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.