Is it possible to create a temp table on a remote server via openquery? I have several queries that currently query a remote server through a linked server connection and 4 part naming that utilize temp tables. In an attempt to improve performance I'd like to convert these to use openquery. Many of these queries use temp tables on the remote server and I'm getting an error trying to do the same with openquery.
4 part naming works
exec LINKEDSERVER.dbName.dbo.sp_executesql @SQL=N'CREATE TABLE #tmp (ID INT);
Openquery does not
exec sp_executesql @SQL=N'SELECT *
FROM OPENQUERY(LINKEDSERVER, ''CREATE TABLE #tmp (ID INT);
Returns an error message:
"Deferred prepare could not be completed.".