SQL 数据库中已经有一个名为“##Temp”的对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3474053/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
There is already an object named '##Temp' in the database
提问by phoenies
I have a stored procedure on SQL Server 2000. It contains:select ... into ##Temp ...
...
drop table ##Temp
我在 SQL Server 2000 上有一个存储过程。它包含：select ... into ##Temp ...
...
drop table ##Temp
When I run the stored procedure with ADO a second time, it prompts:
There is already an object named '##Temp' in the database.
Could anyone kindly tell me what's wrong?
当我第二次使用 ADO 运行存储过程时，它提示：
数据库中已经有一个名为“##Temp”的对象。
任何人都可以告诉我出了什么问题吗？
回答by Jon Freedman
You should re-write your stored proc to drop the temp table if it exists, then you won't ever have this issue
您应该重新编写存储过程以删除临时表（如果存在），那么您将永远不会遇到此问题
IF (SELECT object_id('TempDB..##Temp')) IS NOT NULL
BEGIN
DROP TABLE ##Temp
END
回答by bobs
You are using a global temp table as indicated by the ## at the beginning of the table name. This means multiple sessions can access the table.
您正在使用全局临时表，如表名开头的 ## 所示。这意味着多个会话可以访问该表。
It's likely that you have a connection open that created the table, but failed to drop it. Are you sure that the first ADO run actually drop the table. Could it have failed, or did the flow control in the procedure skip the drop statement?
您可能打开了一个创建表的连接，但未能删除它。您确定第一次 ADO 运行实际上删除了表。它可能失败了，还是过程中的流控制跳过了 drop 语句？
You may want to test the procedure in SQL Server Enterprise Manager to see if it reports any errors.
您可能希望在 SQL Server 企业管理器中测试该过程以查看它是否报告任何错误。
回答by marc_s
Since you chose to use a global temporary table ##Temp, it is visible to all SQL connections at any given time. Obviously, while the stored proc is running for one connection, a second connection comes in and tries to create yet another ##Tempbut that already exists....
由于您选择使用全局临时表##Temp，因此它在任何给定时间对所有 SQL 连接都是可见的。显然，当存储过程为一个连接运行时，第二个连接进入并尝试创建另一个##Temp但已经存在的连接......
Use connection-local #Temptables (only one #) instead.
改用连接本地#Temp表（只有一个#）。
回答by phoenies
Oh, it's all my fault. I called the SP twice through one connection by mistake.
That's why it always reports error when being called the second time.
Of course you won't know that by reading my description. Sorry guys...
哦，都是我的错。我错误地通过一个连接调用了 SP。
这就是为什么它在第二次调用时总是报告错误的原因。
当然，看了我的描述你不会知道的。对不起大家...
回答by Rayven Visva
For me this solution works :
对我来说，这个解决方案有效：
IF (SELECT object_id ='#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
