system stored procecures

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Comments posted to this topic are about the item system stored procecures

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice to be reminded of basic how things work ....

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • ChrisP-374390

    SSC-Addicted

    Points: 498

    The results could depend on the existence of database test2...

  • CoolCodeShare

    SSCrazy

    Points: 2584

    A basic question but SQL script is poor. Though my answer was correct. But I cannot deny bits of fire from folks 😉

    --Sudhir

  • Hafiz Muhammad Suleman

    SSC Enthusiast

    Points: 120

    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

  • tommyh

    SSCertifiable

    Points: 6252

    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

  • Endareth

    Newbie

    Points: 9

    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!

  • Hafiz Muhammad Suleman

    SSC Enthusiast

    Points: 120

    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

  • ma-516002

    Hall of Fame

    Points: 3299

    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:

  • kapfundestanley

    SSCertifiable

    Points: 5627

    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

  • elbedata

    SSCarpal Tunnel

    Points: 4204

    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

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • elbedata

    SSCarpal Tunnel

    Points: 4204

    Thanks! I'm to old for this 😉

    Lars Broberg
    Elbe-Data AB

  • David P Fisher

    SSC Eights!

    Points: 818

    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).

    http://msdn.microsoft.com/en-us/library/ms190669.aspx

    Spot on Chris - Thanks 🙂

    The impossible can be done at once, miracles may take a little longer 🙂

Viewing 15 posts - 1 through 15 (of 73 total)

You must be logged in to reply to this topic. Login to reply