temporary tables in sql server 2005 how persistent is it

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

Thanks
Greens8301Asked:
Who is Participating?
 
BrandonGalderisiCommented:
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.
0
 
chapmandewCommented:
global temp tables (prefixed with ##), go out to scope when ALL connections using them go out of scope
0
 
Greens8301Author Commented:
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

Thanks

0
 
chapmandewCommented:
yes, in that case it would go out to scope (even the global)...why not use a user-table instead?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.