system stored procecures

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

  • 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]

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

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

    --Sudhir

  • 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

  • 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

  • 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!

  • 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

  • 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:

  • 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

  • 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

  • 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

  • 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

  • Thanks! I'm to old for this 😉

    Lars Broberg
    Elbe-Data AB

  • 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 72 total)

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