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
– Joe Stefanelli, Commented Nov 4, 2010 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
– nosirrahcd, Commented Nov 4, 2010 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
– nosirrahcd, Commented Nov 4, 2010 at 21:16

Timothy Khouri · Accepted Answer · 2011-02-12 19:08:37Z

2

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.

answered Feb 12, 2011 at 19:08

Timothy Khouri

32k21 gold badges94 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related