Search code examples
sql-server-2008excelsql-dropvba

Excel VBA - exec script to drop temp table

I want to drop a # temp table when running then macro, is it correct to use:

tempdb..sysobjects

The script is:

sScript = sScript + "IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE xtype = 'U' AND name like '#vl_enrolledByDate%') " & vbCrLf
sScript = sScript + "BEGIN " & vbCrLf
sScript = sScript + "DROP TABLE  #vl_enrolledByDate " & vbCrLf
sScript = sScript + "End " & vbCrLf

iVal = execute_sql_command(sServer, sDatabase, sScript)

The sServer eg. is SQLPROD01 and sDatabase eg. is scratchdb, but I read that #tables are stored in tempdb database, is this the reason why the table does not exist?

So when I run the macro it returns the following error message:

Run-time error '-2147217865 (80040e37)':

Cannot drop the table '#vl_enrolledByDate', because it does not exist
in the system cataglog.

But if I create a non # table and just use

(SELECT * FROM sysobjects WHERE xtype = 'U' AND name like 'vl_enrolledByDate%')

it's fine.

Solution

  • I always conditionally drop temp tables like this:

    if OBJECT_ID('tempdb..#temp') is not null
drop table #temp