Using OPENQUERY (exec stored procedure) to create new temporary table fails with error 11526

Try this:

SELECT *
FROM OPENQUERY("FOO\SQL2012", 'SET FMTONLY OFF; EXEC mySchema.myStoredProc;') X;

The reason for this is that when you execute a stored procedure across a linked server, the provider first tries to determine the shape of the resulting rowset. It does this by issuing SET FMTONLY ON; and then running your statement. In a stored procedure that doesn't use temp tables, this works beautifully. The query parser basically does a dry run without actually fetching all the data, just the metadata (sort of like showing an estimated execution plan).

The problem is that when the stored procedure does use temp tables, it fails, because the temp table's metadata doesn't exist: it can't be collected through the meta-analysis that works for stored procedures that don't use temp tables. The cure, then, is to manually SET FMTONLY OFF; within the batch that is executing the stored procedure.

Be aware that using this method will make the stored procedure run twice. The first time to collect the metadata (the data being discarded), and the second time to actually return the data. If the called stored procedure is particularly costly or has side-effects, you may need to make allowances.

Finally, note that this trick doesn't work on every stored procedure. There are things stored procedures can do that just throw a wrench in the works. I don't know all the possibilities, but one of them is returning multiple recordsets.

In response to your update that SET FMTONLY OFF doesn't work: can you possibly restructure your SP to not use a temp table, or to use a session-keyed permanent table? Either of these options could do the job. In SQL Server 2012, you also have the option of passing around data with table-valued parameters.

You might like to read Erland Sommarskog's How to Share Data between Stored Procedures as it might provide you with inspiration for a way to accomplish your purpose.

Adding "WITH RESULT SETS [NONE | UNDEFINED]" to the end of the EXEC call should correct this issue. http://technet.microsoft.com/en-us/library/ms188332.aspx