468,107 Members | 1,253 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

Handling # (temp) table

Hi,

Handling # table is giving problem.
Does anybody have solution/suggestion for the below given problem.

Create a #TMPTAB table
------------------------
SELECT GETDATE() MYDATE INTO #TMPTAB

Select #TMPTAB Table
-----------------
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
OR
SELECT NAME FROM sysobjects where name ='#TMPTAB'
-----------------------
It does not return the record.
(Because SQL Server create # table with an system generated unique ID
in Name like (#TMPTAB________________________________000000011F 8F))
---------------
But if I select it with Like clause:
SELECT NAME FROM TEMPDB..sysobjects where name ='#TMPTAB'
It return the name:
#TMPTAB________________________________000000011F8 F


When I drop the table it canbe drop with
DROP TABLE #TMPTAB

(EVEN IF I'M USING SOME OTHER DATA BASE 'XDATABASE')


NOW THE PROBLEM IS:
====================
IF I CHECK TABLE WITH LIKE CLAUSE IT WILL RETURN ME ALL # TABLE, WHICH
HAS BEEN CREATED IN TEMPDB.. DATABASE BY DIFFERENT USER/CLIENT).
IN A SCENERIO WHERE A PARTICULAR CONNECTION DOES NOT HAVE MADE ANY #
TABLE, BUT IT HAS MADE FROM SOME ANOTHER INSTANCE.

IF I SEARCH TABLE WITH LIKE CLAUSE (SELECT NAME FROM
TEMPDB..sysobjects where name ='#TMPTAB'), IT WILL RETURN TRUE AND
THEN AT THE TIME OF DROPPING TABLE IT WILL RETURN ERROR.



WHY THIS HAPPEN?


DOES ANYBODY HAVE SOME SOLUTION/SUGGETION ON IT.

THANKS IN ADV.
T.S.NEGI
ti********@mind-infotech.com
Jul 20 '05 #1
1 4353
Use OBJECT_ID to test for the existence of a temp table:

IF OBJECT_ID('tempdb..#tmptab') IS NOT NULL
...

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Josh | last post: by
1 post views Thread by Jim | last post: by
1 post views Thread by Robert McEuen | last post: by
1 post views Thread by serge | last post: by
44 posts views Thread by Kulgan | last post: by
2 posts views Thread by erbrose | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.