|
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?
(comments are locked)
|
|
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] * - 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
(comments are locked)
|
|
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.
(comments are locked)
|
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.