(Continued from previous part...)

How to subscribe with ‘NOSYNC’ to a transactional publication?

To subscribe with ‘no synchronization’, while pushing or pulling the subscription, you can choose ‘No, the Subscriber already has schema and data’ in the ‘Initialize Subscription’ screen of the subscription wizard. This will assume that the subscriber already has the tables with data and that the stored procedures required by replication are already created on the subscriber.

So, it’s the user’s responsibility to create the tables with data and create the stored procedures, before the distribution agent runs. If you haven’t created these stored procedures, distribution agent will fail with the any of the following errors based on the type of operation the distribution task is trying to apply on the subscriber:

Error 2812: Cannot find stored procedure sp_MSins_Table1
Or
Error 2812: Cannot find stored procedure sp_MSupd_Table1
Or
Error 2812: Cannot find stored procedure sp_MSdel_Table1

In the above messages, Table1 is the name of the table that you are trying to replicate.

For every table that is replicated, the user has to manually create 3 stored procedures on the subscriber, one for INSERT, one for UPDATE and one for DELETE. The stored procedure code can be easily generated using the following system stored procedures which can be found in the master database:

sp_scriptinsproc
sp_scriptdelproc
sp_scriptmappedupdproc

All these procedures require a single parameter @artid, which is the ID of the article in the publication. This article id can be obtained by querying syspublications and sysarticles tables present in the publishing database.

The following query will help you find out the article ids for a publication.

select artid
from sysarticles sa, syspublications sp
where
sp.name = ‘Your_Publication_Name’ and
sp.pubid = sa.pubid

Eg: sp_scriptinsproc 1
The above command will output the stored procedure script. You need to run that script against the subscribing database.

How to let merge replication use your own rowguid column?

When you create a merge publication, SQL Server adds a rowguid column to all the underlying tables for uniquely identifying a row across multiple copies of the same table.

If you want to add that column by your own and let SQL Server use it in merge replication, your rowguid column should have the following properties:

- Should be of Uniqueidentifier datatype
- Should have the ROWGUIDCOL property set
- Should not allow NULLs
- Should have a default of newid()

Here is an example:

create table table1
(
i uniqueidentifier rowguidcol not null default newid(),
j int
)

(Continued on next part...)