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:

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:
  
  CREATE TABLE #Temp (SUPPLIERPRODUCTID int PRIMARY KEY)

  1	CREATE 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!