SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


system stored procecures


system stored procecures

Author
Message
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14372 Visits: 12212
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 Angry 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 :heheSmile 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

croberts 36762
croberts 36762
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 442
I ran it. It returned 'this'.
tmcbroom
tmcbroom
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 122
I also ran it on SQL Server 2008 SP2 and it returned "this".
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3718 Visits: 4408
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'? :-)
Tee Time
Tee Time
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 Visits: 465
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?!? :-D :-P Hehe

Good question


Good point! Not for me though. :-P
tmcbroom
tmcbroom
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 122
I had predicted that it would return "this", but I always confirm anything I do not know as fact.
Tony.l
Tony.l
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 67
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.
SQL-DBA-01
SQL-DBA-01
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3689 Visits: 3293
The results will depend upon the presence of the database - test2!!

Thanks.
Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11594 Visits: 780
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





Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1515
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... Ermm
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/MCSE/MCSA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search