How to inspect global temp table from outside the transaction it is created in

Question

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?

Answer 1

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

Answer 2

Not really, no.

The created, but uncommitted, global temporary table is protected by a schema-modification lock, which is incompatible with everything else, including the minimal schema-stability lock required under read uncommitted isolation.

It's hard to guess why you need this, but the usual issues revolve around the lifetime of the global temporary table. The long-standing solution to that is to create the global temporary table in a procedure marked to run at instance startup. A global temporary table created that way is never automatically dropped.

In Azure SQL Database (currently public preview) and presumably for the box product in future, there is the GLOBAL_TEMPORARY_TABLE_AUTODROP argument to ALTER DATABASE SCOPED CONFIGURATION.

Answer 3

If it's possible to add a little code to the current process, you might be able to cheat a little.

--Demo setup
DROP TABLE IF EXISTS [dbo].[CUSTOMER]
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


USE [Test]
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (13579246, N'David', N'Casey', N'[email protected]', 12826363, N'34 Bray Rd', N'Dublin', 94724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (16579946, N'Donal', N'Smith', N'[email protected]', 13826563, N'78 Ballybrack Rd', N'Dublin', 14724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (17579946, N'Steve', N'Reidy', N'[email protected]', 13827563, N'22 Conna Rd', N'Cork', 64724274)
GO
INSERT [dbo].[CUSTOMER] ([CustomerID], [FirstName], [LastName], [Email], [PhoneNo], [StreetAddress], [City], [OrderID]) VALUES (18579946, N'Michael', N'Yule', N'[email protected]', 14827563, N'44 Blasket Rd', N'Kilkenny', 44424274)
GO

---

Outside of any transaction, create a new table to hold some XML

drop table if exists XmlDataTable
CREATE TABLE XmlDataTable (XmlDataColumn xml)

---

Now, in your transaction, create your global temp table and select from it converting to xml raw. Then insert that value into XmlDataTable.

begin transaction
drop table if exists ##temp
SELECT * INTO ##temp FROM customer  --This is your normal create for the global temp table

DECLARE @XmlData XML

--select the data from the global temp table as xml raw
SET @XmlData = (SELECT * FROM ##temp FOR XML raw)

--insert that value into LargeVarcharTable
INSERT INTO XmlDataTable VALUES (@XmlData)

--rollback     

---

In another session, you should be able to select * from xmldatatable (nolock) to select the raw xml.

select * from xmldatatable (nolock)

<row CustomerID="13579246" FirstName="David" LastName="Casey" Email="[email protected]" PhoneNo="12826363" StreetAddress="34 Bray Rd" City="Dublin" OrderID="94724274"/>
<row CustomerID="16579946" FirstName="Donal" LastName="Smith" Email="[email protected]" PhoneNo="13826563" StreetAddress="78 Ballybrack Rd" City="Dublin" OrderID="14724274"/>
<row CustomerID="17579946" FirstName="Steve" LastName="Reidy" Email="[email protected]" PhoneNo="13827563" StreetAddress="22 Conna Rd" City="Cork" OrderID="64724274"/>
<row CustomerID="18579946" FirstName="Michael" LastName="Yule" Email="[email protected]" PhoneNo="14827563" StreetAddress="44 Blasket Rd" City="Kilkenny" OrderID="44424274"/>