Create temp table in Openquery?

  • Durendal

    SSC Rookie

    Points: 44

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

    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

    SSC Rookie

    Points: 44

    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.

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

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