sp_who2 returns what data types?

[katbear]

Help. I need to send the output of sp_who2 to a temp table.

In order to create the temp table, I need the data types of the columns returned by sp_who2. I'm looking at the underlying code to find the tables/columns.

My table definition:

CREATE TABLE ##activespids(
SPID smallint,
Status varchar(255),
Login varchar(255),
Hostname varchar(255),
BlkBy smallint,
DBName varchar(255),
Command varchar(255),
CPUTime smallint,
DiskIO bigint,
LastBatch datetime,
ProgramName varchar(255),
SPID2 smallint
)

I keep getting this error when trying to insert output of sp_who2 to the table:

Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the char value ' . ' to a column of data type int.

 

[ptheriault]

Here is one for sp_who. I use this script to kill all users in a database.

[COLOR=blue]CREATE[/color] [COLOR=blue]table[/color] #tmpUsers(
spid [COLOR=blue]int[/color],
eid [COLOR=blue]int[/color],
status [COLOR=blue]varchar[/color](30),
loginname [COLOR=blue]varchar[/color](50),
hostname [COLOR=blue]varchar[/color](50),
blk [COLOR=blue]int[/color],
dbname [COLOR=blue]varchar[/color](50),
cmd [COLOR=blue]varchar[/color](30))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #tmpUsers [COLOR=blue]EXEC[/color] SP_WHO


[COLOR=blue]DECLARE[/color] LoginCursor [COLOR=blue]CURSOR[/color]
READ_ONLY
[COLOR=blue]FOR[/color] [COLOR=blue]SELECT[/color] spid, dbname [COLOR=blue]FROM[/color] #tmpUsers [COLOR=blue]WHERE[/color] dbname = @dbname

[COLOR=blue]DECLARE[/color] @spid [COLOR=blue]varchar[/color](10)
[COLOR=blue]DECLARE[/color] @dbname2 [COLOR=blue]varchar[/color](40)
[COLOR=#FF00FF]OPEN[/color] LoginCursor

[COLOR=blue]FETCH[/color] [COLOR=blue]NEXT[/color] [COLOR=blue]FROM[/color] LoginCursor [COLOR=blue]INTO[/color] @spid, @dbname2
[COLOR=blue]WHILE[/color] (@@fetch_status <> -1)
[COLOR=blue]BEGIN[/color]
        [COLOR=blue]IF[/color] (@@fetch_status <> -2)
        [COLOR=blue]BEGIN[/color]
        [COLOR=blue]PRINT[/color] [COLOR=red]'Killing '[/color] + @spid
        [COLOR=blue]SET[/color] @strSQL = [COLOR=red]'KILL '[/color] + @spid
        [COLOR=blue]EXEC[/color] (@strSQL)
        [COLOR=blue]END[/color]
        [COLOR=blue]FETCH[/color] [COLOR=blue]NEXT[/color] [COLOR=blue]FROM[/color] LoginCursor [COLOR=blue]INTO[/color]  @spid, @dbname2
[COLOR=blue]END[/color]

[COLOR=blue]CLOSE[/color] LoginCursor
[COLOR=blue]DEALLOCATE[/color] LoginCursor

[COLOR=blue]DROP[/color] [COLOR=blue]table[/color] #tmpUsers


[COLOR=blue]GO[/color]

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

If you want to use sp_who2 here are the datatypes. It's an undocumented procedure so you won't find it in BOL. I used google.

[COLOR=blue]create[/color] [COLOR=blue]table[/color] #spwho (
   SPID [COLOR=blue]int[/color] not null
 , Status [COLOR=blue]varchar[/color] (255) not null
 , Login [COLOR=blue]varchar[/color] (255) not null
 , HostName [COLOR=blue]varchar[/color] (255) not null
 , BlkBy [COLOR=blue]varchar[/color](10) not null
 , DBName [COLOR=blue]varchar[/color] (255) null
 , Command [COLOR=blue]varchar[/color] (255) not null
 , CPUTime [COLOR=blue]int[/color] not null
 , DiskIO [COLOR=blue]int[/color] not null
 , LastBatch [COLOR=blue]varchar[/color] (255) not null
 , ProgramName [COLOR=blue]varchar[/color] (255) null
 , SPID2 [COLOR=blue]int[/color] not null
)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Blocked by is the one that is killing you. It looks like a number but it's not.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Thanks Paul, your script is exactly what I'm trying to do.