error when running openquery

[MaryJaneDoane]

I have a stored procedure that runs based on an open query. I am getting an error I can't resolve. Below is the code calling hte stored procedure. The stored procedure runs fine by it self. Any ideas.

select * from openquery(ccbdata01, 'SET FMTONLY OFF exec OREO.dbo.usp_GenerateTmpPivot')


Here is the error (Cannot process the object "SET FMTONLY OFF exec OREO.dbo.usp_GenerateTmpPivot". The OLE DB provider "SQLNCLI" for linked server "ccbdata01" indicates that either the object has no columns or the current user does not have permissions on that object.
)

Thank you
MaryJDoane

 

[genomon]

I haven't used openquery before, but a quick look at BOL reveals the following:

openquery Requires membership in the public role.

"select * from openquery(ccbdata01, 'SET FMTONLY OFF exec OREO.dbo.usp_GenerateTmpPivot')" may not be the correct syntax - example:

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

And, finally, you could try putting a semicolon after SET FMTONLY OFF.

I don't know if any of this helps - like I said, I just checked out BOL and don't have practical experience with openquery.

Good luck!


Beir bua agus beannacht!

 

[MaryJaneDoane]

Thank you genomon for getting back to me so fast. I will take a look at bol.

 

[genomon]

I should have added that if the sp is actually performing the query, you only need to code EXECUTE OREO.dbo.usp_GenerateTmpPivot. Or EXECUTE @return_code = usp_procname where @return_code is user-defined (this traps the return code in case of an error).

Beir bua agus beannacht!

 

[Emtucifor]

The only reason you'd use FMTONLY off is to deal with SQL Server's propensity to try to get resultset metadata out of queries that are submitted to it (because it secretly runs the SP once with SET FMTONLY ON, which fails for example if the SP creates a temp table since the table won't be created). If problems occur running the SP with SET FMTONLY ON, then using SET FMTONLY OFF can fix the problem as long as running the SP twice causes no harm.

This trick is used in SSIS a lot to trick it into validating (or you can turn off pre-execution validation).

Also note that you can use four-part naming and don't have to use OPENQUERY:

EXEC ccbdata01.OREO.dbo.usp_GenerateTmpPivot

you can insert the results into a table and work with that if necessary as well, just use `INSERT Table EXEC ...`

Anyway, since your SP IS returning a rowset, why bother putting SET FMTONLY OFF in front?