Query global temp table on a linked server

Question

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 ##.

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.

So my question is there a way that I can reconfigure this command to recognize which tempdb to look at?

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

Answer 1

try this to query global temp table from linked server

SELECT * FROM OPENQUERY(linkedServerName, 'SELECT * FROM ##temp')

Answer 2

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:

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.

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.

Looks like the answer is no, there's no (easy) way.