June 6, 2022 at 1:20 pm
Hi all, Really quick one I hope.
I have a stored procedure as part of an ETL process that drops the global temp table and SELECT * INTO ##Global_temp_table.
we are hitting issues with the error message ##Global_temp_table already exists (so you can't instantiate it with a SELECT...INTO)
We are running both QA and UAT databases on the same server (don't ask...) and both updates are triggered from a SQL Agent jobs which are running on the same schedule. Could running both ETLs concurrently cause the Global TT to be overwritten from the other database, or is each ##Global table idempotent to its executing database. We don't hit the issue every time, it is intermittent....
June 6, 2022 at 1:42 pm
As far as I know, a global temp table remains in scope as long as the SPID which created it remains active. So you are likely to get conflicts if you have more than one process active at the same time which is attempting to create a global temp table with the same name as another.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2022 at 1:46 pm
you can't have 2 processes running concurrently that try to drop/create the same global temp table - you get the conflict you have.
either change it so that the 2 processes use different global temp tables (mostly a requirement if you are using SSIS) or change to normal temp tables or even to permanent tables on a different schema.
and the above raises the question - why exactly are you using global temp tables?
June 6, 2022 at 2:00 pm
Global is global. So, yes they could certainly conflict & return bogus data.
I'd recommend using local temp tables instead of global temp tables, or staging tables within the appropriate databases.
I don't know that I've ever found a good use case for global temp tables (not saying I never tried them). Cases where persistence and visibility outside the session seem like a better case for permanent staging tables using truncate and intentionally specified user access.
June 6, 2022 at 2:17 pm
In response to both frederico_fonseca and ratbak
I am using global temp tables because I need to persist the data across SPIDs. The table in question is built from some highly complex query (i.e. poorly designed initial database entities) and forms the basis for a number of different extracts. Each of these extracts firstly calls the sproc to destroy and rebuild the temp table to make sure the data is fresh. As each execute occurs in a different SPID they need to be ##Global
What I want though is 'Global to the executing database context' rather than 'Global to the server instance' but I guess this is not an option.
This is a good case for global temp tables, Another one I came across was if you need to do something on each row of a results set (e.g. send an email via db_sendmail) then using a ##Global to generate the email list and then hand off processing to a different stored procedure was quite an effective solution.
Thanks for the quick replies
Aaron
June 6, 2022 at 2:21 pm
Can you explain why the global temp table is a better option than a permanent staging table? That's not clear from your explanation.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2022 at 2:55 pm
You can reuse local temp tables from one stored procedure in a procedure called by the proc that created the temp table, because they are in the same session.
e.g., get the results in one proc, and if there are any, then call the proc that actually generates/sends the email.
June 6, 2022 at 3:53 pm
In response to both frederico_fonseca and ratbak
I am using global temp tables because I need to persist the data across SPIDs. The table in question is built from some highly complex query (i.e. poorly designed initial database entities) and forms the basis for a number of different extracts. Each of these extracts firstly calls the sproc to destroy and rebuild the temp table to make sure the data is fresh. As each execute occurs in a different SPID they need to be ##Global
What I want though is 'Global to the executing database context' rather than 'Global to the server instance' but I guess this is not an option.
This is a good case for global temp tables, Another one I came across was if you need to do something on each row of a results set (e.g. send an email via db_sendmail) then using a ##Global to generate the email list and then hand off processing to a different stored procedure was quite an effective solution.
Thanks for the quick replies
Aaron
for the case you mention here then a global table is not needed at all.
proc A - main process
define #temp table
call proc B to populate #temp
process contents of #temp table
or on the case of the email bit
proc A
- get list of emails onto #temp table
- call proc B to process email list from #temp table
none of the 2 cases above requires the use of a global temp table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply