Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

What is the search order for Procedures prefixed sp_? Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2002 12:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, August 18, 2014 12:08 PM
Points: 8,369, Visits: 735
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp


Post #8134
Posted Thursday, November 14, 2002 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 1:30 AM
Points: 2,898, Visits: 1,795
Interesting

I've always insisted that stored procedures were prefixed with usp (user stored procedure) so that I can spot the ones for which I should expect documentation.

As there are an ever growing number of internal stored procedures it is vital that non-internal stored procedures are immediately identifiable, if only to show that you don't have to trawl for BOL to find out what they were supposed to do.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #46493
Posted Thursday, November 14, 2002 3:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 573, Visits: 107
It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.

Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088



Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons
Post #46494
Posted Thursday, November 14, 2002 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.

Jon Reade



Jon
Post #46495
Posted Thursday, November 14, 2002 7:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61
Good article James, well investigated.
Looks like Microsoft got it right on the security front with this one too, as the s/p needs to be owned by a system user in order to exec sp_MS_marksystemobject.

Jon Reade



Jon
Post #46496
Posted Thursday, November 14, 2002 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, Visits: 96
Well, my experience is absolutely the opposite of James Travis - and is exactly what I would have expected!

Here is what I did, using Sql Server 2000 Sp2 under Windows 2000 professional Sp3:

- Opened Query Analyzer
- Typed in the following:
use master
go
create procedure sp_mysp as
select 'I am the master'
go
use miscellaneous
go
exec miscellaneous..sp_mysp
go
create procedure sp_mysp as
select 'I am NOT the master'
go
exec miscellaneous..sp__mysp

and got exactly the same as James
I am the master
I am NOT the master
- I closed Sql Server 2000 (SP2) down, then restarted it
- Then
use miscellaneous
go
exec miscellaneous..sp_mysp
STILL gives
I am NOT the master
- And
sp__mysp
ALSO gives
I am NOT the master
- And
use Maintenance
go
exec sp_mysp
gives
I am the master

So how do we explain these 2 sets of opposing results?




Post #46497
Posted Thursday, November 14, 2002 2:05 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
Did you mark the stored procedure in the master database as a system object? I don't see that you did based on your explanation. That would explain the discrepancy based on both the original forum discussion and the article.

K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #46498
Posted Thursday, November 14, 2002 4:09 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, August 18, 2014 12:08 PM
Points: 8,369, Visits: 735
I believe Brain is right. If you look back I mark the sp as a system object by running

sp_MS_marksystemobject

against it.

It is not a documented procedure but what it does is change the bitmask of the status of the object to match with system type. Problem is once done there is no real safe way to undo (can be done don't get me wrong) and as long as marked as a system type you cannot drop thru EM (sorry didn't try thru QA and wasn't really worried since is a test server I got to rebuild in the next few months anyway). Once you do that and restart the server, your results should match up.




Post #46499
Posted Thursday, November 14, 2002 4:12 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, August 18, 2014 12:08 PM
Points: 8,369, Visits: 735
quote:

It would be great if you could categorise stored procedures into folders. So that in EM you have Stored Procedures Then a System folder and a User folder and you can then add more folders under the User folder.

Who nows what we get with Yukon, I know we are getting auto complete like in visual studio, gone will be the days of trying to remember column names.

Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088



Yes, that would be a great feature. This one may be a future possiblity thou if you look at what they did with QA in SQL 2000 and the schema view window (or whatever it is called).




Post #46500
Posted Thursday, November 14, 2002 4:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, Visits: 96
OK, having run sp_ms_marksystemobject, I get the same results as James.

But the article was entitled 'What is the search order for Procedures prefixed sp_?'
In that context, running sp_ms_marksystemobject is not relevant. And I would therefore argue that there is no reason why user-written stored procedures should not be named with the 'sp_' prefix. I would also suggest that having 2 stored procedures with the same name, where one of them resides within master, is poor management.

My own convention is to prefix any sps I write which I store into master with 'sp__' (that's 2 underscores instead of 1). So I get the benefit of a universally available sp, but at the same time can easily see which sps within master are 'mine'.

In other dbs, I use sprName for sps I expect to invoke directly (usually from code within my app), and spr_ for sps that are not invoked directly by me, but instead are invoked from within the (higher level) sprName sps.

And fortunately, I operate in an environment where I have complete and sole control over all such standards and conventions <g>!




Post #46501
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse