And if you are still reading you probably fit into one of the following categories.

• Not a DBA
• A relatively new DBA
• Like to know how things work even if you know you should never do this

Everyone else left because .. well .. trying to query a temp table across a linked server.

Ok, let me start by saying, this can not be done. Period.

Demo first, and then there are a few workarounds.
tl;dr;Use a regular table or OPENQUERY.

-- Run on Server1
SELECT database_id, name INTO ##DBList FROM sys.databases;

-- Run on Server2 with a linked server back to Server1
SELECT * FROM Server1.tempdb.dbo.##DBList;

Database name ‘tempdb’ ignored, referencing object in tempdb.

Database name ‘tempdb’ ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 1

Invalid object name ‘##DBList’.

So a few things here. I’m using a global temp table instead of a local one because it makes it easier to reference. Local temp tables aren’t listed in tempdb under their name while global ones are.

The first part of this message (the bit in black) is a warning basically telling us that if there is a temp table (a # at the front of the name) it’s going to ignore the multi part reference. In other words, you’re going to get this message any time you try to use a multi part name and a temp table. Linked server or not. The second part of the message (the bit in red) just tells us that there isn’t a temp table named ##DBList.

Ok, fine. Let’s run one more test but creating a temp table on Server2 just to make sure what we think is happening is really what’s happening.

-- Run on Server2
SELECT name INTO ##DBList FROM sys.databases
WHERE database_id < 5;

Just to point this out, this version of the temp table has one less column (name only instead of database_id, name) and only has 4 rows (the system databases as it happens) and not however many there were on Server1.

For this test I’m going to make up a database name because I want to see if it completely ignores the DB name or if the parser actually checks it before ignoring it. Next, the output we expect is going to be 4 rows with just the name column. This is the data in the local copy of the temp table, not the much larger data in the copy of the temp table on Server1.

-- Run on Server2
SELECT * FROM Server1.BuckWoody.dbo.##DBList

Database name ‘BuckWoody’ ignored, referencing object in tempdb.

And we get a message saying the database BuckWoody is ignored (I don’t even have a database with that name) and the data we expected!

Ok, so workarounds. Easy workaround: you can always create a regular table. You can even create it in tempdb if you want. Upside, your code works with minimal changes. Downside, you have to make sure you remove the table from tempdb manually, it’s not going to go away on it’s own.

The better workaround IMO is to use OPENQUERY. OPENQUERY is a function that lets you run a query across a linked server.

-- Run on Server2
SELECT * FROM OPENQUERY([Server1], 'SELECT * FROM ##DBList');

Yay! This time the output we get is the remote temp table. I’m not going to say anything about the performance here because honestly, performance on linked servers is frequently problematic. But there you go. Now, if you ever happen to try to combine a temp table and a linked server you know why it won’t work, and how to work around it.

For completeness there are a couple of similar functions (OPENROWSET and OPENDATASOURCE) that will let you access remote data as well. I haven’t tested these with temp tables but I would think that OPENROWSET should work with them but OPENDATASOURCE probably won’t.