Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177

    Question Unanswered: Where am I issues

    Im having where am I issues with a stored procedure Im attempting to write and place in master (so thats available for all dbs).

    Starting with a simple example:

    create procedure sp_fred as
    print convert(nvarchar(3),db_id()) + ' ' + db_name()
    go

    If I connect to a database (tpch) and execute the procedure:
    exec sp_fred
    I get the expected results (9 tpch).

    If I connect to a database (tpch) and execute the procedure:
    exec master..sp_fred
    I get an unexpected results (1 master).

    If I call the stored procedure something different (xp_fred) I (obviously) have to provide the complete name:
    exec master..xp_fred
    And I get the unexpected results (1 master).

    Can someone give me a short explanation or point me to some docs which explain whats going on?

    Thanks.
    Fred Prose

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey look at that...

    Curmudgeon
    I guess your not a resident....

    Anyway..isn't it because of the context your executing it in? (ie master..)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Ok, I feel the shift of a paradigm. Do I interpret from your response that when I connect to one database (ie. tpch) and execute a stored procedure from another, even if it's master using master..xxxxxx that I'm actually changing my database context to a different database?

    I'm new to MS SQL and so I guess my next question would be how does one go about writing stored procedures that will be common to multiple databases and not place those stored procedures in every database?

    I'm potentially converting an environment that has multiple (ie 40 to 50) databases (one per client) on a server with identical structures but data issolation requirements?
    Fred Prose

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure that that's agood idea...

    But run this...

    Code:
    USE master
    GO
    
    create procedure sp_fred as
    print convert(nvarchar(3),db_id()) + ' ' + db_name()
    go
    
    EXEC sp_fred
    GO
    
    USE Northwind
    GO
    EXEC sp_fred
    GO
    
    USE pubs
    GO
    EXEC sp_fred
    GO
    
    USE tempdb
    GO
    EXEC sp_fred
    GO
    
    USE master
    GO
    DROP PROC sp_fred
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Executed as you had written it the results are:

    1 master
    11 Northwind
    10 pubs
    2 tempdb

    But change the execution to:
    USE Northwind
    GO
    EXEC master..sp_fred
    GO

    USE pubs
    GO
    EXEC master..sp_fred
    GO

    USE tempdb
    GO
    EXEC master..sp_fred
    GO

    And the results are:
    1 master
    1 master
    1 master
    1 master
    Fred Prose

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did the light come one yet?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Yes - but I'm not sure I like the view with the lights on. We obviously have a conversion issue.

    Thanks.
    Fred Prose

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't see the problem...just don't qualify the sprocs?

    But I still think it's a bad idea to have n databases all sharinf sprocs compiled in master...

    maybe that's just me...but I've never seen anything like that before, or have heard of anyone doing it either....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "sp_" is sort of reserved for System Stored Procedures. Whenever you call a stored procedure sp_blahblahblah, SQL Server quickly checks to see if that procedure is in the master database. If not, then it checks the local database. When you changed your procedure name to xp_, you fell out of that standard, and SQL Server looked for it only in the local database. Does this help?

    Oh, and remember kids, do not make your naming standard for procedures sp_somename.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    "sp_" is sort of reserved for System Stored Procedures. Whenever you call a stored procedure sp_blahblahblah, SQL Server quickly checks to see if that procedure is in the master database. If not, then it checks the local database. When you changed your procedure name to xp_, you fell out of that standard, and SQL Server looked for it only in the local database. Does this help?

    Oh, and remember kids, do not make your naming standard for procedures sp_somename.

    It's not even 5:00 and you've already started drinking....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    and I thought xp was used for extended stored procedures ... so should MS SQL server not be looking for it in master database ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I follow Tara's standard now...

    isp_ for dba sprocs

    and usp_ for developer sprocs

    And he didn't change it to xp_

    He's running it under the cintext of master....

    when you put a sproc in master with sp_ it can be referenced drom any db on that instance, without having to qualify it...

    For xp_

    You have to qualify master

    Just like master..xp_cmdshell...

    But my POINT....

    is that I don't think creating all your application sprocs in master so you don't have to distibute them to all your look alike db's...

    Why do you want to isolate the data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    But he did change it to xp_

    If I call the stored procedure something different (xp_fred) I (obviously) have to provide the complete name:
    exec master..xp_fred
    And I get the unexpected results (1 master).

    Can someone give me a short explanation or point me to some docs which explain whats going on?
    I don't like having anything touch master myself. It tends to make for upgrades that get too interesting.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh...

    So I'm the one who's drunk.....

    Never mind upgrades...

    Ever have to rebuild master?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    More than I like to say.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •