I want to create a global temp table in a SQL Server Agent Job.

Here is the code:

CREATE TABLE ##TestEmployee(Id int, EmployeeName nvarchar(50))

When I execute in SSMS, all is well.

I then put the above code on a SQL Agent Job. The job will complete successfully, but when I try doing a select statement in SSMS, I get an Invalid Object error.

SELECT * FROM ##TestEmployee

Are there any gotchas I am missing by creating a global temp table in SQL Server Agent?

  • Why are you using a global ##temp table instead of a permanent table? Where exactly are you trying to access the table, and is it after the job has finished? – Aaron Bertrand May 23 '15 at 14:15
  • I want use a global ##temp table in an SSIS package, to assist in some set based updates. Job step 1 create ##temp table if it does not exist. Job step 2 execute SSIS package. Job step 3 truncate or drop ##temp table. – Mike Henderson May 23 '15 at 16:21
  • You could do that with a permanent table too. – Aaron Bertrand May 23 '15 at 18:22
  • In the end a permanent table is where I'll go. I do that for some daily ETL processing. I am exploring the pros and cons of a temp instead. – Mike Henderson May 23 '15 at 18:56

The difference between global and local temp tables isn't how long they exist, it's who can see them.

Local temp tables can only be seen by the session (spid) that created them. Global temp tables can be seen by everyone, but only while the session that created them is active.

If you are running it in a SQL Agent job, as soon as it completes it disappears. That's why you can't access it, because it is no longer there.

  • My intent is a global ##temp table, instead of a local #temp table. I want to do a select from the ##temp table in subsequent job steps. – Mike Henderson May 23 '15 at 16:23
  • I see the error of my ways. I was mistaken in thinking a global ##temp table would exist beyond the life of the session that created it. – Mike Henderson May 23 '15 at 16:31

Understanding global temporary table visibility is essential when using these objects, especially when the global temporary table is used from different sessions.

  • You're not actually responding the question of the original poster. Being more concrete would help him/her better. If you just need to add a comment, add a comment instead of an answer. – joanolo Jan 16 '17 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.