Global temp table usage

Question

Global temp table usage

ocean3300

SSChasing Mays

Points: 621
More actions
February 29, 2016 at 6:13 am

#399784

I am running a SQL agent job and would like to retrieve a value from global temp table.
SQL Agent Job:
Step 1: EXEC SP_1
- In this stored procedure, I am creating a global temp table and insert a value in it
Step 2: cmd
- After Step 1 is completed, I am running a cmd to disconnect VPN connection.
Step 3: EXEC SP_2
- In this stored procedure, I'd like to use the value I inserted into the global temp table in Step 1. However, the gobal temp table is no longer available at this moment.
Is this because the session is closed after the Step 1? Please advise what I need to do.
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

Global temporary tables are removed after the last process that is referencing them closes. Because you're closing the process, and no other process is referencing the table, it gets closed. You can read more about them here.[/url]

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ocean3300 SSChasing Mays Points: 621 More actions · Answer 2

Grant Fritchey (2/29/2016)
Global temporary tables are removed after the last process that is referencing them closes. Because you're closing the process, and no other process is referencing the table, it gets closed. You can read more about them here.[/url]

Thanks for the link. So..what's the alternative ways? In my case mentioned above, it seems like I can't use global temp tables.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

If the process is, record some data and close the connection, open another connection later and read that data, then I'd say you just need a regular table. Don't try using a temporary table for this. You can have the second process truncate it so the storage is reclaimed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101103 More actions · Answer 4

If it's 128 bytes or less, use CONTEXT_INFO(). That will persist for the life of the connection.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ocean3300 SSChasing Mays Points: 621 More actions · Answer 5

Thanks guys. I will create a permanent table for this. I was just hoping to see if there was a way to do it without permanent table because I really didn't want to create a new table.:-)

Lowell SSC Guru Points: 323518 More actions · Answer 6

ocean3300 (2/29/2016)
Grant Fritchey (2/29/2016)
Global temporary tables are removed after the last process that is referencing them closes. Because you're closing the process, and no other process is referencing the table, it gets closed. You can read more about them here.[/url]
Thanks for the link. So..what's the alternative ways? In my case mentioned above, it seems like I can't use global temp tables.

another option is to create a temp table without the # sign; a CREATE TABLE tempdb.dbo.DataDump will create a table that stays in place until the service is stopped and started; so it's a permanent table, still accessible by other processes, that will not disappear after a connection drops.

I'd still use a fully permanent table, as others have suggested; maybe put the table in a [Scratch] database or DBA_Utilities database, and not mess with the production db.

that might cause some permissions headaches with multi database, which would not occur with tempdb, since the public role inherits tempdb access.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 7

whoops duplicate!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101103 More actions · Answer 8

I prefer to create those types of tables in the tempdb simply because that makes them less overall overhead on the SQL instance, because of the optimizations available only in tempdb.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ocean3300 SSChasing Mays Points: 621 More actions · Answer 9

ScottPletcher (2/29/2016)
I prefer to create those types of tables in the tempdb simply because that makes them less overall overhead on the SQL instance, because of the optimizations available only in tempdb.

Thanks for your comments!