Prev: 'SQLOLEDB' failed with no error message available, result code:E_OUTOFMEMORY(0x8007000E).
Next: GhostCleanUpTask - every 4 seconds!?
From: Simon on 16 Nov 2006 06:38
Has anyone ever used the sp_who3 stored procedure. I can get it running fine
on SQL2000 but it falls over on SQL7. Does anyone have a SQL7 version or can
you spot the issue with the script if not ?

Slightly altered script below:-

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE sp_HelpDeskSQLCheck
@loginame sysname = NULL,
@hostname sysname = NULL
as

set nocount on

if @hostname is null set @hostname = '0'

declare
@retcode int

declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int

declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)

declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)

--------

select
@retcode = 0 -- 0=good ,1=bad.

--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))

select
@spidlow = 0
,@spidhigh = 32767

--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_1Parameter1

--------

-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from master.dbo.syslogins where loginname = @loginame)
select @sid1 = sid from master.dbo.syslogins where loginname = @loginame

IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_1Parameter1
end

--------

IF (lower(@loginame) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame)
GOTO LABEL_1Parameter1
end

--------

IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_1Parameter1
end

--------

RaisError(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_2Return


LABEL_1Parameter1:


-------------------- Capture consistent sysprocesses. -------------------

SELECT

spid
,CAST(null AS VARCHAR(5000)) as commandtext
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'

, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'

INTO #tb1_sysprocesses
from master.dbo.sysprocesses (nolock)

/*******************************************

RETURNS LAST COMMAND EXECUTED BY EACH SPID

********************************************/

CREATE TABLE #spid_cmds
(SQLID INT IDENTITY, spid INT, EventType VARCHAR(100), Parameters INT,
Command VARCHAR(5000))

DECLARE spids CURSOR FOR
SELECT spid FROM #tb1_sysprocesses

DECLARE @spid INT, @sqlid INT

OPEN spids
FETCH NEXT FROM spids
INTO @spid

/*
EXECUTE DBCC INPUTBUFFER FOR EACH SPID
*/

WHILE (@@FETCH_STATUS = 0)
BEGIN

INSERT INTO #spid_cmds (EventType, Parameters, Command)
EXEC('DBCC INPUTBUFFER( ' + @spid + ')')

SELECT @sqlid = MAX(SQLID) FROM #spid_cmds

UPDATE #spid_cmds SET spid = @spid WHERE SQLID = @sqlid

FETCH NEXT FROM spids INTO @spid

END

CLOSE spids
DEALLOCATE spids

UPDATE p
SET p.commandtext = s.command
FROM #tb1_sysprocesses P
JOIN #spid_cmds s
ON p.spid = s.spid

---------------------------------------------

--------Screen out any rows?

IF (@loginame IN ('active'))
DELETE #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)

and blocked = 0



--------Prepare to dynamically optimize column widths.


Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)



SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)

,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)

,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),cpu)))) ,7)
)

,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),physical_io)))) ,6)
)

,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),cmd)))) ,7)
)

,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),hostname)))) ,8)
)

,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),program_name)))) ,11)
)

,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength(
rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
whe
 | 
Pages: 1
Prev: 'SQLOLEDB' failed with no error message available, result code:E_OUTOFMEMORY(0x8007000E).
Next: GhostCleanUpTask - every 4 seconds!?