SP_Who_3

• jblovesthegym

  Ten Centuries

  Points: 1048

  More actions

  March 30, 2012 at 5:43 am

  #104137

  Hi all, I want to run the following script to track down some wait times,

  http://www.sqlservercentral.com/scripts/Miscellaneous/31227/ (this is the famed SP_Who_3)

  Is it possible to run this as an ad-hoc query (I'd need to raise a CRF to install as a sproc) ?

  I keep getting errors about declaring variables...

  Server: Msg 139, Level 15, State 1, Line 2

  Cannot assign a default value to a local variable.

  Server: Msg 137, Level 15, State 1, Line 33

  Must declare the variable '@login'.

  Server: Msg 137, Level 15, State 1, Line 46

  Must declare the variable '@spid'.

  Server: Msg 137, Level 15, State 1, Line 116

  Must declare the variable '@run_mode'.

  Server: Msg 137, Level 15, State 1, Line 153

  Must declare the variable '@o'.

  Server: Msg 137, Level 15, State 1, Line 157

  Must declare the variable '@run_mode'.

  Server: Msg 137, Level 15, State 1, Line 165 etc

  Thanks all,

  JB

• Andre Guerreiro

  SSCertifiable

  Points: 7319

  More actions

  March 30, 2012 at 5:57 am

  #1466872

  If I get this right and you want to run this without creating the procedure, you'll need to declare the variables that are passed as parameters to the procedure.

  DECLARE

  @run_mode NVARCHAR(12) = NULL,

  @spid NVARCHAR(50) = NULL,

  @login NVARCHAR(50) = NULL,

  @host NVARCHAR(50) = NULL,

  @db NVARCHAR(50) = NULL,

  @program NVARCHAR(50) = NULL,

  @status NVARCHAR(50) = NULL,

  @command NVARCHAR(50) = NULL,

  @blk NVARCHAR(50) = NULL,

  @wait NVARCHAR(50) = NULL,

  @trans NVARCHAR(50) = NULL,

  @cpu NVARCHAR(50) = NULL,

  @dsk NVARCHAR(50) = NULL,

  @last_batch NVARCHAR(100) = NULL,

  @o NVARCHAR(100) = NULL

  Instead of NULL you'll have to replace it with the right values for each variable that you're going to use. Leave the others NULL because it's the default.

  I'm assuming that you will need to create the objects declared before the SP_WHO3 definition if you want to run this without errors but I can't take a detailed look right now. 🙂

  Hope it helps.

  Best regards,

  Andre Guerreiro Neto

  Database Analyst
  http://www.softplan.com.br
  MCITPx1/MCTSx2/MCSE/MCSA

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 30, 2012 at 6:08 am

  #1466876

  the error 'cannot assign a default variable' shows the script is for 2008+, and you are running it on 2005;

  it'll need a couple of minor tweaks.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jblovesthegym

  Ten Centuries

  Points: 1048

  More actions

  March 30, 2012 at 7:10 am

  #1466920

  Actually runnning on 2k !

• Andre Guerreiro

  SSCertifiable

  Points: 7319

  More actions

  March 31, 2012 at 9:53 am

  #1467312

  Sorry, misread the error messages.

  Best regards,

  Andre Guerreiro Neto

  Database Analyst
  http://www.softplan.com.br
  MCITPx1/MCTSx2/MCSE/MCSA

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  March 31, 2012 at 11:40 am

  #1467320

  The following link contains code for sp_who_3 up from Version 7 to SQL Server 2005:

  http://www.tek-tips.com/faqs.cfm?fid=6561

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/