InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 21:59:53 GMT20http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxwrong answer showing for u question.... answer is this.Thu, 12 Jan 2012 06:13:58 GMTramu.ahttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxThere 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?Mon, 05 Sep 2011 07:06:24 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxJust 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?:)Sun, 04 Sep 2011 18:05:26 GMTChrisP-374390http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]tommyh (8/22/2011)[/b][hr]So BOL is wrong. From the link[quote]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.[/quote]Because running the example it most definatly runs the SP in the current database before the master database./T[/quote]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,Sat, 03 Sep 2011 12:57:28 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxFirst 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.[code]/* Demostrates condition using a view prefixed SP_ */use mastergocreate view sp_oneasselect 'this' as Col1gouse testDBgoselect * from sp_one --notice no reference to mastergocreate view sp_oneasselect 'that' as Col1goselect * from sp_onegodrop view sp_onegodrop view sp_onego/* Demostrates condition using a view prefixed SP_ in master then added proc in Local DB */use mastergocreate view sp_oneasselect 'this' as Col1gouse testDBgoselect * from sp_one --notice no reference to mastergocreate proc sp_oneasselect 'that' as Col1goselect * from sp_one --now throws an error due to local object named sp_onegodrop proc sp_onegodrop view sp_onego[/code]Thu, 01 Sep 2011 09:04:00 GMTAntares686http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxThe results will depend upon the presence of the database - test2!!Wed, 31 Aug 2011 21:05:23 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxi 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.Mon, 29 Aug 2011 15:09:45 GMTTony.lhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI had predicted that it would return "this", but I always confirm anything I do not know as fact.Thu, 25 Aug 2011 06:29:46 GMTtmcbroomhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]OzYbOi d(-_-)b (8/23/2011)[/b][hr][quote][b]SanDroid (8/23/2011)[/b][hr]On top of that, didn't it say to consider the code, not run it?!? :-D :-P :hehe:Good question[/quote]Good point! Not for me though. :-PThu, 25 Aug 2011 06:25:06 GMTTee Timehttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]croberts 36762 (8/24/2011)[/b][hr]I ran it. It returned 'this'.[/quote][quote][b]tmcbroom (8/24/2011)[/b][hr]I also ran it on SQL Server 2008 SP2 and it returned "this".[/quote]Did it also return 'Could not locate entry in sysdatabases for database test2'? :-)Thu, 25 Aug 2011 00:49:16 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI also ran it on SQL Server 2008 SP2 and it returned "this".Wed, 24 Aug 2011 14:45:41 GMTtmcbroomhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI ran it. It returned 'this'.Wed, 24 Aug 2011 12:48:52 GMTcroberts 36762http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxCould 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."Wed, 24 Aug 2011 03:17:07 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]Hafiz Muhammad Suleman (8/24/2011)[/b][hr]already did in the same way as you telling but after that if i executesUSE test2EXEC sp_one it execute the the the sp that is in test2 db with same name not the master db sp :)[/quote]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 :-)Wed, 24 Aug 2011 03:12:33 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI like the idea behind the question, but the question itself (and the script) could have been worked out better.Wed, 24 Aug 2011 03:12:25 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]vk-kirov (8/24/2011)[/b][hr][quote][b]Hafiz Muhammad Suleman (8/24/2011)[/b][hr]no use and still it executes our db sp when execute sp_one[/quote]Database context should be changed to 'master' before executing the sp_ms_marksystemobject procedure:[code="sql"]USE masterGOEXEC sys.sp_ms_marksystemobject 'sp_one'GOUSE test2GO[/code][/quote]already did in the same way as you telling but after that if i executesUSE test2EXEC sp_one it execute the the the sp that is in test2 db with same name not the master db sp :)Wed, 24 Aug 2011 01:57:58 GMTHafiz Muhammad Sulemanhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]Hafiz Muhammad Suleman (8/24/2011)[/b][hr]no use and still it executes our db sp when execute sp_one[/quote]Database context should be changed to 'master' before executing the sp_ms_marksystemobject procedure:[code="sql"]USE masterGOEXEC sys.sp_ms_marksystemobject 'sp_one'GOUSE test2GO[/code]Wed, 24 Aug 2011 01:49:28 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]vk-kirov (8/24/2011)[/b][hr][quote][b]michael.kaufmann (8/23/2011)[/b][hr][quote][b]Hafiz Muhammad Suleman (8/23/2011)[/b][hr]one question : how can we create a system procedure in master by ourselves ?[/quote]I may be wrong, but as far as I know, you cannot create a system stored procedure--they are provided by Microsoft only.[/quote]There is an undocumented method to mark your "sp_xxx" stored procedure as a system object:[code="sql"]EXEC sys.sp_ms_marksystemobject 'sp_xxx'[/code]I've found this method here: [url]http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx[/url][/quote]no use and still it executes our db sp when execute sp_oneWed, 24 Aug 2011 01:09:40 GMTHafiz Muhammad Sulemanhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]michael.kaufmann (8/23/2011)[/b][hr][quote][b]Hafiz Muhammad Suleman (8/23/2011)[/b][hr]one question : how can we create a system procedure in master by ourselves ?[/quote]I may be wrong, but as far as I know, you cannot create a system stored procedure--they are provided by Microsoft only.[/quote]There is an undocumented method to mark your "sp_xxx" stored procedure as a system object:[code="sql"]EXEC sys.sp_ms_marksystemobject 'sp_xxx'[/code]I've found this method here: [url]http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx[/url]Wed, 24 Aug 2011 00:17:11 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxgood oneTue, 23 Aug 2011 17:50:29 GMTBuntyBoyhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]ronmoses (8/23/2011)[/b][hr]Silly me, assuming the blatantly obvious answer couldn't possibly be correct.ron[/quote]:-D That nearly got me, too! Very good question, especially since it highlights that the BOL don't necessarily mean what they appear to say.Tue, 23 Aug 2011 15:33:43 GMTOlga Bhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxSilly me, assuming the blatantly obvious answer couldn't possibly be correct.ronTue, 23 Aug 2011 15:15:30 GMTronmoseshttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI 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...Tue, 23 Aug 2011 15:09:08 GMTUripedes Pantshttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxIt 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.Tue, 23 Aug 2011 10:34:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]SanDroid (8/23/2011)[/b][hr][quote][b]venoym (8/23/2011)[/b][hr]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...[/quote]:-P 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.[/quote]On top of that, didn't it say to consider the code, not run it?!? :-D :-P :hehe:Good questionTue, 23 Aug 2011 09:47:23 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxGood questionTue, 23 Aug 2011 09:21:03 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]Christian Buettner-167247 (8/23/2011)[/b][hr][quote][b]elbedata (8/23/2011)[/b][hr]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[/quote]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).[url]http://msdn.microsoft.com/en-us/library/ms190669.aspx[/url][/quote]Thanks for answering that debateTue, 23 Aug 2011 09:20:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxIMO this was an easy one.... thanks!Tue, 23 Aug 2011 08:58:34 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]venoym (8/23/2011)[/b][hr]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...[/quote]:-P 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.Tue, 23 Aug 2011 08:51:56 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxThanks for the question.Tue, 23 Aug 2011 08:43:26 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxThe question would have been better had it not assumed test2 database was already there.Tue, 23 Aug 2011 08:19:58 GMTkapfundestanleyhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxGreat 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. :cool:Tue, 23 Aug 2011 07:43:48 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]skrilla99 (8/23/2011)[/b][hr]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[/quote]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:Tue, 23 Aug 2011 07:42:35 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]David Burrows (8/23/2011)[/b][hr]I found this interestingdrop procedure sp_onegodrop procedure sp_onegowill drop the procedure in test2 and master[/quote]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.Tue, 23 Aug 2011 07:37:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxLook 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.Tue, 23 Aug 2011 07:36:32 GMTDavid Smerchekhttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxHave 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 1Database 'test2' does not exist. Make sure that the name is entered correctly.Msg 2714, Level 16, State 3, Procedure sp_one, Line 3There is already an object named 'sp_one' in the database.And the returned value will be 'this'.Tue, 23 Aug 2011 07:34:05 GMTRay Lauberthttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxWell, 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 BTue, 23 Aug 2011 07:32:25 GMTskrilla99http://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]bitbucket-25253 (8/23/2011)[/b][hr][quote][b]venoym (8/23/2011)[/b][hr]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...[/quote]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.[/quote]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. :-DTue, 23 Aug 2011 07:07:34 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspxI found this interestinguse mastergocreate procedure sp_oneasselect 'this'gouse test2gocreate procedure sp_oneasselect 'that'godrop procedure sp_onegodrop procedure sp_onegowill drop the procedure in test2 and master :Wow:Tue, 23 Aug 2011 07:04:14 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1163694-1375-1.aspx[quote][b]bitbucket-25253 (8/23/2011)[/b][hr][quote][b]venoym (8/23/2011)[/b][hr]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...[/quote]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.[/quote]+1And 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./TTue, 23 Aug 2011 07:02:45 GMTtommyh