Stored Procedures getting created as system stored procedures.

Stored Procedures getting created as system stored procedures.

Post by SmFzb24gU2 » Thu, 08 Nov 2007 07:18:02


Using SQL 2005, SP2. All of a sudden, whenever I create any stored
procedures in the master database, they get created as system stored
procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?


Thanks,

Jason
 
 
 

Stored Procedures getting created as system stored procedures.

Post by Tibor Kara » Thu, 08 Nov 2007 07:22:37

See if below applies:

http://www.yqcomputer.com/

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/

 
 
 

Stored Procedures getting created as system stored procedures.

Post by SmFzb24gU2 » Thu, 08 Nov 2007 07:30:16

I saw that one two...but that applies to SQL 2000, not SQL 2005. That
sp_MS_upd_sysobj_category procedure it mentions apparently doesn't exist in
2K5.
 
 
 

Stored Procedures getting created as system stored procedures.

Post by Uri Diman » Thu, 08 Nov 2007 15:06:37

Jason
I cannot reproduce the problem . I see it under stored procedures folder and
not uder system stored procedueres one

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
 
 
 

Stored Procedures getting created as system stored procedures.

Post by SmFzb24gU2 » Thu, 08 Nov 2007 21:35:01

I can only reproduce it on one of my servers, can't reproduce on others.
 
 
 

Stored Procedures getting created as system stored procedures.

Post by SmFzb24gU2 » Fri, 09 Nov 2007 03:04:01

OK...I've decided to look at this backwards to see if it helps.


Now I'm looking for an article/KB/post that tells you how to create a system
stored procedure. Perhaps an explanation of how to make a stored procedure a
system stored procedure will also tell you how to undo that.



I know in SQL2000 you could mark an SP as system after it was created, but
from what I'm seeing you can't do that in 2005 (or at least it's harder to
find out how to). Does anyone know of anywhere I can find that information?



Thanks,

Jason