Error with Openquery and sp_stored_procedures... help with error or a better way
-
I'm trying to run this on your new SQL 2005 server and I'm getting an error (error is below for reference). I'm running this way because I want to join the results with a table.
Is another way to get the results so that they can be joined? I'm only using Openquery because that is the only way I could find to get the results so that I could join them. The data is from the local server.
If not maybe someone can help me with the error. I've googled and found only one reference to the error. For some reason it seems to think that the parameters for the sp_stored_procedures changed order.
Select
*
From
Openquery (
theservername
,
databasename..sp_stored_procedures'
)
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "theservername" supplied inconsistent metadata for a column. The column "PROCEDURE_QUALIFIER" (compile-time ordinal 1) of object "documentation..sp_stored_procedures" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
-
Our developers like to return the recordset to a table variable, then join the table variable to another table or view.
Greg Roberts
-
Thanks for the feedack. That is the only thing I could of as well. That is probably a better way to do this anyhow
-
First of all you need to specify the schema ex: "dbo".
Second the correct syntax should include "exec"
Third the Openquery rowset must be aliased in my example : dt
Fourth If the procedure uses internal temporary tables you may need to redesign it or try the "set fmtonly off" hack
Fith In 2005 the option has to be explicitly enabled, for the error that you are receiving I think you already did that
Last and not least if you are going to insert the result of openquery on any table then MSDTC is required
Select *
From Openquery ( theservername, 'exec databasename.dbo.sp_stored_procedures' ) dt
This reminds me that I should write an article on linked servers but I haven't had the time.
Good Luck
* Noel -
Ya you're past du on this one... and I don't think there are many guys who know more than you on this subject!!!!!
-
Don't know about all that but this did work fine under SQL 2000.
I agree that schema should be used this procedure just has not been brought up to date with standards which include always using the owner (now schema) name.
Not sure what you are saying about the exec. In the stored procedure this is being used in it is using the exec but only so that a use statement can be used to switch to the DB that this needs to be run in.
I didn't get a syntax error on the alias so I missed that since I was focused on the inconsistant metadata error.
All that and still no answer about the error. I ended up just rewritting this to use a table that gets cleared each time this runs. It is a low volumn and low freqency procedure so this works fine. Have other things that need my attention now.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply