August 23, 2011 at 6:14 am
rfr.ferrari (8/23/2011)
good question!!!
Sorry, I have to respectfully disagree. Two assumptions are required but not specified. One, that anyone can create a stored procedure in the master db without appropriate permission. I discovered this since I am a consultant developer and do not have access to do so, so I could not test the results in my environment. And, two, it assumes presence of the test2 database. Further, the link to the documentation states the opposite of the "correct" answer.
I know people can be nitpicky on these questions, but I don't think that's the case here.
August 23, 2011 at 6:50 am
I thought it was a fine question.
I'm surprised by the number of people who are complaining about the existence of a Test2 db or not. I would have thought a reasonable assumption would be that the master database and the Test2 db existed and with appropriate permissions to create a stored procedure. 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...
August 23, 2011 at 6:55 am
venoym (8/23/2011)
I thought it was a fine question.I'm surprised by the number of people who are complaining about the existence of a Test2 db or not. I would have thought a reasonable assumption would be that the master database and the Test2 db existed and with appropriate permissions to create a stored procedure. 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...
My guess is that their ego is injured when they answer incorrectly. Thus they are forced to complain to cover up their own lack of knowledge.
August 23, 2011 at 7:02 am
bitbucket-25253 (8/23/2011)
venoym (8/23/2011)
I thought it was a fine question.I'm surprised by the number of people who are complaining about the existence of a Test2 db or not. I would have thought a reasonable assumption would be that the master database and the Test2 db existed and with appropriate permissions to create a stored procedure. 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...
My guess is that their ego is injured when they answer incorrectly. Thus they are forced to complain to cover up their own lack of knowledge.
+1
And i got it wrong. I thought it was ALL SPs starting with sp_ that would get effected. Not that it matters to me. Since its alot better to keep the rule... "DONT USE SP_" without any extra conditions.
/T
August 23, 2011 at 7:04 am
I found this interesting
use master
go
create procedure sp_one
as
select 'this'
go
use test2
go
create procedure sp_one
as
select 'that'
go
drop procedure sp_one
go
drop procedure sp_one
go
will drop the procedure in test2 and master :Wow:
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2011 at 7:07 am
bitbucket-25253 (8/23/2011)
venoym (8/23/2011)
I thought it was a fine question.I'm surprised by the number of people who are complaining about the existence of a Test2 db or not. I would have thought a reasonable assumption would be that the master database and the Test2 db existed and with appropriate permissions to create a stored procedure. 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...
My guess is that their ego is injured when they answer incorrectly. Thus they are forced to complain to cover up their own lack of knowledge.
Oh, please. My ego is fine. I just pointed out that there were a number of flaws in the question, so I don't think it should be considered "good." Just stating my opinion. 🙂
Regarding "a reasonable assumption." All we got was "consider the following." So when your presented an issue, do you just assume something, or do you do some background investigation? Check any policies, documentation, question someone, etc. I rarely ***-u-me anything. 😀
August 23, 2011 at 7:32 am
Well, I got it wrong. And, I can appreciate some of the complaints.
But the question is a good one if it teaches, and this one reminds us that 'sp_' is not all that is required to make a system stored procedure.
Thanks for the question.
Thanks also to Chris Büttner for the link.
-Dan B
August 23, 2011 at 7:34 am
Have to disagree. Since the script did not include the create database test2, the second part of the script errors out.
The result will be :
Msg 911, Level 16, State 1, Line 1
Database 'test2' does not exist. Make sure that the name is entered correctly.
Msg 2714, Level 16, State 3, Procedure sp_one, Line 3
There is already an object named 'sp_one' in the database.
And the returned value will be 'this'.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
August 23, 2011 at 7:36 am
Look at the reference you sent, in it it states:
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.
August 23, 2011 at 7:37 am
David Burrows (8/23/2011)
I found this interestingdrop procedure sp_one
go
drop procedure sp_one
go
will drop the procedure in test2 and master
You're right, that is interesting. It makes sense, but I had never tried that before.
On a slight tangent, I cringe slightly whenever I see unqualified references or 'hungarian' prefixes. Using unqualified sp_ references, in particular...why?!
That said, a very reasonable question today, thanks.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 23, 2011 at 7:42 am
skrilla99 (8/23/2011)
Well, I got it wrong. And, I can appreciate some of the complaints.But the question is a good one if it teaches, and this one reminds us that 'sp_' is not all that is required to make a system stored procedure.
Thanks for the question.
Thanks also to Chris Büttner for the link.
-Dan B
I agree on this point. And since that appears to be the purpose of the question, yes it's a well-intentioned question. I've always used the practice of naming user stored procedures starting with 'UP...'. Most of the time I'm in a position of being a DBA, so I can enforce the rule. In my latest gig, as I said, I'm not in that position. And, when I mentioned the best practice of not naming procedures starting with "sp" to the manager I'm working with, I got the glassy-eyed crickets chirping look. :hehe:
August 23, 2011 at 7:43 am
Great question! Easy to understand and no tricks.
Thank you.
I have to admit I looked at it five minutes trying to decide if it was a syntax error since ther was no script to make database test2.
😎
August 23, 2011 at 8:19 am
The question would have been better had it not assumed test2 database was already there.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
August 23, 2011 at 8:43 am
Thanks for the question.
August 23, 2011 at 8:51 am
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.
Viewing 15 posts - 31 through 45 (of 72 total)
You must be logged in to reply to this topic. Login to reply