Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to preserve global temporary table data Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 1:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Hi guys,

I would like to know if anyway to preserve global temporary table data till some one explicitly drop it.

I have created one global ##tmp table and inserted data into it in one procedure (proc1) and selecting the data from another procedure (proc2).

If i execute the proc2 immediately after proc1 finished.. there is no issue but when i execute the same proc2 after few min later (for say around 15/20 min) i found that the global tmp table has been dropped!!!!

how to achieve the result without creating any physical table!!!

Thanks for your help....
Post #1437061
Posted Friday, March 29, 2013 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 12,876, Visits: 31,782
even global temp tables get dropped if all the sessions that referenced it disconnect, i believe.

you'd need to start adding logic to create if not exists to your process, if you refuse to use a permanent table.

It really depends on what you are using teh global temp table for.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437064
Posted Friday, March 29, 2013 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Actually i am importing csv file in the database through sp. the temp table is required to keep the validation error messages. When user wants to see the error messages then i need to select the entire table rows from this temp table and send to user in excel. If there is any delay from user end the table drops!!!

Is there any way to keep the reference to the global tmp table till i explicitly dropped it!!!

Thanks
Post #1437075
Posted Friday, March 29, 2013 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 12,876, Visits: 31,782
the only sure fire way to keep the keep that global temp table around is by having the process which created the temp table keep it's connection open. that is the only way to guarantee that a global temp table will remain in scope.

It really sounds like a permanent table would be better; especially for situations where the table gets created Friday, but the first person in the office Monday used an app that expected that info to still be there.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437078
Posted Friday, March 29, 2013 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
The highest time to keep the table alive is 120min.

Is it possible to keep the table alive by locking it or anything else?
Post #1437079
Posted Friday, March 29, 2013 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 12,876, Visits: 31,782
rajarshi_ghosh_05 (3/29/2013)
The highest time to keep the table alive is 120min.

Is it possible to keep the table alive by locking it or anything else?


again, no, it is not possible to guarantee that a global temp table will stay in place.

instead,create a permanent table, and add a job to delete/drop the table after 120 minutes, or have the application check a date that exists inside the now-permanent table, so the data is not used if it is over 120 minutes old.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437088
Posted Friday, March 29, 2013 3:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 1,946, Visits: 2,877
Note that you can use a "real" table name in tempdb; that is, a table name that does not begin with # or ##. The table will then remain until you drop it or tempdb is recreated.

You could also have a process that periodically dropped any non-temp table in tempdb that was created more than 120 minutes ago.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1437104
Posted Friday, March 29, 2013 3:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437105
Posted Friday, March 29, 2013 3:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Great idea!!! This should works for me!! i just tried a little bit testing on this and it works!! .... Will post u the details after i actually implemented it..

Thanks again....
Post #1437106
Posted Friday, March 29, 2013 3:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 12:16 PM
Points: 10, Visits: 30
Lynn Pettis (3/29/2013)
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.


That's true but there are several process that needs to be followed prior to add anything in db design... so as long as the table created by sp and destroyed by DB itself... i guess we are ok...
Post #1437108
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse