getting currentlly executing sql statement for specified Process Id

Question

Viewing 6 posts - 1 through 5 (of 5 total)

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

Hi,

There is another method to get the last executed SQL statement through a client session using a built in table valued function : fn_get_sql .

DECLARE @HANDLE BINARY(20)

SELECT @HANDLE = sql_handle from sys.sysprocesses where spid =

SELECT text FROM ::fn_get_sql(@handle)

But, not sure how far it is really useful.

[font="Verdana"]Renuka__[/font]

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Look in BOL for fn_get_sql (note, you will need the version of BOL updated with SP3 information) for further information, but this is what you need:-

DECLARE @Handle binary(20)

SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = ???

SELECT * FROM ::fn_get_sql(@Handle)

Abhishek Tendulkar Valued Member Points: 52 More actions · Answer 3

Hi Ian,

I have run the script using fn_get_sql.

But it is not showing any data.text column is empty.

Please respond.

Regards

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

fn_get_sql only works for queries that SQL Server decides to cache.

Check that you are getting a valid handle by selecting @Handle:-

DECLARE @Handle binary(20)

SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = ???

SELECT @Handle

If that's giving you a valid handle, and you aren't getting the SQL, then SQL Server has decided not to cache the query, and you are out of luck.

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 5

Hi

The next step you could take is to run SQL Profiler to "catch" the sql that is being sent to the SQL Server.

You will need to select the relevant filters for DB, user and type of transactions etc

Let us know if this works

Thanks

Kevin