Sql-server – Global Temp Table Error when Stored Proc run by SQL Job on a Schedule

sql-server-2017sql-server-agenttemporary-tables

I have a puzzling situation I am trying to confirm/solve.

A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This all works well. It has been tested over and over, so I am convinced that there is nothing amiss in the way the tables are being instantiated or accessed.

The stored procedure is then kicked off by a Job using the SQL Job Agent. This also works fine as long as I start the SQL Job manually.

But strangely, if the SQL Job is put on a schedule (say it runs every hour on the hour), the last Global Temp Table set to be created sometimes can't be found and the job sometimes fails while sending back an error:

Server Error Message = Invalid object name '##GlobalTempTableName'

But it doesn't fail every time. It might fail twice and the succeed the next time only to begin failing again the next hour. In addition, I have created a workaround to force the scheduled Job to succeed by setting it to make two more attempts before giving up. This solution has satisfied the stakeholders, because all they care about is that the SQL Job runs, they don't care HOW it runs.

But I am a very curious person and I would really like to know why this might be occurring.

Thanks for the geeky answers in advance.

Lars

Best Answer

With global Temp Tables, when the session that created it no longer exists and no other T-SQL statements are actively referencing the table, it gets dropped automatically.

Per Microsoft's Create Table docs:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

This is only a guess, but perhaps whatever session that is creating the global temp table and code that is actively referencing it both stop before the Agent Job gets a chance to access it? (E.g. if you're using multiple steps in the Agent job, and it's a subsequent step that tries to access the global temp table.) That would make the difference from you just executing the whole thing in a new query window in one go.

Related Question