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) =
<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.