I was discussing this with a colleague yesterday and thought everyone might be interested in the outcome.
Problem:
I was getting an error “There is already an object named ‘PK_SUPPLIERPRODUCTID’ in the database.” when trying to create a temp table.
Scenario:
- A temp table (‘#Temp’ & Thread()) was created initially in a window procedure on thread 2.
- That window was then closed and later opened on a different thread number e.g. 3.
- When the CREATE TABLE is called the second time I was getting the above error.
Original SQL:
1 |
CREATE TABLE #Temp (SUPPLIERPRODUCTID int NOT NULL CONSTRAINT PK_SUPPLIERPRODUCTID PRIMARY KEY) |
Gotcha’s:
- Normally the details of a temp table are “session” dependent. It seems that because I was explicitly naming the primary key (PK_SUPPLIERPRODUCTID) this was somehow bypassing the way temp tables are assigned session unique names.
- Scope of SQL temp tables:
- “All other local temporary tables are dropped automatically at the end of the current session.”
- I had always thought that a “session” would mean the Clarion Thread connection.
- It kinda does and kinda doesn’t.
- Temp tables are assigned uniqueness based on the clarion thread connection but they are not removed from the database when the clarion thread closes.
- They are only actually dropped from the SQL tempdb when the clarion application closes!
Solutions:
- Don’t explicitly name the primary key! This works without error:
1CREATE TABLE #Temp (SUPPLIERPRODUCTID int PRIMARY KEY) - If you do a “CREATE TABLE” then try and make sure to do a “DROP TABLE” once you are done (perhaps as well as before the create?).
- This has the added benefit of keeping the tempdb cleaner.
- I guess this is just basic house keeping like in clarion when you must always New/Dispose.
Disclaimer: I don’t claim to be an SQL expert, these are my observations and my solutions. I would love to hear any comments!