ericb1 (2/24/2012)
I get the same error unfortunately. Also, I'm running this as a script in a query window in SQL Server Management Studio. Do I need to execute it as a stored procedure? Or run it against a particular database?Maybe I'm just executing it wrong?
thanks!
The original version had unicode stuff in it. Try pasting this one:
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO
CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(3),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)
DECLARE @spID smallint,
@Blocked VARCHAR(3),
@sqltext VARBINARY(128),
@physio BIGINT,
@hostName VARCHAR(2000),
@desSPID SMALLINT,
@dbName VARCHAR(100),
@Cmd VARCHAR(100),
@status VARCHAR(100),
@programName VARCHAR(1000),
@loginTime DATETIME,
@lastBatch DATETIME
-- Set desired SPID. If NULL, it will return all non-system SPID's.
--SET @desSPID = <Put desired SPID her to filter>
BEGIN TRY
IF @desSPID IS NOT NULL
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid = @desSPID
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
ELSE
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
OPEN spID_cursor
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spID
INSERT INTO #curSQL
SELECT @spID, @Blocked, @hostName, @dbName, @cmd, TEXT, @physio, @status, @programName, @loginTime, @lastBatch
FROM ::fn_get_sql(@sqltext)
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
END
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT * FROM #curSQL ORDER BY blocked asc, spid
END TRY
BEGIN CATCH
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg
PRINT 'There was an error in the script.'
END CATCH