SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to preserve global temporary table data


How to preserve global temporary table data

Author
Message
rajarshi_ghosh_05
rajarshi_ghosh_05
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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....
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69720 Visits: 40917
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
--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!
rajarshi_ghosh_05
rajarshi_ghosh_05
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69720 Visits: 40917
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
--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!
rajarshi_ghosh_05
rajarshi_ghosh_05
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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?
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69720 Visits: 40917
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
--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
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19145 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92495 Visits: 38954
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.

Cool
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)
rajarshi_ghosh_05
rajarshi_ghosh_05
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 30
Great idea!!!w00t 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.... :-)
rajarshi_ghosh_05
rajarshi_ghosh_05
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search