Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question of the Day for 28 Aug 2006 Expand / Collapse
Author
Message
Posted Thursday, August 24, 2006 4:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, 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.
Post #303988
Posted Monday, August 28, 2006 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2008 8:10 AM
Points: 7, Visits: 5

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

 

Post #304398
Posted Monday, August 28, 2006 8:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 3,248, Visits: 557
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



Post #304462
Posted Monday, August 28, 2006 11:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:07 AM
Points: 2,645, Visits: 790

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




Post #304518
Posted Monday, August 28, 2006 11:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 3,248, Visits: 557
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



Post #304531
Posted Tuesday, August 29, 2006 8:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, February 6, 2014 9:39 AM
Points: 420, Visits: 487
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.usesage.com
Post #304668
Posted Wednesday, August 30, 2006 1:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681

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 2008, MVP
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

Post #304880
Posted Monday, February 25, 2008 4:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:48 PM
Points: 1,136, Visits: 699
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...:)
Post #460015
Posted Thursday, November 22, 2012 4:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 1,926, Visits: 2,356
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/
Post #1387823
Posted Thursday, November 22, 2012 4:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 1,926, Visits: 2,356
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/
Post #1387828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse