Difference between Stored Procs and System Stored Procs

  • I run a user sp script in master db and sometimes it goes under Stored Procedures list and other under System Stored Procs. I have 2 instances, one is created under system sps and works just fine, I dont want to fix what's not broken, on the other instance re-created several times now, shows up under Stored Procs but does not work, and it works like that in dev. Any idea why?

    ¤ §unshine ¤

  • It's not considered to be a good practice to place procedures in the system databases (except for model). The caveats in the article are true, but they don't emphasize the possible problems quite enough. I would only do this when I absolutely had to.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • This sp is named usp and somehow got created under system sps in one instance and under the regular sps in the other. Same script.

    ¤ §unshine ¤

  • Are both servers 2k5?

    I dont know exactly, but I have seen the same situation, 2k server was creating as system SP while 2k5 was taking same code as user SP. Although there was no difference in functionality.

    Creating SPs in master is not advisable, but some SPs are common throughout enterprise and used by secured applications, such SPs can be created under master. I think sunshine has the same case here.

  • Yes, both are 2k5, 64bit, and clustered.

    ¤ §unshine ¤

  • Ok. Use this

    EXEC sys.sp_MS_marksystemobject your_stored_proc_name

    and you are done. This SP is now a system SP. Same is true for any object (table, function ...)

  • I have found that the behaviour changes dependent on the running value of the 'allow updates' configuration option.

    Run the following (on a test server):

    USE [master]

    GO

    EXEC sp_configure 'allow updates', 1

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    CREATE PROCEDURE dbo.sp_Test

    AS

    SELECT 1

    GO

    If you have a look at the master database in SSMS you should see that the 'sp_Test' procedure has been created under the 'System Stored Procedures' node.

    Run the following:

    DROP PROCEDURE dbo.sp_Test

    GO

    EXEC sp_configure 'allow updates', 0

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    CREATE PROCEDURE dbo.sp_Test

    AS

    SELECT 1

    GO

    Again have a look at the master database in SSMS and you should see that the 'sp_Test' procedure has been created under the 'Stored Procedures' node.

    Chris

  • I'll try that thank you.

    ¤ §unshine ¤

  • Chris its not required sp_MS_marksystemobject will do the magic without running allow updates, believe me i have tested it. 🙂

  • Boolean_z (4/3/2009)


    Chris its not required sp_MS_marksystemobject will do the magic without running allow updates, believe me i have tested it. 🙂

    Hi, thanks for the info. I was just trying to demonstrate that a difference in configuration between instances of SQL Server 2005 can cause the seemingly random behaviour to occur. 🙂

    Cheers

    Chris

Viewing 11 posts - 1 through 10 (of 10 total)

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