Sql-server – How to inspect global temp table from outside the transaction it is created in

sql servertempdbtemporary-tablestransaction

To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction (I have no choice about that for reasons I am not going into here).

I thought I might be able to select from the temp table from outside the transaction (i.e. under a different connection) by using "with(nolock)". However, the select is blocked from completing.

Is there any way to select from the temp table from outside the transaction?

Best Answer

File this under "just because you can, doesn't mean you should"

If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session.

Eg from spid 61:

if @@TRANCOUNT > 0 rollback
go
begin transaction

select *
into ##t
from sys.objects 


declare @bind_token varchar(255);  
exec sp_getbindtoken @bind_token output
declare @bind_token_bin as varbinary(128) = cast(@bind_token as varbinary(128))
set context_info @bind_token_bin

--rollback

then from another session

declare @bind_token varchar(255) = (
    select cast(context_info as varchar(255))
    from sys.dm_exec_sessions
    where session_id = 61
)

exec sp_bindsession @bind_token
go
select * from ##t
Related Question