April 22, 2010 at 1:23 pm
Comments posted to this topic are about the item Another sp_who3
April 27, 2010 at 7:16 am
I get the following error when i execute this script. Thanks for the script.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 3
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 31
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 51
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 52
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 54
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 55
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure sp_who3, Line 61
Must declare the scalar variable "@s_spid".
Msg 102, Level 15, State 1, Procedure sp_who3, Line 66
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 68
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 69
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure sp_who3, Line 79
Incorrect syntax near '?'.
April 27, 2010 at 9:31 am
Incorrect syntax near '?'.
Would it be possible to get a revised script posted with the errors above?
...thanks
April 27, 2010 at 10:35 am
I get the same errors - not sure why - anyone know?
April 27, 2010 at 10:42 am
I get the same errors
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 50
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 51
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 60
Must declare the scalar variable "@s_spid".
Msg 102, Level 15, State 1, Line 65
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 67
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 68
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 78
Incorrect syntax near '?'.
April 27, 2010 at 3:22 pm
The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.
Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)
April 28, 2010 at 12:01 am
Roman Vinnik (4/27/2010)
The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)
Thanks for getting this cleared up... my first post using the required sql server central script plugin and it for some reason changed it to some wierd characters.
Anyone know why this is happening as I was only copy-pasting from SSMS?
April 28, 2010 at 12:04 am
Here is a revised script...
CREATE PROCEDURE [dbo].[sp_who3]
@SPID INT = NULL,
@DBName VARCHAR(255) = NULL,
@running BIT = NULL,
@blocked BIT = NULL,
@eventinfo VARCHAR(100) = NULL
AS
SET NOCOUNT ON
DECLARE @iSPID int
CREATE TABLE #spwho (
SPID int NOT NULL
, Status varchar (255) NOT NULL
, Login varchar (255) NOT NULL
, HostName varchar (255) NOT NULL
, BlkBy varchar(10) NOT NULL
, DBName varchar (255) null
, Command varchar (255) NOT NULL
, CPUTime int NOT NULL
, DiskIO int NOT NULL
, LastBatch varchar (255) NOT NULL
, ProgramName varchar (255) null
, SPID2 int NOT NULL
, REQUESTID int NOT NULL
)
CREATE TABLE #dbcc (
SPID int,
EventType varchar(255),
Paramters int,
EventInfo varchar(8000)
)
INSERT #spwho
EXEC sp_who2
DECLARE buf CURSOR FAST_FORWARD FOR
SELECT SPID FROM #spwho
OPEN buf
FETCH NEXT FROM buf
INTO @iSPID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @s_spid VARCHAR(10)
SET @s_spid = CAST(@iSPID AS varchar(10))
DECLARE @dbcctab TABLE (
EventType varchar(255),
Paramters int,
EventInfo varchar(8000)
)
INSERT @dbcctab
EXEC ('dbcc inputbuffer(' + @s_spid + ') WITH NO_INFOMSGS')
INSERT #dbcc
SELECT @iSPID, * FROM @dbcctab
DELETE FROM @dbcctab
FETCH NEXT FROM buf
INTO @iSPID
END
CLOSE buf
DEALLOCATE buf
SET NOCOUNT OFF
SELECT
s.SPID,
d.EventInfo,
s.Status,
s.Login,
s.HostName,
s.BlkBy,
s.DBName,
s.Command,
s.CPUTime,
s.DiskIO,
s.LastBatch,
s.ProgramName,
s.REQUESTID
FROM
#spwho s
LEFT JOIN #dbcc d ON
s.SPID = d.SPID
WHERE
(@SPID IS NULL OR s.SPID = @SPID)
AND (@blocked IS NULL OR (@blocked = 1 AND LTRIM(RTRIM(s.BlkBy)) != '.') OR (@blocked = 0 AND LTRIM(RTRIM(s.BlkBy)) = '.'))
AND (@running IS NULL OR (@running = 1 AND s.Status != 'sleeping') OR (@running = 0 AND s.Status = 'sleeping'))
AND (@DBName IS NULL OR s.DBName = @DBName)
AND (@eventinfo IS NULL OR d.EventInfo LIKE @eventinfo)
ORDER BY
LastBatch DESC
April 28, 2010 at 6:34 am
Good work mate.
Works good for me.
Thanks
May 4, 2010 at 6:24 pm
Kristian Ask (4/28/2010)
Roman Vinnik (4/27/2010)
The indentation uses some weird characters. In my case I pasted it into an advanced text editor and they came up with question marks. I just replaced the question marks with spaces and it worked great.Also, if you want to run this in MSSQL 2000, replace the table variable with a temporary table (ie. @dbcctab becomes #dbcctab)
Thanks for getting this cleared up... my first post using the required sql server central script plugin and it for some reason changed it to some wierd characters.
Anyone know why this is happening as I was only copy-pasting from SSMS?
That problem happens frequently.
A simple solution is to copy to notepad++ and then to SSMS after copying from SSC.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2010 at 6:25 pm
I find this to be an interesting solution.
Nice Job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply