cancel
Showing results for 
Search instead for 
Did you mean: 

MS SQL Stored Procedure call error

former_member190695
Participant
0 Kudos

Hi All,

I am calling a remote custom Stored Procedure from a To Database (SAP NW IDM 7.2) task and have the following error message each time an error is reported in the target system. java.lang.Throwable: A result set was generated for update.

The Stored procedure doesn't have any input parameters of type OUTPUT, so I think this is the reason why I am not seeing the real error message in SAP NW IDM.

Any idea's how to deal with this situation?

Many thanks.

Best Regards,

Ridouan Taibi

Accepted Solutions (1)

Accepted Solutions (1)

former_member190695
Participant
0 Kudos

Thanks for your prompt response Guys.

I think I have found a solution for my issue.

I have tried to create my own procedure which I call from IdM that makes a call to the remote procedure. This didn't work either even though I had declared an output parameter.

SET NOCOUNT ON was set from the beginning, indeed this is required to prevent the count of rows.

The script sap_core_callStroredProcedure seems to work with a To Database task only.

I am now using a generic task where I basically define my procedure and then use the internal function uSelect to return the output. EXEC @someDeclaredInteger = EXEC someProcedure @withSomeParameters --This is IMPORTANT -- SELECT @someDeclaredInteger

In my case the procedure was returning some application error codes.

Thanks again.

Best Regards,

Ridouan Taibi

Answers (2)

Answers (2)

Former Member
0 Kudos

It could also be caused by the procedure you're calling returning the count of rows inserted/updated as it's processing the data. Make a wrapper as Norman suggested and keep in mind his ignore output suggestion, and also add SET NOCOUNT ON at its beginning. Something like:

create procedure mywrapperthingy

     @param1 <type>

     @param2 <type>

     <and so on>

as

begin

     SET NOCOUNT ON

     exec originalprocedure @param1,paramt2,<and howevermanymore>

end

normann
Advisor
Advisor
0 Kudos

Hi Ridouan,

does the stored procedure have any output parameters? If so you cannot call it from IdM. You have to create an own stored procedure as a shelf that does not have an output parameter and does nothing but calling the original one.

Furthermore you should always make use of the script sap_core_callStroredProcedure. You can find an example of how to use it in the initial load jobs where the IdM attributes for the repositories are going to be created.

Regards

Norman