SQLServerCentral / SQL Server 2005 / Administering / SP_EXECUTE executing... what? / Latest Posts

RE: SP_EXECUTE executing... what?

noeld — Wed, 09 May 2007 15:25:00 GMT

Michelle is right. I left the spid out for you to "fill in the blanks"Cheers,

RE: SP_EXECUTE executing... what?

Michelle-138172 — Tue, 08 May 2007 09:21:00 GMT

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 = 60SELECT * FROM ::fn_get_sql(@Handle)

RE: SP_EXECUTE executing... what?

Mark Shvarts-166311 — Mon, 16 Apr 2007 10:44:00 GMT

You can also try this in 2005:

select session_id,text from sys.dm_exec_requests s1cross applysys.dm_exec_sql_text(sql_handle) as s2where session_id = <SPID NUMBER>

RE: SP_EXECUTE executing... what?

Aaron Gonzalez-394690 — Fri, 13 Apr 2007 17:43:00 GMT

Hi Noel!I can't find the sql_handle column in the SysProcesses table and "fn_get_sql is not a recognized function name"

RE: SP_EXECUTE executing... what?

noeld — Fri, 13 Apr 2007 16:03:00 GMT

In 2000get 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 useSELECT session_id, textFROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sWHERE session_id =

RE: SP_EXECUTE executing... what?

Aaron Gonzalez-394690 — Fri, 13 Apr 2007 13:40:00 GMT

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))GOCREATE TRIGGER trgMyTable ON dbo.MyTableFOR INSERT, UPDATE, DELETEASBEGINDBCC INPUTBUFFER(@@SPID)ENDGOCREATE PROCEDURE dbo.iMyTable( @Field1 INT , @Field2 VARCHAR(10))AS INSERT INTO dbo.mytable(field1, field2) VALUES (@Field1, @Field2)GOEXEC dbo.iMyTable @Field1 = 1, @Field2 = 'uno'--DROP TABLE dbo.MyTable--DROP PROCEDURE dbo.iMyTable

SP_EXECUTE executing... what?

Philip Kelley — Fri, 13 Apr 2007 10:03:00 GMT

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