Issue reusing global temp table name in separate SQL jobs

I have 2 SQL jobs that reuse the same global temp table names. The jobs run just fine when executed independently. The issue arises when I trigger JOB#1 to trigger Job #2 to run. The stored procedures in both job include code to check if the global temp tables exists and to drop it if it does.

I don't understand why I can't have the same name for global temps tables when the procedures are executed in separate jobs?

more ▼

asked May 01, 2012 at 09:08 PM in Default

avatar image

0 1 1 1

Thanks for your response. The reason why I'm using global temp tables is because I dynamically pass in paramaters.

Here is a simplified version of my issue.

  1. Job #1 runs PROC #1 which creates and drop table globalTemp1

  2. Job #1 triggers Job#2 to run after Job #1 completes

  3. Job #2 runs PROC #2 which creates and drop table globalTemp1

May 02, 2012 at 01:17 PM dbuck2135
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If Job#1 creates and uses ##TableA it has an "ownership lock"* on it. If it then starts Job#2 then the table @TableA cannot be dropped or created by Job#2. Job#2 should be able to Ins/Upd/Del data in the table but wont be able to issue and DDL commands.

Some questions to consider: Why dont you use a normal table? Will truncate work as well as DROP/CREATE? Why use the same table?

[Edit - after your extra info in your comment]
Job#1 doesnt finish and then Job#2 start. Job#1 doesn't finish until Job#2 finishes and for want of a better phrase "hands back" to Job#1. Job#1 is waiting while Job#2 runs.

* - my term. Simply to mean that the SPID being used by Job#1 is the only SPID that can issue DROP/ALTER commands on ##Table1

more ▼

answered May 02, 2012 at 08:31 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

The whole idea with a global temporary table is to create one that is accessible from multiple different processes. If you don't want to access this from different processes, then don't use the global temp table. Conversely if you need to have two different globally accessed tables in existence at the same time, you should use different names.

Think of it like this, a global temp table is creating a table for the server in the same way as you create a table for a database. You can't create the same table in the same schema on a database right? Same issue here.

more ▼

answered May 02, 2012 at 10:28 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 01, 2012 at 09:08 PM

Seen: 1203 times

Last Updated: May 02, 2012 at 01:22 PM

Copyright 2018 Redgate Software. Privacy Policy