• R. van Laake (8/5/2008)


    Hi there,

    Thanks for your input.... however being a newbie about where (or where not) to place SP's I'm not quite getting it. I understand that when you put a SP in the master database, the name should begin with sp_ , and that a name should be chosen that Microsoft will not likely use in the future.

    Question (1)

    I assume I can put any own SP in the master database Programmability|Stored Procedures... or should I put it in Programmability|Stored Procedures | System Stored Procedures ?

    Question (2)

    Will any SP in the master database use data in the user-database it is fired from? In my first test I could fire a SP named sp_mytest in the master database from a user-defined database; but I 'm not getting a resultset back (SELECT * FROM customers gives the error:Invalid object name 'customers')

    This SP does work:

    declare @dbname sysname

    SET @dbname = db_name()

    exec('SELECT * from '+@dbname+'.dbo.customers')

    ... but then I would have to rewrite all queries to a querystring.... not acceptable

    Question (3)

    Apart from the SP's name having to be unique, are there any other considerations?

    Looking forward to any input!!

    Thanks, Raymond

    One more consideration for placing stored procedures into master - and that is, you need to mark them as system stored procedures. Otherwise, they just run in the context of the master database.

    You would do this:

    Execute sp_MS_marksystemobject sp_mytest;

    And that should setup the procedure so it runs in the context of the database where it is executed.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs