August 23, 2011 at 8:58 am
IMO this was an easy one.... thanks!
August 23, 2011 at 9:20 am
Christian Buettner-167247 (8/23/2011)
elbedata (8/23/2011)
It returns "that" when I test on SQL Server 2008, but I thought it should return "this"...Has this changed in later SQL versions? I am pretty sure I've learned that prefixing SP:s with "sp_" was a bad idea and older MS documents clearly states this.
Lars B
Better check "Naming Stored Procedures", it perfectly explains what is happening.
(sp_one in master is not a system stored procedure, therefore it is not "preferred" over the one in the current database).
Thanks for answering that debate
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 23, 2011 at 9:21 am
Good question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 23, 2011 at 9:47 am
SanDroid (8/23/2011)
venoym (8/23/2011)
I thought it was a fine question....Especially since you don't see the instructions for installing SQL server on the question, you have to make an even larger assumption that SQL Server is even installed.
"Reasonable Assumption" tends to confuse a lot of people on this question... not sure why...
😛 Nice one. I could not have put it better myself.
Sometime the assumptions completely change the outcome depending on what they are. I don't think that is the case today.
On top of that, didn't it say to consider the code, not run it?!? 😀 😛 :hehe:
Good question
August 23, 2011 at 10:34 am
It is a good question. I answered too quickly and got it wrong. Had I thought about it a little longer I would have remembered that an application could break if Microsoft created a system stored procedure that happened to match you user stored procedure if they conflicted because you used sp_ in the naming of your procedures.
Also, based on the script we were to consider, I assumed that the database test2 already existed. If one of the choices had been something like database does not exist or procedure already exists (or something similar), then I might agree with those who complained about not having a create database in the script as well.
August 23, 2011 at 3:09 pm
I didn't read all 5 pages of comments, so maybe someone else has already pointed this out.... I know the rules of the sp_ prefix, BUT in the code block the last USE statement puts the code execution on the test2 database. From that, I assumed the engine would still be looking at test2 for the procedure before looking elsewhere.
...on the other hand, I could have gotten the right answer based on wrong assumption...
August 23, 2011 at 3:15 pm
Silly me, assuming the blatantly obvious answer couldn't possibly be correct.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 23, 2011 at 3:33 pm
ronmoses (8/23/2011)
Silly me, assuming the blatantly obvious answer couldn't possibly be correct.ron
😀 That nearly got me, too!
Very good question, especially since it highlights that the BOL don't necessarily mean what they appear to say.
August 23, 2011 at 5:50 pm
August 24, 2011 at 12:17 am
michael.kaufmann (8/23/2011)
Hafiz Muhammad Suleman (8/23/2011)
one question : how can we create a system procedure in master by ourselves ?I may be wrong, but as far as I know, you cannot create a system stored procedure--they are provided by Microsoft only.
There is an undocumented method to mark your "sp_xxx" stored procedure as a system object:
EXEC sys.sp_ms_marksystemobject 'sp_xxx'
I've found this method here: http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx
August 24, 2011 at 1:09 am
vk-kirov (8/24/2011)
michael.kaufmann (8/23/2011)
Hafiz Muhammad Suleman (8/23/2011)
one question : how can we create a system procedure in master by ourselves ?I may be wrong, but as far as I know, you cannot create a system stored procedure--they are provided by Microsoft only.
There is an undocumented method to mark your "sp_xxx" stored procedure as a system object:
EXEC sys.sp_ms_marksystemobject 'sp_xxx'I've found this method here: http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx
no use and still it executes our db sp when execute sp_one
August 24, 2011 at 1:49 am
Hafiz Muhammad Suleman (8/24/2011)
no use and still it executes our db sp when execute sp_one
Database context should be changed to 'master' before executing the sp_ms_marksystemobject procedure:
USE master
GO
EXEC sys.sp_ms_marksystemobject 'sp_one'
GO
USE test2
GO
August 24, 2011 at 1:57 am
vk-kirov (8/24/2011)
Hafiz Muhammad Suleman (8/24/2011)
no use and still it executes our db sp when execute sp_oneDatabase context should be changed to 'master' before executing the sp_ms_marksystemobject procedure:
USE masterGO
EXEC sys.sp_ms_marksystemobject 'sp_one'
GO
USE test2
GO
already did in the same way as you telling but after that if i executes
USE test2
EXEC sp_one it execute the the the sp that is in test2 db with same name not the master db sp 🙂
August 24, 2011 at 3:12 am
I like the idea behind the question, but the question itself (and the script) could have been worked out better.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2011 at 3:12 am
Hafiz Muhammad Suleman (8/24/2011)
already did in the same way as you telling but after that if i executesUSE test2
EXEC sp_one it execute the the the sp that is in test2 db with same name not the master db sp 🙂
You are right, sorry for the confusing posts about sp_ms_marksystemobject.
I tested it yesterday and somehow I got 'this' as a result. Probably I executed the 'sp_one' procedure in context of the 'master' database.
Sorry again 🙂
Viewing 15 posts - 46 through 60 (of 72 total)
You must be logged in to reply to this topic. Login to reply