April 13, 2007 at 10:03 am
I'd like to know what command SPID ### is executing. I run DBCC INTPUTBUFFER(###), and I get back something like "sys.sp_execute;1". How do I find out what command/query that actually is?
I believe I read once (Kalen Delaney, no less) that it wasn't really possible within SQL 2000--nothing existied to directly connect sysCacheObjects with the sp_prepare, sp_execute, and sp_unprepare family. Has this been addressed in SQL 2005? If so, how? (Gotta be in the sys.dm_* stuff, which material is really hurting my brain these days...)
Philip
April 13, 2007 at 1:40 pm
I'd like to know a little bit more about the scenario. I don't know if this would be helpful:
CREATE TABLE dbo.mytable(field1 int, field2 varchar(10))
GO
CREATE TRIGGER trgMyTable ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DBCC INPUTBUFFER(@@SPID)
END
GO
CREATE PROCEDURE dbo.iMyTable(
@Field1 INT
, @Field2 VARCHAR(10)
)
AS
INSERT INTO dbo.mytable(field1, field2) VALUES (@Field1, @Field2)
GO
EXEC dbo.iMyTable @Field1 = 1, @Field2 = 'uno'
--DROP TABLE dbo.MyTable
--DROP PROCEDURE dbo.iMyTable
April 13, 2007 at 4:03 pm
In 2000
get the sql_handle for the spid and use
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid =
SELECT * FROM ::fn_get_sql(@Handle)
In 2005 use
SELECT session_id, text
FROM sys.dm_exec_requests AS r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS s
WHERE session_id =
* Noel
April 13, 2007 at 5:43 pm
Hi Noel!
I can't find the sql_handle column in the SysProcesses table and "fn_get_sql is not a recognized function name"
April 16, 2007 at 10:44 am
You can also try this in 2005:
select
session_id,
text
from sys.dm_exec_requests s1
cross apply
sys.dm_exec_sql_text(sql_handle) as s2
where session_id = <SPID NUMBER>
May 8, 2007 at 9:21 am
SysProcesses is in master db. You probably forgot to put the spid number in the query. Here's what the query would look like to run in any db (replace 60 with your own spid):
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses
WHERE spid = 60
SELECT * FROM ::fn_get_sql(@Handle)
May 9, 2007 at 3:25 pm
Michelle is right. I left the spid out for you to "fill in the blanks"
Cheers,
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply