Sql-server – sp_startpublication_snapshot Parameter(s)

replicationsql serverstored-procedurest-sql

I am creating a stored procedure that:

  1. Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
  2. (If specified in the SP's parameter) Creates three merge replication publications
  3. (What I need help doing) Generating the snapshots for the three publications using sp_startpublication_snapshot

Here is my new brick wall… On this DB server, I have a 'shell' db that they will be running the SP from, that has a history table so I can keep track of who created/deleted databases using my SP's… The only parameter for sp_startpublication_snapshot is @publication… I can give it the publication name, but since I am not running it from the publishing database, how do I specify the publishing database?

i.e.: the publication shows up as:

[WC48_Database1]: upb_Inspection_PrimaryArticles

but I am running the script from the database [WC_QACatalog]

Any ideas about how to accomplish this?

Thank you,
Wes

Best Answer

Inside of your stored procedure use EXECUTE with a character string. Something like this:

EXEC ('USE [WC48_Database1]; EXEC sp_startpublication_snapshot @publication = @PublicationName;');
Related Question