|
|
|
SSChasing Mays
 
Group: General Forum Members
Last Login: Monday, June 01, 2009 8:51 PM
Points: 646,
Visits: 170
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 223,
Visits: 154
|
|
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
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, June 30, 2009 12:12 PM
Points: 6,072,
Visits: 1,485
|
|
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
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 223,
Visits: 154
|
|
Hi Noel!
I can't find the sql_handle column in the SysProcesses table and "fn_get_sql is not a recognized function name"
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, June 24, 2009 2:21 PM
Points: 379,
Visits: 414
|
|
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>
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Monday, June 15, 2009 12:51 PM
Points: 80,
Visits: 46
|
|
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)
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Tuesday, June 30, 2009 12:12 PM
Points: 6,072,
Visits: 1,485
|
|
Michelle is right. I left the spid out for you to "fill in the blanks"
Cheers,
* Noel
|
|
|
|
