March 31, 2009 at 2:10 pm
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 ¤
April 1, 2009 at 2:45 am
April 1, 2009 at 5:44 am
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
April 1, 2009 at 8:29 am
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 ¤
April 2, 2009 at 2:00 am
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.
April 2, 2009 at 7:43 am
Yes, both are 2k5, 64bit, and clustered.
¤ §unshine ¤
April 2, 2009 at 9:06 am
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 ...)
April 3, 2009 at 5:33 am
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
April 3, 2009 at 11:00 am
I'll try that thank you.
¤ §unshine ¤
April 3, 2009 at 12:07 pm
Chris its not required sp_MS_marksystemobject will do the magic without running allow updates, believe me i have tested it. 🙂
April 3, 2009 at 1:05 pm
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