5

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?

17

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
  • Perfect! Word of caution to anyone who tries this - the 2nd session is now in the 1st session's transaction (as David says), so you can hold it open, commit it, roll it back etc - be careful! – Laurence Feb 1 at 11:39
  • This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Multiple Active Results Sets (MARS) or distributed transactions instead. For more information, see Using Multiple Active Result Sets (MARS). – Keeta - reinstate Monica Feb 1 at 20:36
  • Yes. This feature is both ancient and obscure. – David Browne - Microsoft Feb 1 at 22:41
8

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.

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'dcasey@gmail.com', 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'dsmith@gmail.com', 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'sreidy@gmail.com', 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'myule@gmail.com', 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="dcasey@gmail.com" PhoneNo="12826363" StreetAddress="34 Bray Rd" City="Dublin" OrderID="94724274"/>
<row CustomerID="16579946" FirstName="Donal" LastName="Smith" Email="dsmith@gmail.com" PhoneNo="13826563" StreetAddress="78 Ballybrack Rd" City="Dublin" OrderID="14724274"/>
<row CustomerID="17579946" FirstName="Steve" LastName="Reidy" Email="sreidy@gmail.com" PhoneNo="13827563" StreetAddress="22 Conna Rd" City="Cork" OrderID="64724274"/>
<row CustomerID="18579946" FirstName="Michael" LastName="Yule" Email="myule@gmail.com" PhoneNo="14827563" StreetAddress="44 Blasket Rd" City="Kilkenny" OrderID="44424274"/>
  • Good idea but I really needed to be able to see the contents of the table at any moment as the data changes. – Laurence Feb 1 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.