can not get union recordset from two Sql Server2K store procedure
-
There are two store procedure on same DB server: create procedure usp_A as declare @ShiftName as datetime select @ShiftName=getdate() select * from LineConfig where Cur_PlanQty_0=0 GO create procedure usp_B as select * from LineConfig where Cur_PlanQty_0=1 GO I use follow sql want to get the union record sets select * from openrowset('sqloledb','trusted_connection=yes','abc.dbo.usp_A') U N I O N ALL select * from openrowset('sqloledb','trusted_connection=yes','abc.dbo.usp_B') get the follow error message: Could not execute query against OLE DB provider 'sqloledb'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements. OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. if I remove the usp_A the local variable operation sentence: select @ShiftName=getdate() the error message disappear. if I add new sentence such as: declare @ShiftValue as varchar(10) the same error message display again.
-
There are two store procedure on same DB server: create procedure usp_A as declare @ShiftName as datetime select @ShiftName=getdate() select * from LineConfig where Cur_PlanQty_0=0 GO create procedure usp_B as select * from LineConfig where Cur_PlanQty_0=1 GO I use follow sql want to get the union record sets select * from openrowset('sqloledb','trusted_connection=yes','abc.dbo.usp_A') U N I O N ALL select * from openrowset('sqloledb','trusted_connection=yes','abc.dbo.usp_B') get the follow error message: Could not execute query against OLE DB provider 'sqloledb'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements. OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. if I remove the usp_A the local variable operation sentence: select @ShiftName=getdate() the error message disappear. if I add new sentence such as: declare @ShiftValue as varchar(10) the same error message display again.
-
Don't
UNIONon anOpenRowSet. Import it into a local (temp) table, and union from there. That way you'll also see which of both procedures is failing.Bastard Programmer from Hell :suss:
I have to insert the temp record into a temp table in order to resolve the issue at last. But I think I have a lot of user store procedures to compute record sets. Do I have to create a lot of temp tables? Is it a bug or some mis-understand by me?
-
I have to insert the temp record into a temp table in order to resolve the issue at last. But I think I have a lot of user store procedures to compute record sets. Do I have to create a lot of temp tables? Is it a bug or some mis-understand by me?
Zhenjie Fu wrote:
Do I have to create a lot of temp tables?
Nope, only for the two tables that you're importing with the OpenRowSet-command.
Zhenjie Fu wrote:
Is it a bug or some mis-understand by me?
It's an error in one of the imports. Hence the suggestion to select those two first into a temporary table, and then to union on those.
Bastard Programmer from Hell :suss: