Create temp table in Openquery?

Question

Post reply

Create temp table in Openquery?

Durendal

SSC Enthusiast

Points: 184
More actions
January 14, 2020 at 3:40 pm

#3715270
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); 

SELECT *
FROM #tmp'
```
Openquery does not
```
exec sp_executesql @SQL=N'SELECT *
FROM OPENQUERY(LINKEDSERVER, ''CREATE TABLE #tmp (ID INT);  

SELECT *
FROM #tmp'')'
```
Returns an error message:
"Deferred prepare could not be completed.".

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

I don't see how OPENQUERY would provide any performance benefit here. But, no, you can't create a temp table using OQ.

If you can provide more details of your specific situation, we can help you make your code perform better.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Durendal SSC Enthusiast Points: 184 More actions · Answer 2

I have a set of queries that query a linked server using 4 part naming. Some of these use temp tables and some do not. We started seeing slow performance where the queries would take 20 seconds to return vs the 1 second response time we were expecting.

A suggestion was made to run the query with OPENQUERY instead of 4 part naming to improve performance. We tested this out with one of the queries that do not use a temp table and the response time went from 20 seconds to less than 1 second. All is good! Then in the process of converting queries that use temp tables to OPENQUERY ran into the issue of not being able to create a temp table with OPENQUERY.

At this point I think I'll need to query the remote server directly from the client and avoid linked servers and OPENQUERY all together.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

Openquery runs on the remote server and returns the result set. 4 part names runs local and has to suck the data across the network needed for the query. There are some syntax issues you could run into when using openquery to non sqlserver sources.

Tom

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

is it possible to write the query in a way that you are just reading the data from the remote server through OpenQuery, then writing it to a local temp table instead of a remote temp table?