Temporary table will not drop

Question

The conditional drop table line in the code below doesn't seem to work. It executes without error, but then the next line errors saying the object ID already exists - so it's not actually dropping the table.

Any ideas?

I'm connecting to an MS SQL Server 2005 database through OLE DB (SQLOLEDB provider) using ADO.

'Create a temporary table on the SQL Server and insert into it the info for the end part
With ADOcmA
    Set .ActiveConnection = mObjCON
    .CommandType = adCmdText
    .CommandText = "IF OBJECT_ID('" & TEMPTABLE & "') IS NOT NULL DROP TABLE " & TEMPTABLE
    .Execute
    .CommandText = "CREATE TABLE " & TEMPTABLE & " (ITEM VARCHAR(255),DESCRIP1 CHAR(255),DESCRIP2 CHAR(255), LEV INT, SEQ VARCHAR(255), FLAG1 TINYINT, PRIMARYKEY INT IDENTITY(1,1) PRIMARY KEY,QTY_PER FLOAT)"
    .Execute
    .CommandText = "Insert Into " & TEMPTABLE & " (ITEM,DESCRIP1,DESCRIP2,LEV,SEQ,FLAG1,QTY_PER) select item_no,Item_desc_1,Item_desc_2,1,1,'1',1 FROM " & cstrMACtItem & " WHERE Item_no ='" & strITEM & "' "
    .Execute
End With

FWIW the temptable name is created at runtime using the format #[WSID]TEMP[NOW] and truncated to 116 characters, so for example:TEMPTABLE=#LOKSPEC1TEMP141031155408

Also, I've tried escaping the table name using single quotes or double quotes but always same result (it doesn't drop). I found square brackets raise a syntax error.

Answer 1

Sql creates tables prefaced with the pound sign in a whole other place than your current working database. So if you want to interrogate schema information of a temp table, then you need to explicitly reference that other database. It might be easier to see if you tweaked your code like this

.CommandText = "IF OBJECT_ID('tempdb.." & TEMPTABLE & "') IS NOT NULL DROP TABLE " & TEMPTABLE