August 24, 2011 at 3:17 am
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 :hehe:) 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
August 24, 2011 at 12:48 pm
I ran it. It returned 'this'.
August 24, 2011 at 2:45 pm
I also ran it on SQL Server 2008 SP2 and it returned "this".
August 25, 2011 at 12:49 am
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'? 🙂
August 25, 2011 at 6:25 am
OzYbOi d(-_-)b (8/23/2011)
SanDroid (8/23/2011)
On top of that, didn't it say to consider the code, not run it?!? 😀 😛 :hehe:Good question
Good point! Not for me though. 😛
August 25, 2011 at 6:29 am
I had predicted that it would return "this", but I always confirm anything I do not know as fact.
August 29, 2011 at 3:09 pm
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.
August 31, 2011 at 9:05 pm
The results will depend upon the presence of the database - test2!!
Thanks.
September 1, 2011 at 9:04 am
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
September 3, 2011 at 12:57 pm
tommyh (8/22/2011)
So BOL is wrong. From the linkIt 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
September 4, 2011 at 6:05 pm
Just as a slight off-topic post, you have all noted I hope that there is a difference in behaviour between stored procedures named sp_blah and those named, say, usp_blah?
🙂
September 5, 2011 at 7:06 am
There was nothing in the question that told me the code had to be run using SQLServer. I wrote it down on a piece of paper and nothing happened. Can I have my point back?
January 12, 2012 at 6:13 am
wrong answer showing for u question....
answer is this.
Viewing 13 posts - 61 through 72 (of 72 total)
You must be logged in to reply to this topic. Login to reply