Using OPENQUERY to execute a script

Question

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?

Answer 1

You are probably seeing an error like:

Msg 7357, Level 16, State 1, Line xx
Cannot process the object "<query text>".
The OLE DB provider "<provider>" for linked server "<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'
    <some amazing script>
    ';

EXECUTE (@Script) AT <linked_server_name>

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 <table> (columns)
EXECUTE (@Script) AT <linked server>;

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.

Answer 2

Late to the question, but nonetheless, I thought I'd add my two pennies worth.

Make your first line in the procedure "SET NOCOUNT ON". This will suppress multiples result sets produced from all the Rows Affected returns. (it works for me!)

Answer 3

If you have any comments in the first line following OPENQUERY it will also give similar error.

i.e.

Select * From
OPENQUERY ( [db], '

#EFT history of all clients

Select 
   a.ClientNumber,
    a.Description,
    Sum(a.units) Drafts,
    Sum(a.amount) DraftAmount

 From trans a

Group by a.ClientNumber,
        a.Description

') a

Moving the comment after Select or at end will solve error