November 5, 2002 at 11:00 am
When transaction replication is set up by allowing the SQL server to post the initial snapshot, SQL will also create the neccessary stored procedures at the subscriber. (These procedures are named: sp_MSdel_... sp_MSupd_... sp_MSins_...)
It will create 3 per table, one for each of action (INSERT, UPDATE, DELETE).
If instead of letting the server apply the snapshot, you use a database dump and restore it (by telling replication that the data already exists at the subscriber), the SQL server will not create these stored procedures.
Is there a way/tool that will create these procs for a given table when SQL didn't do the snapshot in the beginning?
-Dan
-Dan
November 5, 2002 at 2:47 pm
The following is from MS KB article Q299903:
SQL Server 2000 provides the following stored procedures to assist in creating these stored procedures:
sp_scriptdelproc
sp_scriptinsproc
sp_scriptmappedupdproc
sp_scriptupdproc
sp_scriptxdelproc
sp_scriptxupdproc
Sometimes it is difficult to know which of these stored procedures to use. Even when it is obvious which stored procedures to use it can be a tedious and time consuming process to create the insert, update and delete stored procedures at the subscriber, especially when there are numerous tables in the publication.
Service Pack 1 for SQL Server 2000 includes a stored procedure that generates a script to create the script insert, update and delete stored procedures for every table in a transactional publication. The sp_scriptpublicationcustomprocs stored procedure is created in the master database when Service Pack 1 is applied to a SQL Server 2000 installation.
------------
I haven't personally used these procedures, but I have created some from scratch. If you have replication set up somewhere on just one table, you can get the format that is used and create yours from there. They are fairly straightforward, you just need one for insert,update, delete.
Good luck.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
November 5, 2002 at 3:53 pm
Thanks, I'll give it a shot!
-Dan
-Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply