Mixing
How to inspect global temp table from outside the transaction it is created in
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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?
sql-server transaction tempdb temporary-tables
asked Jan 31 at 19:08
LaurenceLaurence
22029
add a comment |
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?
sql-server transaction tempdb temporary-tables
asked Jan 31 at 19:08
LaurenceLaurence
22029
add a comment |
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?
sql-server transaction tempdb temporary-tables
asked Jan 31 at 19:08
LaurenceLaurence
22029
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?
sql-server transaction tempdb temporary-tables
sql-server transaction tempdb temporary-tables
asked Jan 31 at 19:08
LaurenceLaurence
22029
asked Jan 31 at 19:08
LaurenceLaurence
22029
edited Feb 1 at 11:40
Laurence
asked Jan 31 at 19:08
LaurenceLaurence
22029
asked Jan 31 at 19:08
LaurenceLaurence
22029
asked Jan 31 at 19:08
LaurenceLaurence
22029
22029
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
add a comment |
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.
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
add a comment |
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"/>
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228597%2fhow-to-inspect-global-temp-table-from-outside-the-transaction-it-is-created-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
add a comment |
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
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
add a comment |
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
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
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
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
answered Jan 31 at 20:39
David Browne - MicrosoftDavid Browne - Microsoft
12.2k729
12.2k729
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
add a comment |
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
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
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
Feb 1 at 20:36
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
Feb 1 at 20:36
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
Yes. This feature is both ancient and obscure.
– David Browne - Microsoft
Feb 1 at 22:41
add a comment |
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.
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
add a comment |
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.
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
add a comment |
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.
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
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.
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
edited Jan 31 at 22:31
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
answered Jan 31 at 20:11
Paul White♦Paul White
54.1k14287461
54.1k14287461
add a comment |
add a comment |
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"/>
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
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
add a comment |
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"/>
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
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
add a comment |
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"/>
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
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"/>
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
edited Jan 31 at 21:10
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
answered Jan 31 at 20:38
Scott HodginScott Hodgin
18.3k21635
18.3k21635
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
add a comment |
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
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
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
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228597%2fhow-to-inspect-global-temp-table-from-outside-the-transaction-it-is-created-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
