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 «««45678»»

system stored procecures Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2011 3:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:16 PM
Points: 8,830, Visits: 9,387
Could it be that people are trying to use sys.sp_ms_marksystemobject in a later version of SQL Server than the one it worked in? That would account for its not working quite nicely.

Sometimes I think it's a great pity that SQL Server has abandoned the idea of building a relational database system based on Ted Codd's model* - if it hadn't, we would never have needed an obscure system stored procedure to mark an SP as "system" (which is a very useful concept); on the other hand, I've seen what some developers can achieve by writing directly to system tables (system rendered irrevocably fubar - reinstall from clean media and recover data from backups) so even if we had the relational catalog as described by Codd we would have to make very sure that most people had no update access to it (not even by obscure SPs ) and couldn't create system stored procedures.

* in particular, conforming to Codd's catalog rule, which he expressed in various ways, including the following (this paragraph is taken from his RM2 book)
"An important property of the relational model is that both the database and its description are perceived by users as a collection of relations. Thus, with very few exceptions, the same relational language that is used to interrogate and modify the database can be used to interrogate and modify the database description. No new training is needed."


Tom
Post #1164514
Posted Wednesday, August 24, 2011 12:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 30, 2012 12:36 PM
Points: 404, Visits: 442
I ran it. It returned 'this'.
Post #1164923
Posted Wednesday, August 24, 2011 2:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:26 AM
Points: 24, Visits: 80
I also ran it on SQL Server 2008 SP2 and it returned "this".
Post #1164989
Posted Thursday, August 25, 2011 12:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
croberts 36762 (8/24/2011)
I ran it. It returned 'this'.

tmcbroom (8/24/2011)
I also ran it on SQL Server 2008 SP2 and it returned "this".

Did it also return 'Could not locate entry in sysdatabases for database test2'?
Post #1165136
Posted Thursday, August 25, 2011 6:25 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, July 24, 2014 1:50 PM
Points: 532, Visits: 448
OzYbOi d(-_-)b (8/23/2011)
[quote]SanDroid (8/23/2011)

On top of that, didn't it say to consider the code, not run it?!?

Good question


Good point! Not for me though.
Post #1165278
Posted Thursday, August 25, 2011 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 8:26 AM
Points: 24, Visits: 80
I had predicted that it would return "this", but I always confirm anything I do not know as fact.
Post #1165281
Posted Monday, August 29, 2011 3:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 2:33 PM
Points: 68, Visits: 60
i agree it will be a syntax error as test2 does not exist.
if test2 does exist then the "that" answer is what i would expect as a user function could "override" the system one of the same name.
Post #1167214
Posted Wednesday, August 31, 2011 9:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 857, Visits: 1,381
The results will depend upon the presence of the database - test2!!

Thanks.
Post #1168465
Posted Thursday, September 1, 2011 9:04 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 12:49 PM
Points: 8,369, Visits: 739
First off full disclosure, I got this wrong as well as I had just forgotten since I don't prefix with sp_

OK, I think a bit of clarification on the SQL BOL thing is needed here. There should be several articles on this site even about it. If you use SP_ to prefix your procedure the issue was that it does in fact search for it in Master first (the search mechanism actually looks for any object named SP_ in master first, table, view, function, whatever is prefixed SP_ see example for more fun to demonstrate). This was to ensure procedures in Master were available to all databases without having to set database to master or even using the 3 part name. However, it does not execute until it has also searched the database you are currently in. If there is an object of the same name in the local database and you are not in master the code of the local procedure is executed. You however cannot create a local database version of a MS System Stored Procedure and override the behavior for that database. The procedure mentioned sys.sp_ms_marksystemobject to mark the object as a system stored proc can flag it as such but there is something else I forget right now in the flags that makes it the choice over anything else, may even be directly in the database engine but again I forget.

The real downside of using SP_ is that the process to find the object for use makes the extra trip across Master which means you take a hit on performance for the extra work, especially if used very often. Also, because SP_ is global from master in the search precedence it will as I recall cause a cache miss every time on the procedure in question, but I don't have an example to share to show that right off. So the big key is the hit to performance you will take by using SP_ not that it changes behavior.

/* Demostrates condition using a view prefixed SP_ */
use master
go
create view sp_one
as
select 'this' as Col1
go
use testDB
go
select * from sp_one --notice no reference to master
go
create view sp_one
as
select 'that' as Col1
go
select * from sp_one
go
drop view sp_one
go
drop view sp_one
go

/* Demostrates condition using a view prefixed SP_ in master then added proc in Local DB */
use master
go
create view sp_one
as
select 'this' as Col1
go
use testDB
go
select * from sp_one --notice no reference to master
go
create proc sp_one
as
select 'that' as Col1
go
select * from sp_one --now throws an error due to local object named sp_one
go
drop proc sp_one
go
drop view sp_one
go




Post #1168740
Posted Saturday, September 3, 2011 12:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
tommyh (8/22/2011)
So BOL is wrong. From the link

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

The stored procedure in the master database.

The stored procedure based on any qualifiers provided (database name or owner).

The stored procedure using dbo as the owner, if one is not specified.


Because running the example it most definatly runs the SP in the current database before the master database.

/T


I was tricked by BOL as well. Have they changed something and forgot to update documentation?

[EDIT] I wasn't paying too much attention...
That's not a system store procedure so that was not the case.
Thank you for the question.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1169643
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse