2

Currently using WSO2 DSS 3.5.0 to execute a stored procedure in MSSQL that does an insert in a transaction. The procedure responds with a return code and a return message. The DSS is able to execute the statement in the database as the new entries are added, but when i try to map the output of the procedure to the output of the DSS i get the following exception:

com.microsoft.sqlserver.jdbc.SQLServerException: This operation is not supported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.NotImplemented(SQLServerStatement.java:613)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:2374) ...

This is the statement i'm using:

DECLARE @returnCode int, @returnMessage varchar(255)

EXEC "dbo"."sp_insertExample" @ipFirstId = ?, @ipSecondId = ?, @opErrorCode = @returnCode output, @opErrorMesssage = @returnMessage output

SELECT @returnCode, @returnMessage

The inputs are both int values.

Has it happen to somebody else, and do you know how to correct it or what i'm doing wrong?

Edit - Adding the mapping done between outputs:

<result element="result" escapeNonPrintableChar="true" rowName="Addition" useColumnNumbers="true">
    <element column="1" name="ReturnCode" xsdType="integer"/>
    <element column="2" name="ReturnMessage" xsdType="string"/>
</result>
Bernardo Palma
  • 63
  • 7
  • What version of SQL Server are you using? Using the same basic syntax, can you execute a simple select? – Neo Dec 09 '16 at 15:11
  • 1
    Using Microsoft SQL Server 2014, and yes i have created more services that work fine, including one similar to this one, that calls the procedure and then does a select for the output – Bernardo Palma Dec 09 '16 at 15:15
  • Can you execute the stored proc in SSMS? – Neo Dec 09 '16 at 15:17
  • 1
    "Yes" in DBeaver and HeidiSQL, both the stored procedure and this statement to call it in particular, because when i call the service the procedure is executed, but the conversion of the output to the DSS one fails – Bernardo Palma Dec 09 '16 at 15:20

1 Answers1

3

So found out what was different between the stored procedures, basically there was a line missing from one that apparently was important, namely:

SET NOCOUNT ON;

In my case it was that, hope it helps someone else.

Bernardo Palma
  • 63
  • 7