temporary tables in sql server 2005 how persistent is it

Posted on 2009-02-17
Medium Priority
Last Modified: 2012-05-06
on a sql agent job created 2 steps
step1 creates temporary tables by running sql script
step2 reads these temporary tables, does some processing and writes to a new table

what i found was, as soon as the step1, exists, the temporary tables disappears

how persistent are the temporary tables. can it be used across different sessions

Question by:Greens8301
  • 2
LVL 39

Accepted Solution

BrandonGalderisi earned 1000 total points
ID: 23663562
Temporary tables go out of scope when the connection is disconnected.  If Storedprocedure1 creates a temp table, and it then calls StoredProcedure2, StoredProcedure2 can access it.  When Step 1 exits, the table goes out of scope and is dropped so Step 2 cannot access it.
LVL 60

Assisted Solution

chapmandew earned 1000 total points
ID: 23663650
global temp tables (prefixed with ##), go out to scope when ALL connections using them go out of scope

Author Comment

ID: 23663841
Does global temporary table work across steps in the SQL Agent job

"ALL connections using them go out of scope"
When step1 completes, there are no connects to the tables. Does this constitute ALL connections
Then when step2 starts, (probably a different session id), can it see GTT


LVL 60

Expert Comment

ID: 23663893
yes, in that case it would go out to scope (even the global)...why not use a user-table instead?

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question