Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am at a client where they don't allow Adam Machanic's sp_WhoIsActive stored procedure to be installed anywhere on the SQL server. Does anyone have any suggestions for running it as a query instead?

share|improve this question
2  
If you look at the Stored Procedure creation you can see what it does. You can also see the DMVs it uses. It is quite complex and Adam put a lot of time into it - it really works best as a Stored Procedure as you'll see but if you look at what it does you can learn how to query the DMVs necessary to find what you need.. I would suggest you find out more about why they won't allow it installed and point them to Adam's blog about the procedure or all of the reviews, awards, and blog posts about it. –  Mike Walsh Mar 19 '13 at 15:46
    
How about using Glenn Berry's DMV scripts ... Very useful sqlserverperformance.wordpress.com/tag/dmv-queries –  Kin Mar 19 '13 at 15:49
add comment

1 Answer 1

up vote 1 down vote accepted

In general you can run any SP as a query by getting rid of the "CREATE PROCEDURE" part and defining all of the parameters as variables.

For example

CREATE PROCEDURE test (@var1 int, @var2 int) AS
PRINT @var1
PRINT @var2

Would become

DECLARE @var1 int
DECLARE @var2 int
PRINT @var1
PRINT @var2

You might also ask them if it is ok if you create a "DBA" database for code like this and put the SP there. That way the SP is on the instance and will run correctly, but is still segregated from the rest of their databases.

share|improve this answer
    
Actually this proc needs to be run in Master. –  Mike Walsh Mar 19 '13 at 16:04
2  
Per Adam at sqlblog.com/blogs/adam_machanic/archive/2012/03/22/…: "Max: why don't you just create it in tempdb? Or even better, make it into a temporary stored procedure (just put a # before the procedure name). You're allowed to create temp tables, right? This is effectively the same thing. Much easier than editing." My guess is that it wants to be put in master so it can be called from any location without referencing the DB name. –  Kenneth Fisher Mar 19 '13 at 16:55
    
interesting suggestions.... let me give it a try and get back to you –  Rizwan Mar 19 '13 at 20:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.