How to fetch 2 set of records in MII from SQL procedure

[Former Member]

Hi Experts,

I am invoking a SQL procedure from MII which return 2 set of records. But at MII I am able to get only first set of records. Is there any configuration required at MII side or SQL side to get both set of records in MII?

Here is the SQL Query Structure

Create procedure Sample_Proc

  @Param1 Varchar(10),

  @Param2 varchar(10),

  @Param3 Varchar(20) OUT,

  SET INCOUNT ON;

AS

Begin

  *//Selection statements//*

END

SP Executing in MII

Declare @Param1,
  @Param2,
  @Param3,

Exec Sample_Proc

  @Param1='name',
  @Param2='Id',
  @Param3=@Param3 OUTPUT,

Select @Param3

Our SP is returning values (Say Recordset1)based on the input parameters 1 and 2 , along with Parameter3 value(Say Recordset2) in MS SQL server but in MII its returning only the values(Recordset1) ... how to fetch recordset2 values in MII

I hope MII can return 2 set of records (rowsets) after executing the procedure.

MII version -> 12.2.3 Build(182)

Thanks & Regards,

Rajasekhar Kantepalli

[former_member185280]

It looks like the ability to get multiple result sets was added in version 14.0 SP03:

http://service.sap.com/sap/support/notes/1901036

• Updated functionality of SQL Query templates to enable the template to return multiple rowsets when running a Fixed Query.

Regards,
Christian

[swaroop_anasane]

Hi Christian,

But above mentioned behaviour is observed in Sp07 as well where nocount is off and it returns no result.

Regards,

Swaroop

[swaroop_anasane]

Hi Rajasekhar,

I second Christian here. 14.0 till sp07 does have a fix for this. Not an ideal case hence can call it a bug.

Haven't tried the same in 15.0 yet.

If you are working on 14.0, it won't work. Let me know what is your output structure so that I can suggest a work around.

Best Regards,

Swaroop

[swaroop_anasane]

Didn't saw the version....in 12.x it would not work.

[manishagb]

Hi Swaroop,

With MII 14.0 SP5 Patch 11, in a transaction, I get following XML output for a query that executes an SP(returning multiple resultSets) :

And, results in this format can surely be used for further processing in an MII transaction.

Thanks Rajasekhar, got to know about this because of your query.

regards,

Manisha

[swaroop_anasane]

Hi Manisha,

This again depends on how you right the sp. For instance, if your set nocount is off and you have an update query post that you have a select query, it would fail to get even one record set.

Anyways,there's always a work around.

Regards,

Swaroop

[former_member185280]

I do not think MII supports getting multiple record sets back from one query.

[manishagb]

We use MII 14.0. It supports usage of SPs returning more than one recordsets. Not sure about the 12.x version(s). Need to trace down through the MII upgrades.