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

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?