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

opening up odbc data source in the query query inside of the server manager

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.

Thanks in advance

Daniel Buchanan.

If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

You can set up linked servers on one of the SQL Servers and join the data.

http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx

hth

BobP