Tuesday, October 28, 2003 7:49 PM nielsb

View Executing Statement in SQL Server 2000

I may be the last person to know this but anyway. In SQL 2K sp3 Microsoft included a new UDF fn_get_sql(). This UDF returns among other things the text of the excuting statement for a particular spid.

You may ask what's the big deal with this; pre sp3 you used DBCC INPUTBUFER to achieve this. Well, the big deal is that DBCC INPUTBUFFER only returns the outermost statement executing. So if you have a proc that calls another proc you will only see the first proc, regardless if the actual executing proc is proc 2. Also, DBCC INPUTBUFFER only shows the first 256 characters of the proc or batch.

To test out fn_get_sql() do this:

1. create a table in your favorite database and add some data, like so:

create table test(id int identity, name varchar(15))
insert into test values('Niels')

2. Create and catalogue a stored procedure to retrieve data from the test table:

create procedure getTest @id int
select * from test where id = @id

3. Open a new session and check what the spid is for that session: select @@spid

4. Open yet a new session and execute the following code:

begin tran
update test
set name = 'Bob' 
where id = 1

5. Note that you haven't commited or rolled back the tx in step 4, so there is at the moment a lock on the data with id 1. Go now back to the session for which you noted the spid and execute the proc you created in step 2. As there still is a write lock on the data, the proc will be locked.

6. To check what statement is executing you can now use the UDF fn_get_sql(). The UDF takes a statement handle which can be retrieved from the sysprocesses table and the sql_handle column. The following code does just that:

--declare a handle variable
declare @h binary(20)
select @h = sql_handle 
from master..sysprocesses
where spid = [spid noted in step 3]
--get the text
select text from ::fn_get_sql(@h)

7. You should now see the text from the stored procedure. Do not forget to roll back or commit the tx in step 4.


No Comments