Avoid JDBC sender error: Execute statement did not return a result set

[ystein_emhjellen3]

Hi!

My JDBC sender adapter towards MS SQL server works fine, with an Execute statement calling a stored procedure that returns the source data needed. The stored procedure itself updates the status of database table records, so that only the unread records are returned each time the stored procedure is called.

However, the communication channel monitoring sets a red flag for the JDBC sender adapter, when there are no values to fetch from the database table (using the stored procedure). Message says: "Database-level error reported by JDBC driver while executing statement 'EXECUTE FetchMessage 1, 9000'. The JDBC driver returned the following error message: 'com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.'. For details, contact your database server vendor."

This is not an error situation, as I do not expect there to be any values to fetch from the database at all times.

I do not see how to change the stored procedure to avoid this error.

Is there a parameter to be set on the JDBC adapter that I can use, so the red flag is avoided?

Thanks for any input!

Regards,

Oeystein Emhjellen

[former_member241146 · Accepted solution]

Hi Oeystein Emhjellen.

The problem is Store Procedure that has to generate always a ResultSet (or cursor). If it doesn't have a output, you have to generate an Empty ResultSet.

Like a SELECT Statement:

If there are data, SELECT get an output result but if it get nothing the SELECT Statement get a empty ResultSet.

Ask to your database team.

I hope it helps you.

Bruno.

[ystein_emhjellen3]

Thanks for your input!

I asked the creator of the stored procedure to produce an empty resultset if no records are available, and that did the trick!

Problem solved, no more error message from the jdbc adapter!

Regards,

Oeystein

[former_member192343]

Can your stored procedure give an empty answer if no result?

and check if you deploed all files for MSSQL JDBC driver

[Former Member]

Hi,

Did you manage to resolve this problem?

[ystein_emhjellen3]

No, I have not.

[Former Member]

Hi

I hope you have a flag set in the table which is set to "Y" or " Successful" each time a record is read so that it does not pick it the next time.

Regards

Monika