Possible to use "INSERT INTO @VARIABLE EXEC() AT LINKED_SERVER" syntax? (SQL Server 2005)

Question

I am trying to execute a query on a linked server, but I need the results locally.

DECLARE @test TABLE
(
greeting CHAR(5)
)

INSERT INTO @test
EXEC('select ''hello'' as greeting')

SELECT * FROM @test

Uses an EXEC() and INSERT INTO but, obviously the query is executing locally.

DECLARE @test TABLE
(
greeting CHAR(5)
)

INSERT INTO @test
EXEC('select ''hello'' as greeting') AT LINKED_SERVER

SELECT * FROM @test

Does not work at all.

SELECT greeting FROM OpenQuery(LINKED_SERVER,'SELECT''hello'' AS greeting')

Accomplishes exactly what I want, but I need to be using a dynamic string, and the only way to make that work is to make my entire query a huge string and put it into an EXEC(), which I don't want to do since it is really ugly....

Thanks for any help!

Your middle version works for me in a simple test case. What is the exact problem you're experiencing with it? — Joe Stefanelli, Nov 04 '10 at 20:57
It says that a distributed transaction isn't supported by the server. I probably should mention that the linked server isn't another SQL Server but an OLE DB. — nosirrahcd, Nov 04 '10 at 21:07
But it is good to know, at least that what I am trying to do probably just isn't possible with an OLE db since the syntax is valid. — nosirrahcd, Nov 04 '10 at 21:16

score 2 · Accepted Answer · answered Feb 12 '11 at 19:08

You could use the following:

INSERT INTO @test
EXEC('select TOP 1 ''hello'' as greeting FROM LINKED_SERVER.SomeDB.dbo.SysObjects')

And assuming you don't want to actually just get the word "hello"... you probably want to do something like actually selecting a value from a table, then the above should be even more likely what you want.

Possible to use "INSERT INTO @VARIABLE EXEC() AT LINKED_SERVER" syntax? (SQL Server 2005)

1 Answers1