Does sp_who_3 work under SQL 2005?

Question

Does sp_who_3 work under SQL 2005?

Spicoli

SSC-Addicted

Points: 435
More actions
December 5, 2007 at 9:07 pm

#127222

I'm in the process of migrating to 2005 and was wondering what the deal is with sp_who_3 under 2005. Does it work? are there any issues? is there now something built into 2005 that does the same job that should be used instead?
Any feedback would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I assume you mean sp_who2 ? Most 2000 code works on sql 2005 as pretty much all the old system tables are supported with views - this may not be the case with sql2008.

To be honest if you've custom code then rewriting to use native sql2005 dmvs is much better. Typical issues are column renaming, e.g. db_id tends to be database_id now.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Spicoli SSC-Addicted Points: 435 More actions · Answer 2

Thanks for the feedback, but I am talking about "sp_who_3". It's an extension of sp_who and sp_who2. I used it a lot in SQL 2000 as it's very quick and easy to get details on active processes, long running processes and and blocking.

See http://www.sqlservercentral.com/scripts/Miscellaneous/31227/

If there is a dmv that does the same I'm more than happy to use it.

Larry Taylor Old Hand Points: 326 More actions · Answer 3

As mentioned by the previous person who replied, SQL Server 2005 has backwards compatibility views for virtually all of the old (documented) system tables and functions. I didn't pour through the code to check every feature being used, but you could try and run it and see if it works. However, I would recommend updating this stored procedure to use DMVs/DMFs as the backwards compatibility views are deprecated, and quite frankly, not as robust. For this particular case, you will want to look at DMVs/DMFs starting with sys.dm_exec_.

Larry Taylor Old Hand Points: 326 More actions · Answer 4

Also, if you are interested in looking anything up that might not work and what the new solution is, Microsoft included a backwards compatibility guide in BOL which you can find here: http://msdn2.microsoft.com/en-us/library/ms143232.aspx.

homebrew01 SSC Guru Points: 55546 More actions · Answer 5

I parsed it in 2005 and got this:

Incorrect syntax near the keyword 'OPENROWSET'.

I didn't dig any deeper.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

without wishing to sound "nasty" there is an inherent danger in downloading and using scripts you don't understand - especially if you're using them against production systems - so if you don't understand them maybe you shouldn't use them?

To be honest extracting information from sql2000 is very easy especially against sysprocesses table - I can't see any reason why you'd need to use openrowset to get user information and as a production dba I'd be very wary of allowing such a script. the table sysprocesses still exists in sql 2005 - although you can build far better queries using the dmvs - download the system table maps and figuring it out yourself would probably be the best way to learn and understand what you need to do.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/