Using OPENQUERY to execute a script

I have a SQL Server 2008 instance which I want to use to import data from an Oracle server.

I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. However, if I declare a variable or loop through the rows in a table or anything else inside the OPENQUERY, I get errors.

Is it possible to do this using OPENQUERY? Do I need additional permissions?

You are probably seeing an error like:

Msg 7357, Level 16, State 1, Line xx
Cannot process the object "".
The OLE DB provider "" for linked server "" indicates that either
the object has no columns or the current user does not have permissions on that object.


This occurs when SQL Server tries to discover the shape of the data returned by the OPENQUERY statement. There are many fascinating details here, but the upshot is that OPENQUERY is often best used with only a single SELECT statement, as all the examples in Books Online do.

It is possible to do other things with OPENQUERY, including executing a remote stored procedure, but the procedure should only return a single result set, and there should be nothing before that SELECT that might break the way SQL Server checks the result set shape.

An alternative is to use EXECUTE ... AT, for example:

DECLARE @Script nvarchar(max) =
N'

';

EXECUTE (@Script) AT


See Books Online for the full syntax and options.

You will probably need to set the linked server's Server Options property Enable promotion of distributed transactions for RPC to false to capture results in a table using:

INSERT  (columns)
EXECUTE (@Script) AT ;

Note this is potentially much less efficient because the results of the EXECUTE are spooled to tempdb before being fed to the INSERT. This can be problematic if the results are large.

StackExchange Database Administrators Q#64947, answer score: 10