SQL Clone
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 (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12569 Visits: 16
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 (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 5

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


SQLBill
SQLBill
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11313 Visits: 1085
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3727 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11313 Visits: 1085
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
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 551
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 Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216391 Visits: 46278

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
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 2767
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5200 Visits: 2767
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