after making a non system stored procedure it appears in the system stored procedure folder

Question

Post reply

after making a non system stored procedure it appears in the system stored procedure folder

bryan van ritter

SSCrazy

Points: 2270
More actions
September 23, 2010 at 4:20 pm

#142851

Hi,
i have this strange thing going on. when i create a procedure in the master database it appears in the system stored procedures folder. can anyone explain me how this comes. maybe something to do with the user mapping?? platform: sql server 2005 64-bits enterprise / os : windows 2003 64-bits enterprise
kind regards,
Bryan

Viewing 14 posts - 1 through 13 (of 13 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

What is the name of that SP?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

bryan van ritter SSCrazy Points: 2270 More actions · Answer 2

the name is random. not a specific name op this SP. let's call it test.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

MANU-J.

SSC-Dedicated

Points: 31126

October 10, 2010 at 2:25 am

#1234669

Is your problem resolved?

MJ

bryan van ritter SSCrazy Points: 2270 More actions · Answer 4

My problem is not solved yet. i hope someone can help me on this one. maybe it's a user issue??

so normally when you create a stored procedure in the master database it will not end up in the system stored procedures folder, but in my case it is.

kind regards,

Bryan

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

You're not prefacing the name of your procedure with sp_, are you? That's reserved for system stored procedures.

bryan van ritter SSCrazy Points: 2270 More actions · Answer 6

bryan van ritter

SSCrazy

Points: 2270

October 12, 2010 at 4:22 am

#1235390

No not using sp_.....

Leo.Miller SSChampion Points: 13051 More actions · Answer 7

I've tested this on my SQL 2005 server and can't get it to save an object in System Stored procs.

Are you sure they are actually defined as system stored procs? In SQL 2005 the view is a bit misleading as there isn't a seperate User Stored Procs folder. All the user stored procs appear under "Stored Procedures", and there is a seperate "System Stored Procedures" folder on the same level, but it's at the top of the list, so it does look a bit like everythin is in that folder.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 8

what is the schema of your SP its not sys. right.

if you give the name with sys. spname it creates in system store proc.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Leo.Miller SSChampion Points: 13051 More actions · Answer 9

pavan_srirangam (10/12/2010)
what is the schema of your SP its not sys. right.
if you give the name with sys. spname it creates in system store proc.

I tested this and it wouldn't let me create a stored proc with the sys. prefix.

Also if you look in System Stored Procs you will see stored procs owned by dbo, so this isn't definitive.

I've looked in sys.sysobjects and can't see any way of identifying a stored proc as belonging to the system stored procs. I'm not sure how SQL determines this, does anyone else?

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Oliiii SSCertifiable Points: 5328 More actions · Answer 10

In sys.all_objects there is a field is_ms_shipped.

The management studio (2005 and 2008) put all the procedures (and extended procedures) in the system folder if this field is set to 1.

The only way to set this field to 1 is to use sp_ms_marksystemobject (use at your own risk).

Martin Catherall Right there with Babe Points: 746 More actions · Answer 11

Hi,

I'm not sure if this post will explain anything that may help you.

http://www.sqlservercentral.com/blogs/martin_catherall/archive/2011/04/06/this-appears-to-be-a-system-procedure_2E002E00_but-it-isn_1920_t_2100_.aspx

cheers

martin.

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 12

I am having the same issue as Bryan with this original post. Can anyone explain?

I just realized that any procedure I create is being stored in the System Stored Procedures folder. My platform is also 2005 64-bit Enterprise SP4 (my other servers are storing it correctly under the master stored procedures folder - they are not 64-bit Enterprise - using the exact same script)

I am creating a stored procedure like this:

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[XYZ]

....

Now that they are there I dont have the option to delete them via SSMS either.

I confirmed they are returned along with the system procedures with the query:

SELECT * FROM sys.objects where is_ms_shipped = 1

chumphrey 12211 Ten Centuries Points: 1223 More actions · Answer 13

After posting I found the fix in another Forum titled SYSTEM STORED PROCEDURE QUESTION. Many Thanks to Jason! My server also had the sp_configure 'allow updates' incorrectly set to 1. When I followed his suggestion and created a stored procedure it now correctly goes in the correct folder. I, too, have no idea how this setting was set to 1.

His fix was to run the following:

sp_configure 'allow updates',0

reconfigure