August 22, 2011 at 8:28 pm
Comments posted to this topic are about the item system stored procecures
August 22, 2011 at 8:29 pm
August 22, 2011 at 9:44 pm
The results could depend on the existence of database test2...
August 22, 2011 at 10:43 pm
A basic question but SQL script is poor. Though my answer was correct. But I cannot deny bits of fire from folks 😉
--Sudhir
August 22, 2011 at 10:50 pm
Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed. (from Microsoft)
then how it executes test2 sp instead of master db
August 22, 2011 at 11:21 pm
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
August 22, 2011 at 11:35 pm
From the Microsoft linked article, the master db gets searched first, so the version of the sp in there will run, correct? So either the answer to this question is wrong, or the MS documentation is wrong. Anyone sitting in front of an SQL server able to test this?
Of course anyone stupid enough to name their procs sp_ and fail to specify the db when execing deserves the uncertainties they get!
August 22, 2011 at 11:41 pm
MS documentation is wrong as i tried and it exectue test2 DB sp instead of master db sp , but micrsoft says that it should first search in master db and it would run master db sp but here its behavior is different . unable to understand what's wrong with microsft
August 22, 2011 at 11:46 pm
In SQL 2008 the script return 'that'. But the link point to an SQL 2000 document. Behavior might have changed...
Wait! Doesn't that mean, the answer 'that' is wrong, since it does not comply to the information provided by the linked document? I want my point back :hehe:
August 23, 2011 at 12:46 am
The answer is syntax error why should we assume the database test2 exist when it does not?
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
August 23, 2011 at 12:50 am
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
Lars Broberg
Elbe-Data AB
August 23, 2011 at 12:59 am
kapfundestanley (8/23/2011)
The answer is syntax error why should we assume the database test2 exist when it does not?
The fact that test2 does not exist is not a syntax error.
Best Regards,
Chris Büttner
August 23, 2011 at 1:11 am
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).
http://msdn.microsoft.com/en-us/library/ms190669.aspx
Best Regards,
Chris Büttner
August 23, 2011 at 1:14 am
Thanks! I'm to old for this 😉
Lars Broberg
Elbe-Data AB
August 23, 2011 at 1:59 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).
Spot on Chris - Thanks 🙂
The impossible can be done at once, miracles may take a little longer 🙂
Viewing 15 posts - 1 through 15 (of 72 total)
You must be logged in to reply to this topic. Login to reply