philcart (7/18/2008)
How about using fn_get_sql instead of dbcc inputbuffer? That way the sql statement won't be truncated. 😉
Here is the fn_get_sql version, but it will strip out any statement that may have a password. Also, you have to change the "Maximum characters per column" in query analyzer to more than 256 to see the results.
CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT
)
Create Table #SqlStatement
(spid int,
statement varchar(8000))
create table #temp (dbid varchar(100), objectid varchar(100), number varchar(100), encrypted varchar(100), stmt varchar(8000), id int identity (1,1))
INSERT #sp_who2 EXEC sp_who2
Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE @Handle binary(20)
DECLARE SpidCursor Cursor
FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @spid
insert #temp
SELECT * FROM ::fn_get_sql(@Handle)
Insert Into #SqlStatement
Select @spid, stmt From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor
INTO @spid
END
Close SpidCursor
Deallocate SpidCursor
Select B.Statement, A.* from #sp_who2 A Left JOIN
#SqlStatement B ON A.spid = B.spid
Drop Table #Temp
Drop Table #SqlStatement
Drop Table #sp_who2