| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
ScottMitchell
Starting Member
USA
6 Posts |
|
|
ScottMitchell
Starting Member
USA
6 Posts |
|
|
sqlguru
SQLTeam CoFounder
USA
20 Posts |
|
|
perlpunk
Starting Member
USA
2 Posts |
 Posted - 02/18/2005 : 13:57:51
|
Long story short I needed to find out if a temp table exists because Classic ASP is a pile of crap, so here is really the only way I found.
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
Thanks for sql-server-performance.com for that answer.
-Hap
------
Digital Propulsion Labs
www.digitalpropulsionlabs.com
Denver, CO |
 |
|
|
ThomasK67
Starting Member
5 Posts |
Posted - 02/09/2007 : 10:48:29
|
This is exactly what I needed to know. Thanks!
I've never worked with Classic ASP, but ASP.NET is very easy to pick up, especially if you know object-oriented programming. Really really easy if you know C++ and you're going to be doing ASP.NET in C#.
Thanks!
Thomas |
|
|
|
ThomasK67
Starting Member
5 Posts |
Posted - 02/09/2007 : 11:26:48
|
... except I get the message
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
I'm talking to my dba about it, I'm sure it's something he can fix fairly easily |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
|
|
ThomasK67
Starting Member
5 Posts |
Posted - 02/09/2007 : 11:49:08
|
Thankya for the info
My dba sent me this:
if exists (select * from tempdb.dbo.sysobjects where name like '#THETEMPTABLE%')
DROP TABLE #THETEMPTABLE
GO
works groovy
thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 02/09/2007 : 12:38:00
|
quote:
Originally posted by ThomasK67
Thankya for the info
My dba sent me this:
if exists (select * from tempdb.dbo.sysobjects where name like '#THETEMPTABLE%')
DROP TABLE #THETEMPTABLE
GO
works groovy
thanks!
One problem with that code is that is does not check to make sure it is a temp table, and not some other type of temporary object, like a procedure or function.
Another problem is that it does not look for an exact match, so it may find another table with a longer name.
The code from the F_TEMP_TABLE_EXISTS function that Harsh Athalye posted a link to does not have those problems, so this would be a better way to do it.
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id( N'tempdb..#THETEMPTABLE' )
)
drop table #THETEMPTABLE
CODO ERGO SUM |
|
|
|
ThomasK67
Starting Member
5 Posts |
Posted - 02/09/2007 : 17:12:46
|
Hey, thanks for the info... I'll look at it.
Points taken, though in this specific case neither would be a problem. However, specificity in coding can prevent future problems, so I'll try your method.
Thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 02/09/2007 : 18:14:02
|
quote:
Originally posted by ThomasK67
Hey, thanks for the info... I'll look at it.
Points taken, though in this specific case neither would be a problem. However, specificity in coding can prevent future problems, so I'll try your method.
Thanks!
That is not the only possible problem of the other code. If would also find a temp table with the same name created on another connection, and get an error on the drop statement.
CODO ERGO SUM |
|
|
|
ThomasK67
Starting Member
5 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 02/09/2007 : 18:41:58
|
quote:
Originally posted by ThomasK67
Hmm, interesting. I had understood otherwise from this page:
http://www.programmers-corner.com/article/76
Thanks for the input!
There is nothing in that article that addresses the problem I described with the code you posted from your DBA.
1. Open two Query Analyzer windows connected to the same server.
2. Execute this code in Windows 1:
create table #myTemp ( P int )
3. Execute this code in Window 2:
if exists(select * from tempdb.dbo.sysobjects where name like '#myTemp%')
drop table #myTemp
You will get this error:
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#myTemp', because it does not exist in the system catalog.
CODO ERGO SUM |
|
|
| |
Topic |
|
Article: Using the Information Schema Views
