Create temp table in Openquery?

  • Durendal

    Valued Member

    Points: 74

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

  • ScottPletcher

    SSC Guru

    Points: 98427

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Durendal

    Valued Member

    Points: 74

    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: 5705

    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: 42080

    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?

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

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