Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcuros
Starting Member
1 Post |
 Posted - 2011-07-01 : 06:40:30
|
| HiI have a problem on our production server. Every few weeks #temptable stops working. I get the following error : "Invalid object name '#temptable'."Temporary table is used inside a stored procedure that gets called about 100000 times a day. Most of the time it works flawlessly, but every once in a while I get the above mentioned error. When this happens only the instance restart helps.The stored procedure has the following logic:CREATE TABLE #temptable ( ClientId int IDENTITY PRIMARY KEY, idA int )INSERT INTO #temptable ( idA ) SELECT idA FROM a WHERE <some_conditions>SELECT * FROM #temptable INNER JOIN b ON <join_predicate>The thing is that I can't reproduce this and I can't find out why this happens. If I fetch the query from the SQL Profiler and run it in Management Studio it works fine. But in the same time it doesn't on web page.After the restart it works fine.Server is MS SQL Server 2005 64bit. Version 9.00.5000.00 (SP4).For tempdb I have 4 data files. Each size of 5GB and autogrow by 10%.Server works on:Windows server 2003 R2Standard x64 EditionService Pack 2Intel Xeon CPU E5420 @ 2.50GHz12GB RAMI have searched a lot of forums, blogs.. but I haven't found anything that could help me..Does anyone know what could be wrong? Or where should I look for error? |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-07-01 : 07:20:56
|
| umn. What are you *actually* using the temp table for? Getting a rank / row_number? If that's all then *STOP DOING THAT*Look at the windowing function ROW_NUMBER () (OVER.....) instead.I can't see any reason for using the temp table in your code.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-01 : 07:55:42
|
| Is something creating a temp table with the same name on a connection then calling the sp?It sounds like an issue with the connection not being reset properly and the temp table persisting a bit - the object id for the connection being found when it's not really there.Can you use a table variable instead.Needing an instance restart implies that there is an issue with a connection being held from iis - i.e. sql server not clearing it down properly. Maybe it's something to do with the connection pool?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Invalid object name '#temptable'