Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Can you clarify? How are you creating the publication if you are not in the context of the publication database? sp_addmergepublication needs to be run from the published database as well. –  Brandon Williams Jan 25 '13 at 17:57
    
I am using EXEC sp_executesql to execute under the other database. –  Wes Crockett Jan 25 '13 at 21:31

1 Answer 1

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

EXEC ('USE [WC48_Database1]; EXEC sp_startpublication_snapshot @publication = @PublicationName;');
share|improve this answer
    
This might work... I'll give it a go. –  Wes Crockett Jan 25 '13 at 21:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.