I have seen all of the references on how to query two different tables on two different SQL servers, and I understand how it is to be implemented. However, the command doesn't seem work with temporary tables created with the ##.
我已经看到了关于如何在两个不同的SQL服务器上查询两个不同表的所有引用，我理解它是如何实现的。但是，该命令似乎不适用于使用##创建的临时表。
If I write a join on one server, and it references one temp table on that server, and one temp table on the other server, SQL Server assumes that because the ## is in the command, it automatically looks at the local server's tempdb, not the remote one. I also cannot use OPENROWSET at this time because the feature has been disabled and I have to get approval to turn it back on.
如果我在一台服务器上编写一个连接，并且它在该服务器上引用一个临时表，在另一台服务器上引用一个临时表，则SQL Server假定因为##在命令中，它会自动查看本地服务器的tempdb，不是遥远的。我此时也无法使用OPENROWSET，因为该功能已被禁用，我必须获得批准才能重新启用。
So my question is there a way that I can reconfigure this command to recognize which tempdb to look at?
所以我的问题是有一种方法可以重新配置此命令以识别要查看的tempdb吗？
SELECT *
FROM (##mytemptable1 Demog
INNER JOIN MyServer.tempdb.dbo.##mytemptable2 PeakInfo ON (Demog.SAMPLE_NO = PeakInfo.SampleNum) AND (Demog.JOB_NO = PeakInfo.JobNum) )
ORDER BY PeakInfo.JobNum, PeakInfo.SampleNum, PeakInfo.Replicate ,PeakInfo.Reinjection ,PeakInfo.PeakNameCustSort
2 个解决方案
#1
16
try this to query global temp table from linked server
尝试此操作从链接服务器查询全局临时表
SELECT * FROM OPENQUERY(linkedServerName, 'SELECT * FROM ##temp')
#2
1
MSDN http://msdn.microsoft.com/en-us/library/ms186986(v=sql.105).aspx says that the global temp tables are visible only on the specific instance of SQL Server:
MSDN http://msdn.microsoft.com/en-us/library/ms186986(v=sql.105).aspx表示全局临时表仅在SQL Server的特定实例上可见：
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.
全局临时表在创建后对任何用户和任何连接都可见，并且在引用该表的所有用户与SQL Server实例断开连接时将被删除。
Also, any action attempted on the remote global temp table results in a clear error message:
此外，在远程全局临时表上尝试的任何操作都会产生明确的错误消息：
SELECT * FROM LinkedServerName.TempDB.dbo.##GLOBTABLE
Database name 'TempDB' ignored, referencing object in tempdb.
忽略数据库名称“TempDB”，引用tempdb中的对象。
Looks like the answer is no, there's no (easy) way.
看起来答案是否定的，没有（简单的）方法。
#1
16
try this to query global temp table from linked server
尝试此操作从链接服务器查询全局临时表
SELECT * FROM OPENQUERY(linkedServerName, 'SELECT * FROM ##temp')
#2
1
MSDN http://msdn.microsoft.com/en-us/library/ms186986(v=sql.105).aspx says that the global temp tables are visible only on the specific instance of SQL Server:
MSDN http://msdn.microsoft.com/en-us/library/ms186986(v=sql.105).aspx表示全局临时表仅在SQL Server的特定实例上可见：
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.
全局临时表在创建后对任何用户和任何连接都可见，并且在引用该表的所有用户与SQL Server实例断开连接时将被删除。
Also, any action attempted on the remote global temp table results in a clear error message:
此外，在远程全局临时表上尝试的任何操作都会产生明确的错误消息：
SELECT * FROM LinkedServerName.TempDB.dbo.##GLOBTABLE
Database name 'TempDB' ignored, referencing object in tempdb.
忽略数据库名称“TempDB”，引用tempdb中的对象。
Looks like the answer is no, there's no (easy) way.
看起来答案是否定的，没有（简单的）方法。