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
tilak.negi@mind-infotech.com 
