I have received following questions numerous times:
“How to check existence of Temp Table in SQL Server Database?”
“How to drop Temp Table from TempDB?”
“When I try to drop Temp Table I get following error. Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database. How can I fix it?”
“Can we have only one Temp Table or we can have multiple Temp Table?”
“I have SP using Temp Table, when it will run simultaneously, will it overwrite data of temp table?”
In fact I have already answer this question earlier in one of my blog post. I have even explained how Temp Table works in TempDB and how they are managed.
Visit my earlier written article for answers to questions listed above.
SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Below is how you can check and delete temp tables.
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N’tempdb..#MyTempTable’)
)
BEGIN
DROP TABLE #MyTempTable
END
i tired using it
i get sysntax error
Incorrect syntax near ‘)’
“i tired using it
i get sysntax error
Incorrect syntax near ‘)’ ” – SVK
I suspect that this is because the character ’ (single quote) is invalid, it should be ‘ (apostrophe) instead.
What I suspect has happened is that either the database for this website is converting apostrophes to single quote or the developer of the query pasted their query into MS Word which then assumed that the character should be a single quote and converted it.
Solution: Try replacing all single quote characters to apostrophes. I did that and it works fine for me.