June 15, 2006 at 6:40 am
Can someone explain something to me.
Several years ago I built an application where temp tables (#) were used. The application worked fine for a long time.
At the time, we were on SQL Server 2000. Since then, we have loaded patches, done upgrades, etc. (currently on 2004). Now, I frequently get "There is already an object named '#[tblname]' in the database".
I know if I change it to create a "normal" table (not temp), check for it's existence in the beginning of the function (drop it if it exists) and Drop Table at the end of the function - all will be fine. However, I fear the overhead and other potential problems.
Does anyone have a recommendation or fix for this "pain"?
Thanks, in advance, for your input.
June 15, 2006 at 8:40 am
Why don't you just check for the existance of the table and drop it if it already exists? That's how I do my scripts that create #temp and ##temp tables.
-SQLBill
June 15, 2006 at 9:17 am
From another member here:
"The name of a temp table is actually a 132 byte name consisting of the user name, the table name used during the CREATE, a wad of underscores, and a unique/constantly changing hexadecimal number; therefore, you can't possibly know the name of the table."
These tables are suppose to drop themselves when the connection is gone.
if exists [#temp_tablename] DROP TABLE will always work, never create an error --- but it will do nothing.
June 16, 2006 at 1:12 am
not sure what the 'real name' of a temp table is....I can see where you're coming from gleazenby, however the code below DOES WORK. Just make sure you check for existence in the tempdb, and not in the current db.
IF object_id('tempdb..#tmp') IS NOT NULL
drop table #tmp
Regards,
Dave McKinney.
June 16, 2006 at 1:31 am
Thinking again about my post above...I guess there could be an issue, if 2 users have concurrently created temp tables with the same name. You could probably end up deleting the wrong table! I used to be uncertain, now I'm not so sure.
Regards,
Dave McKinney.
June 16, 2006 at 6:10 am
But the table names will begin with the owners name:
joesmith.#mytable
janedoe.#mytable
That's one reason it's always a good idea to use the 3-part naming convention (dbname.owner.table)
-SQLBill
June 16, 2006 at 6:46 am
I believe this is the real issue here:
Select @@version on our 2 servers displays the following:
Production Server:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Development Server:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
The issue here is that the code works fine on the Development Server BUT errors out on the Production Server.
If the version/service pack (newest) on the Production Server is the culprit, it might be nice for folks to know this.
The Production Server is the one that has the problem dropping the #temp tables (even if done within a stored procedure) - same code works just fine on the development Server.
I had to change the code so that I generate "real" tables and then drop them "by name" - works fine now, but this temp table issue puzzles me.
Gary
June 16, 2006 at 7:42 am
This works fine for me on SQL 2000 sp3a... everytime:
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb.dbo.#tbl') )
DROP TABLE #tbl
CREATE TABLE #tbl (....)
hth...
Mark
June 16, 2006 at 7:49 am
Now I am dealing with someone who does not understand the value of temp tables (to me). He says eliminate them - they cause problems.
On the same subject - who out there advocates "do not use temp tables"?
June 16, 2006 at 9:53 am
I personally do not use temp tables in most cases. What I normally do is use TABLE variables. As long as you have enough ram on the server, this is normally a more ideal way of doing the same thing because you loose the overhead of writing to disk. Plus, the table variable will fall out of scope and be deleted as soon as the stored procedure or script is completed.
I've used them alot on search queries for an application I'm working on because the database is extremely normalized and the client wants an all inclusive search that has to hit very large tables that would have to be joined to other very large tables (and sometimes joined to yet another very large table). We haven't run into any problems with this method. In fact, we were able to get a SP that was taking 12 seconds using temp tables down to 3 seconds using the variables.
DECLARE @PLACARD_TABLE TABLE(
CUSTOMER_UID FOREIGN_KEY
)
INSERT INTO @PLACARD_TABLE
select CDP.CUSTOMER_UID
from CDP_STOCK AS CDPS
inner JOIN CUSTOMER_DISABILITY_PLACARD CDP
on CDPS.CDP_UID=CDP.CDP_UID
DECLARE @DLN_TABLE TABLE(
CUSTOMER_UID FOREIGN_KEY,
CUSTOMER_LICENSE_NUMBER VARCHAR(50)
)
INSERT INTO @DLN_TABLE
SELECT CUSTOMER_UID,
CUSTOMER_LICENSE_NUMBER
FROM CUSTOMER_LICENSE_NUMBER
SELECT DT.CUSTOMER_UID,
DT.CUSTOMER_LICENSE_NUMBER
FROM @DLN_TABLE AS DT
INNER JOIN @PLACARD_TABLE AS PT
ON PT.CUSTOMER_UID = DT.CUSTOMER_UID
You have to alias table variables to select from them if there is more than one table involved.
June 16, 2006 at 10:49 am
Your development server has a RTM version of SQL Server. That needs to be upgraded to SP3a or 4 right away. SP3a was the first to correct the vulnerability that made SLAMMER worm possible. Plus there are other 'bugs' in the RTM version.
The production version is SP3 (that's what 760 means).
-SQLBill
June 16, 2006 at 10:51 am
You might want to look at Microsoft's website for some of the other patches that came out after SP3. There were problems with that version, which is why they came out with SP3a.
-SQLBill
June 16, 2006 at 12:05 pm
Actually, from Dave McKinney's example from above, I have used it many times...
IF object_id('tempdb..#tmp') IS NOT NULL
drop table #tmp
This works just fine in all cases, even when there are multiple users using the same temp table, even when they are logged in using the same security credentials. This is because the temp table's real name is heavily decorated in tempdb to associate it to a single SPID. This can be seen by opening several windows in Query Analyzer, where each window creates the same #table name. Then run (in a separate window) the following script to view the actual table names:
select name
from tempdb.dbo.sysobjects
where xtype = 'u'
Note that these tables cannot be selected against their actual name in tempdb, even from the originating window.
...Mel
September 27, 2006 at 2:44 pm
Hi gleazenby! It looks like you received a lot of reponses but none from anyone who know's what you are talking about. You are correct! It appears that SQL Server 2000 with Servicepack 4 does have problems clearing temp tables. I spent a whole day trying to find out what I was receiving messages like the #Temp table my code created wouldn't work. I finally changed the table name to #Temp1 instead and everything worked like a charm. Actually this issue has resurfaced again in another routine so I renamed it to #Temp1 which didn't help. Then I renamed it #Temp123 and all is fine again. I am still suspicious of some type of corruption going on but I have done maintenance on the DB which did not report any problems with it. This is occuring in a Db specifically used for DBA procedures and functions. It's main purpose is not for data although there is a bit of data in it. I haven;t seen any posts on google specifically addressing this but then again it wouldn't be the first time I found issues with SQL Server 2000 that the MS techs could not explain. There are a number of things such as putting comments in a where clause that will make the query return bad data or not sort it as it should. Heck they can't even seem to get the If then construct correct.
Here's another one! The first bit of code won't execute the IF logic correctly.
IF @CheckForDirectory = 1
IF Exists(SELECT 1 FROM #temp2 WHERE FileisaDirectory2 = @CheckForDirectory)
SET @retval = 0
ELSE
IF Exists(SELECT 1 FROM #temp2 WHERE FileExists2 = 1 AND FileisaDirectory2 = 0)
SET @retval = 0
You have to code the begin and end statements for it to work.
IF @CheckForDirectory = 1
BEGIN
IF Exists(SELECT 1 FROM #temp2 WHERE FileisaDirectory2 = @CheckForDirectory)
SET @retval = 0
END
ELSE
BEGIN
IF Exists(SELECT 1 FROM #temp2 WHERE FileExists2 = 1 AND FileisaDirectory2 = 0)
SET @retval = 0
END
Sorry but I don't have any solutions. Some have suggested not to use temp tables but I suspect they are not heavy SQL Server users or have been writing extended procedures in another language to perform file system functions. Good luck!
September 30, 2006 at 2:45 am
I would definitely recommend upgrading your dev server to more current bits. In general, your dev environment should mirror your production environment as closely as possible (hardware as well as software).
To your question regarding when to use temp tables.. I blogged on that topic here; hope it helps!
- Ward Pond
blogs.technet.com/wardpond
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply