Post reply

same sp in master and user database

  • August 31, 2015 at 4:44 pm

    #398970

    Hi All,

    If i create same sp on master and myDB (sp_XYZ with dbo schema) and run exec sp_XYZ which sp_XYZ will execute ,the on master or myDB?

    And second question:if i create sp_XYZ in master database only and run exec sp_XYZ from myDB would it execute?

    thanks

  • August 31, 2015 at 5:05 pm

    #1823516

    Barcelona10 (8/31/2015)

    Hi All,

    If i create same sp on master and myDB (sp_XYZ with dbo schema) and run exec sp_XYZ which sp_XYZ will execute ,the on master or myDB?

    And second question:if i create sp_XYZ in master database only and run exec sp_XYZ from myDB would it execute?

    thanks

    The stored proc in master will run in both cases.

    Test it out and see.

  • August 31, 2015 at 7:16 pm

    #1823531

    Here is my test results:

    if i run from MyDB it executes sp_XYZ that is in MyDB

    if i run from master it executes sp_XYZ that is in master

    if i run from other than MyDB or master ,error:

    "Msg 2812, Level 16, State 62, Line 5

    Could not find stored procedure 'dbo.sp_XYZ'

  • September 1, 2015 at 1:30 am

    #1823550

    The sp that is local to the db will run first. If you want to run the sp in master, when you are in the context of myDB, you will need to mark the sp in master and a system sp, in order for it to be available globally.

    If I understand what you are trying to test, read this: https://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/

  • September 1, 2015 at 10:57 am

    #1823705

    Thank You

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply