Post reply

Alternatives to using temp tables within OPENQUERY

  • June 7, 2011 at 11:50 am

    #251458

    I understand that the below code will not work.

    SELECT t.*

    FROM OPENQUERY([linkedservername],

    'CREATE TABLE #tmp (testcolumn varchar(4)); INSERT INTO #tmp SELECT ''test'';

    SELECT * FROM #tmp') t

    OLE DB provider "SQLNCLI10" for linked server "linkedservername" returned message

    "Deferred prepare could not be completed.".

    Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Msg 208, Level 16, State 1, Line 1

    Invalid object name '#tmp'.

    Are there any alternatives that do not require RPC to be enabled on the linked server? I am dealing with a third party who will not enable this for us (nor can we create stored procs on their DB). Executing my query locally is not an option due to the performance hit that comes with a distributed query against a linked server. I will also suffer a performance hit without the temp table that is being created on the remote server.

    Any ideas are appreciated, thanks!

  • June 7, 2011 at 2:31 pm

    #1335323

    Did you try the Execute...At command?

    EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'

    GO

    EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl

    (SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

    GO

    -------------------------------------------------
    Will C,
    MCITP 2008 Database Admin, Developer

  • June 7, 2011 at 2:34 pm

    #1335326

    Hi ricer. Yes, I did. But because RPC is not enabled, it does not work.

    Msg 7411, Level 16, State 1, Line 1

    Server [linkedservername] is not configured for RPC.

    Edited to remove propietary linked server name...

  • June 7, 2011 at 2:41 pm

    #1335337

    There is no other alternative I am afraid.

    Either you need the permission on the remote server or you just have to deal with performance hit with executing the query against the linked server.

    -------------------------------------------------
    Will C,
    MCITP 2008 Database Admin, Developer

  • June 7, 2011 at 2:44 pm

    #1335344

    Pity. Well thanks for the confirmation. This just means I get to go back to the third party and push them to add proper indexes and whatnot to their DB. 😀 Thanks!

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

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