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.

Apparently, since your question is marked as a duplicate, I cannot add an answer, but the simple answer is that you are trying to drop a session-local temp table (one #-prefix, e.g. #tmp), as opposed to a global temp table (two #-prefix, e.g. ##tmp), and these are two completely different things. — John Zabroski, Dec 28 '15 at 15:40

score 2 · Answer 1 · answered Oct 31 '14 at 20:27

2

Change your temp table drop query to this

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

answered Oct 31 '14 at 20:27

gunvant.k

1,096
1
10
15

score 2 · Accepted Answer · answered Oct 31 '14 at 20:27

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

score 0 · Answer 3 · edited Nov 01 '14 at 01:16

0

I also found another approach that works by looking in the same tempdb location.

.CommandText = "IF EXISTS (SELECT * FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '" & TEMPTABLE & "%') DROP TABLE " & TEMPTABLE
.Execute

edited Nov 01 '14 at 01:16

Luís Cruz

14,780
16
68
100

answered Oct 31 '14 at 20:33

CBRF23

1,340
1
16
44

2

Yeah, this one is interesting because of the wildcard there. Temp tables like this are session specific. Another user might come along and create a temp table with a name like yours, and SQL avoids a name collision by appending a whole bunch of other junk that keeps the two tables unique and qualifies them a bit. It's imaginable that this query could return an id for a table similar to yours but created in a different session. It is crazy unlikely, but it will be damn near impossible to figure out what happened if it ever does. – Ryan B. Oct 31 '14 at 20:40

Temporary table will not drop

3 Answers3

Linked