问题描述
根据Microsoft的定义，全局临时表对任何用户以及创建后的任何连接都是可见的，并且当所有引用该表的用户与SQL Server实例断开连接时，这些临时表将被删除."
According to Microsoft's definition, "Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server."
为什么可以进行以下工作:
Why is it that the following work:
使用SSMS创建全局临时表(## SomeTempTableName)，并在几秒钟或几分钟后使用SSMS运行单独的查询，第二个查询可以查看和使用临时表.
Create a global temp table (##SomeTempTableName) using SSMS and run a separate query using SSMS a few seconds or minutes later and the second query can see and use the temp table.
使用ADO创建全局临时表，并在几秒钟或几分钟后运行一个单独的ADO查询，第二个查询可以查看并使用临时表.
Create a global temp table using ADO and run a separate ADO query a few seconds or minutes later and the second query can see and use the temp table.
使用Access传递查询创建全局临时表，并在几秒钟或几分钟后运行一个单独的Access传递查询，第二个查询可以查看和使用临时表.
Create a global temp table using an Access pass-through query and run a separate Access pass-through query a few seconds or minutes later and the second query can see and use the temp table.
但是，如果我在SSMS，ADO或通过Access传递查询创建了一个全局临时表，那么他们中谁都看不到任何其他方法创建的临时表?我刚刚使用SSMS可以看到的Access传递查询创建了一个临时表.
But if I create a global temp table in SSMS, ADO or via an Access pass-through query, none of them can see a temp table created by any other method? I just created a temp table using an Access pass-through query that SSMS could see.
请注意，我没有关闭SSMS，没有关闭ADO连接或没有关闭任何这些事件之间的Access.
Note that I am not closing SSMS, closing the ADO connection or closing Access between any of these events.
推荐答案
参考标题问题...
...我的测试表明答案是是".这是我所做的:
...my testing shows that the answer is "yes". Here's what I did:
重新启动SQL Server计算机后，我以管理员身份登录Windows，然后通过SSMS(Windows身份验证)本地登录到SQL Server.在SSMS中，我运行了脚本...
After restarting the SQL Server machine I logged in to Windows as Administrator and then logged in to SQL Server locally via SSMS (Windows Authentication). In SSMS I ran the script...
USE [Accounting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE ##gord(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
...创建全局临时表，然后运行脚本...
...to create the global temp table, then I ran the script...
INSERT INTO ##gord ([Name]) VALUES ('bar')
...给它一行.然后，在SSMS仍在运行的情况下，我启动了Access并运行了传递查询...
...to give it a row. Then, with SSMS still running I launched Access and ran the pass-through query...
SELECT * FROM ##gord
...，它从全局临时表中返回该行.只要SSMS正在运行，我就可以继续运行传递查询，但是一旦我关闭SSMS，下一次尝试运行Access传递查询就会导致
...and it returned the row from the global temp table. I could keep running the pass-through query as long as SSMS was running, but as soon as I shut down SSMS the next attempt to run the Access pass-through query resulted in
[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]无效的对象名称"## gord". (#208)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##gord'. (#208)
在SSMS和Access会话中，我都以管理员身份(服务器角色:sysadmin)连接到SQL Server，所以我想知道您是否可能遇到SQL Server权限问题，该问题阻止其他会话看到全局临时表.
In both the SSMS and the Access sessions I was connecting to the SQL Server as an Administrator (Server Role: sysadmin) so I'm wondering if perhaps you may have a SQL Server permissions issue that is preventing other sessions from seeing the global temp table.
这篇关于Access传递查询能否查看使用ADO和/或SSMS创建的SQL Server上的全局临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！