July 24, 2003 at 7:45 am
Hi!
Used this code for stored procedure:
CREATE PROCEDURE dbo.blocks_history AS
DECLARE @who_spid smallint
DECLARE @block_spid smallint
...
CREATE TABLE #t (EventType nvarchar(30), Parameters Int, EventInfo nvarchar(255) )
...
insert #t exec('dbcc inputbuffer(@who_spid)')
SELECT top 1 @who_EventType = EventType
FROM #t
SELECT top 1 @who_Parameters = Parameters
FROM #t
SELECT top 1 @who_EventInfo = EventInfo
FROM #t
delete from #t
insert #t exec('dbcc inputbuffer(@block_spid)')
SELECT top 1 @block_EventType = EventType
FROM #t
SELECT top 1 @block_Parameters = Parameters
FROM #t
SELECT top 1 @block_EventInfo = EventInfo
FROM #t
delete from #t
--------------------------------------------------
Got this error:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@who_spid'.
(0 row(s) affected)
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@block_spid'.
(0 row(s) affected)
---------------------------------------------
Also tried:
CREATE PROCEDURE dbo.blocks_history AS
DECLARE @who_spid smallint
DECLARE @block_spid smallint
DECLARE @temp_who_spid smallint
DECLARE @temp_block_spid smallint
...
SET @temp_who_spid = @who_spid
SET @temp_block_spid = @block_spid
...
CREATE TABLE #t (EventType nvarchar(30), Parameters Int, EventInfo nvarchar(255) )
...
insert #t exec('dbcc inputbuffer(@temp_who_spid)')
SELECT top 1 @who_EventType = EventType
FROM #t
SELECT top 1 @who_Parameters = Parameters
FROM #t
SELECT top 1 @who_EventInfo = EventInfo
FROM #t
delete from #t
insert #t exec('dbcc inputbuffer(@temp_block_spid)')
SELECT top 1 @block_EventType = EventType
FROM #t
SELECT top 1 @block_Parameters = Parameters
FROM #t
SELECT top 1 @block_EventInfo = EventInfo
FROM #t
delete from #t
---------------------------------------------
Also got:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@temp_who_spid'.
(0 row(s) affected)
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@temp_block_spid'.
(0 row(s) affected)
=======================================
What I am doing wrong?
Thanks.
July 24, 2003 at 7:47 am
Check to see that you haven't got a GO statement somewhere...it'll end the batch and put the variable declarations out of scope.
July 24, 2003 at 7:49 am
Actually, I just noticed what it is: if you use the variable in the EXEC statement you need to put together the SQL string for DBCC INPUTBUFFER by concatenating the variable, not including it within the static string passed to EXEC...
July 24, 2003 at 7:55 am
No "GO" statement... Also, if I remove this code:
---------------------------------------------
insert #t exec('dbcc inputbuffer(@who_spid)')
SELECT top 1 @who_EventType = EventType
FROM #t
SELECT top 1 @who_Parameters = Parameters
FROM #t
SELECT top 1 @who_EventInfo = EventInfo
FROM #t
delete from #t
insert #t exec('dbcc inputbuffer(@block_spid)')
SELECT top 1 @block_EventType = EventType
FROM #t
SELECT top 1 @block_Parameters = Parameters
FROM #t
SELECT top 1 @block_EventInfo = EventInfo
FROM #t
delete from #t
---------------------------------------------
but leave this:
---------------------------------------------
FETCH NEXT FROM block_list INTO
@who_sql_handle, @who_spid, ...
@block_sql_handle, @block_spid, ...
---------------------------------------------
Everything works fine...
July 24, 2003 at 8:02 am
Like this:
insert #t exec('dbcc inputbuffer(' + '@who_spid' + ')')
?
July 24, 2003 at 8:05 am
quote:
Like this:insert #t exec('dbcc inputbuffer(' + '@who_spid' + ')')
?
Almost:
insert #t exec('dbcc inputbuffer(' + @who_spid + ')')
July 24, 2003 at 8:12 am
Greate!!!
Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply