Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 28 Aug 2006


Question of the Day for 28 Aug 2006

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10095 Visits: 1
Comments posted to this topic are about the Question of the Day for 28 Aug 2006 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=846.
John Kelly-334278
John Kelly-334278
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 5

Wouldnt it Print 'Surprise' and then system info from sp_who?


SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3457 Visits: 1017
Nope...the PRINT part is only part of the sp_who created in Northwinds. When the script actually runs (EXECUTE sp_who) it runs the version in Master. That is why 'best practice' is to fully qualify objects. EXECUTE Northwind.dbo.sp_who should have worked and printed Surprise.

-SQLBill



MattieNH
MattieNH
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 901

So does the statement

EXECUTE Northwind.dbo.sp_who should have worked and printed Surprise.

make this

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.

somewhat incomplete, or misleading?

Mattie





SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3457 Visits: 1017
Nope. The issue is with NAMING stored procedures. This is why you should NEVER name a stored procedure with the beginning 'sp_'. That automatically defaults to the master database. So, if you run a stored procedure and the name is sp_something; SQL Server first looks for it in the Master database. If it finds it, then it runs it. If not, then it 'may' look in the current database for it. But the default is to look in Master. That is why you either create it as something like: usp_something or fully qualify it like: northwind.dbo.sp_something.

(BTW - usp_ is commonly used for 'u'ser 's'tored 'p'rocedure).

-SQLBill



Joshua M Perry
Joshua M Perry
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 545
Since I am following security best practices and not installing unnecessary components, I of course, get an error. You did not specify that we are NOT following security best practices.

Joshua Perry
http://www.greenarrow.net
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47343 Visits: 44392

EXECUTE Northwind.dbo.sp_who should have worked and printed Surprise.

Actually not, it has something to do with the name resolution, but any procedure that starts sp_ is first looked for in master, no matter if it's fully qualified or not. I tried that just now.

USE Northwind
GO

CREATE PROCEDURE sp_who
AS
PRINT
'Surprise'
GO

EXEC sp_who
EXEC Northwind.dbo.sp_who

and in both cases I got the process list back.

I do recall playing with this some time back, and if the user-created stored proc is owned by someone other than dbo, then it can be executed with a fully qualified name. If it's owed by dbo then the one in master is always run, no matter how qualified the call is.




Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
depends on which grid...my sql 2000 query analyzer jumps to the messages grid displaying the error. On the data grid, the output of sp_who...Smile
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 2763
i tried to created a stored procedure schema specific for sp_who but am not able to create that...
can you plz tell me how to create procedure specific to a schema as i also tried to execute sp_who but it returns the same result..
I am able to created any other Stored procedre rather than sp_who with different schema but not with this this one

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 2763
Hey I am able to create sp_who to a schema specific.. and it result whatever i wrote in stored procedre no other information is showing as it shows when run by master database..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search