-- Script A in a 2 script demo of how to use fn_get_sql.
-- Execute batches in numeric order.

-- Batch A-1   Moves to the Pubs sample database
PRINT 'Batch A-1  Script A''s SPID = ' + CAST (@@SPID as varchar)
DBCC TRACEON(2861)
Use Pubs
go

-- Batch A-2
PRINT 'Batch A-2 Begin a transaction and create the blockage'
BEGIN TRAN -- the transaction will cause 
    DELETE FROM authors WHERE au_id = '527-72-3246'
GO
-- Stop Batch A-2 here

-- Now go open a new connection and run batches B-3 and B-4
-- Come back here when you're done.

-- At this point batch B-4 should be running and you have 
-- the SPID of Script B that was printed by Batch B-3

-- Batch A-5  sp_lock shows who's waiting and who's locking
PRINT 'Batch A-5 -- Output of sp_lock'
exec sp_lock
GO

-- Batch A-6  You must change the SPID number in this batch
--           before executing this step!             
PRINT 'Batch A-6 -- Get the text of the blocked connection'
DECLARE @Handle binary(20)
SELECT @handle=sql_handle 
     FROM master..sysprocesses 
     WHERE spid= 55 -- <<<<<< Change 55 to the SPID of Script B

SELECT * FROM ::fn_get_sql(@handle)

ROLLBACK TRAN -- Releases the lock on authors
GO