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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: Administrators
Points: 18681 Visits: 225
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
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 5

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


SQLBill
SQLBill
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25402 Visits: 1089
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
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5143 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25402 Visits: 1089
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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 552
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 (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)SSC Guru (555K reputation)

Group: General Forum Members
Points: 555515 Visits: 47767

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
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: 3685 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
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: 10707 Visits: 2778
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
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: 10707 Visits: 2778
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