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:
  • 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!