sql query to find statement currently executing in sp

Question

Post reply

sql query to find statement currently executing in sp

Tanveer-700199

Ten Centuries

Points: 1230
More actions
September 11, 2008 at 5:40 am

#123514

Hi,
After running dbcc inputbuffer() , I get only the stored procedure that is executing, but not the exact statement that is running within that stored procedure. Please provide sql query to find statement currently executing in stored procedure.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

Why? What is it that you're trying to accomplish?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

you need to pass SPID or hard coded it

use following script

CREATE TABLE [dbo].[#Sp_info](

[Eventtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[parameters] [int] NULL,

[eventinfo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

declare @spid int,@lstr varchar(100),@text nvarchar(max)

set @spid = 51

set @lstr = 'dbcc inputbuffer( ' +cast( @spid as varchar) + ')'

insert into #Sp_info

exec (@lstr)

select @text = eventinfo from #Sp_info

SELECT sys.sysobjects.name, sys.syscomments.text

FROM sys.sysobjects INNER JOIN syscomments

ON sys.sysobjects.id = sys.syscomments.id

WHERE sys.syscomments.text LIKE '%@text %' ----KEYWORD

AND sys.sysobjects.type = 'P' ----FOR VIEW.SP/TABLE

ORDER BY sys.sysobjects.NAME

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Input buffer only returns the name of the stored proc, if it is a stored proc that's running. If it's an ad-hoc SQL batch, it will return the query

If you want to see the exact statement that's running within the proc, use the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs, and use the statement_start_offset and statement_end_offset columns within that to substring the text.

Something (roughly) like this:

select

SUBSTRING(st.text, (er.statement_start_offset/2)+1,

((CASE er.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1) AS statement_text

from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(er.sql_handle) st

where er.session_id = 54

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Gila ...you are genius....i always find reply everywhere and with sounded logics:):)

welll r u DBA or Developer and from which country?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

Heh... I still want to know why the op wants to do this. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jeff Moden (9/23/2008)
Heh... I still want to know why the op wants to do this. :hehe:

Dunno about the OP, but I had a very similar piece of code in one of my scripts to check blocking.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

Yeah, but what does the OP want to do with it? 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Hi Gila,

i m directing you towards the problem of a lady ....please help her ,i m sure u wont mind it as i posted this reply beyond this TOPIC:)

http://www.sqlservercentral.com/Forums/Topic573108-357-1.aspx#bm575413

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (9/24/2008)
Yeah, but what does the OP want to do with it? 🙂

<shrug> Beats me. We'll probably never know

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass