More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
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.
(Continued on next question...)
