OPENQUERY problem with dynamic result set

I am having a problem with OPENQUERY in SQL2005.
SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
This generates an error along the lines of:
The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
that either the object has no columns or the current user does not have
permissions on that object.
I am pretty sure this is becuase my procedure returns a dynamic
resultset with a dynamic number of columns.
I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
other suggestions; however, I still get the same error. I am guessing
possibly, becuase my procedure in turn calls another dynamically
executed OPENQUERY MDX query against an OLAP data source.
The whole reason I am doing this is becuase I need to get the results
of the stored procedure into a table for further manipulation.
Modifying the underlying procedures is not really an option.
My procedure returns a dynamic number of columns so I can't create a
temp table ahead of time to stick the results into.
I have been going around in circles for a while on this one. Any
advice would be much appreciated.Hi
If you granted EXECUTE permission to stored procedure which performs dynamic
sql , it will not be enough , you'll have to grant permissions on the
object/ underlaying tables
However , in SQL Server 2005 you are be able to perfom EXECUTE AS ( If I
remember well) as more power user
<jalbenberg@.yahoo.com> wrote in message
news:1148426602.112398.67180@.i39g2000cwa.googlegroups.com...
>I am having a problem with OPENQUERY in SQL2005.
> SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
> This generates an error along the lines of:
> The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
> that either the object has no columns or the current user does not have
> permissions on that object.
> I am pretty sure this is becuase my procedure returns a dynamic
> resultset with a dynamic number of columns.
> I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
> other suggestions; however, I still get the same error. I am guessing
> possibly, becuase my procedure in turn calls another dynamically
> executed OPENQUERY MDX query against an OLAP data source.
> The whole reason I am doing this is becuase I need to get the results
> of the stored procedure into a table for further manipulation.
> Modifying the underlying procedures is not really an option.
> My procedure returns a dynamic number of columns so I can't create a
> temp table ahead of time to stick the results into.
> I have been going around in circles for a while on this one. Any
> advice would be much appreciated.
>|||Sorry if my post was misleading. This is not a permissions issue - the
queries run fine outside of the OPENQUERY context.
The problem is the dynamic recordset that comes back with an unknown
number of columns, so OPENQUERY cannot properly prepare the statement.

OPENQUERY problem with dynamic result set

I am having a problem with OPENQUERY in SQL2005.
SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
This generates an error along the lines of:
The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
that either the object has no columns or the current user does not have
permissions on that object.
I am pretty sure this is becuase my procedure returns a dynamic
resultset with a dynamic number of columns.
I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
other suggestions; however, I still get the same error. I am guessing
possibly, becuase my procedure in turn calls another dynamically
executed OPENQUERY MDX query against an OLAP data source.
The whole reason I am doing this is becuase I need to get the results
of the stored procedure into a table for further manipulation.
Modifying the underlying procedures is not really an option.
My procedure returns a dynamic number of columns so I can't create a
temp table ahead of time to stick the results into.
I have been going around in circles for a while on this one. Any
advice would be much appreciated.Hi
If you granted EXECUTE permission to stored procedure which performs dynamic
sql , it will not be enough , you'll have to grant permissions on the
object/ underlaying tables
However , in SQL Server 2005 you are be able to perfom EXECUTE AS ( If I
remember well) as more power user
<jalbenberg@.yahoo.com> wrote in message
news:1148426602.112398.67180@.i39g2000cwa.googlegroups.com...
>I am having a problem with OPENQUERY in SQL2005.
> SELECT * FROM OPENQUERY(MySQLServer, 'EXEC myProc')
> This generates an error along the lines of:
> The OLE DB provider "SQLNCLI" for linked server "MySQLServer" indicates
> that either the object has no columns or the current user does not have
> permissions on that object.
> I am pretty sure this is becuase my procedure returns a dynamic
> resultset with a dynamic number of columns.
> I have tried prefacing the EXEC call with SET FMTONLY OFF as per some
> other suggestions; however, I still get the same error. I am guessing
> possibly, becuase my procedure in turn calls another dynamically
> executed OPENQUERY MDX query against an OLAP data source.
> The whole reason I am doing this is becuase I need to get the results
> of the stored procedure into a table for further manipulation.
> Modifying the underlying procedures is not really an option.
> My procedure returns a dynamic number of columns so I can't create a
> temp table ahead of time to stick the results into.
> I have been going around in circles for a while on this one. Any
> advice would be much appreciated.
>|||Sorry if my post was misleading. This is not a permissions issue - the
queries run fine outside of the OPENQUERY context.
The problem is the dynamic recordset that comes back with an unknown
number of columns, so OPENQUERY cannot properly prepare the statement.sql

OpenQuery - Execution plan

Whats the best way to run some dynamic sql when using OpenQuery on a linked
server? Is it possible to use sp_executesql in OpenQuery?
Ben
See if this helps you
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..authors'
EXECUTE ('SELECT * FROM OPENQUERY([servername], ''SELECT * FROM ' +
@.tablename + ''') AS T')
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E6724190-2A1D-4FCB-A6E5-18A8F312D6E6@.microsoft.com...
> Whats the best way to run some dynamic sql when using OpenQuery on a
> linked
> server? Is it possible to use sp_executesql in OpenQuery?
sql

OpenQuery - Execution plan

Whats the best way to run some dynamic sql when using OpenQuery on a linked
server? Is it possible to use sp_executesql in OpenQuery?Ben
See if this helps you
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..authors'
EXECUTE ('SELECT * FROM OPENQUERY([servername], ''SELECT * FROM ' +
@.tablename + ''') AS T')
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E6724190-2A1D-4FCB-A6E5-18A8F312D6E6@.microsoft.com...
> Whats the best way to run some dynamic sql when using OpenQuery on a
> linked
> server? Is it possible to use sp_executesql in OpenQuery?

OpenQuery

I wish to send dynamic SQL to Openquery but it seems OpenQUERY only accepts
string constants. Any advise on how to do this. It would convenient if
Openquery could execute similar to dynamic SQL EXEC.
Please advise.
Regards,
Tim PeeIf you need it to be dynamic, you can build the entire
string that does the Openquery statement. Pass that string
into an EXEC. You can find examples in the following
article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Thu, 23 Jun 2005 21:34:04 -0700, Timothy Peer
<TimothyPeer@.discussions.microsoft.com> wrote:

>I wish to send dynamic SQL to Openquery but it seems OpenQUERY only accepts
>string constants. Any advise on how to do this. It would convenient if
>Openquery could execute similar to dynamic SQL EXEC.
>Please advise.
>Regards,
>Tim Pee

OpenQuery

I wish to send dynamic SQL to Openquery but it seems OpenQUERY only accepts
string constants. Any advise on how to do this. It would convenient if
Openquery could execute similar to dynamic SQL EXEC.
Please advise.
Regards,
Tim Pee
If you need it to be dynamic, you can build the entire
string that does the Openquery statement. Pass that string
into an EXEC. You can find examples in the following
article:
HOW TO: Pass a Variable to a Linked Server Query
http://support.microsoft.com/?id=314520
-Sue
On Thu, 23 Jun 2005 21:34:04 -0700, Timothy Peer
<TimothyPeer@.discussions.microsoft.com> wrote:

>I wish to send dynamic SQL to Openquery but it seems OpenQUERY only accepts
>string constants. Any advise on how to do this. It would convenient if
>Openquery could execute similar to dynamic SQL EXEC.
>Please advise.
>Regards,
>Tim Pee