Partager via

SQLServerException: The statement did not return a result set

  • Article

Have you ever encountered this exception from executeQuery()? If so, chances are good that your query actually did return a result set (we’ll be addressing the error message text...), but it wasn’t the first result. Look at your query or stored procedure. Does it contain multiple SQL statements? Maybe it includes DDL and DML to create and populate a temp table before the SELECT statement. If that is the case, then you really need to use execute(), not executeQuery(), to execute it. The problem is that executeQuery(), per JDBC spec, should only be used with queries whose only result is a single ResultSet. Quoting from Sun’s documentation of this method:

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the given SQL statement produces anything other than a single ResultSet object

Some JDBC drivers apparently just ignore and discard DDL and DML results in executeQuery() rather than throwing an exception.

For an explanation on how to use execute() and its partner, getMoreResults(), to execute complex queries, look here.

 

 --David Olix [SQL Server]

This post is provided 'as is' and confers no express or implied warranties or rights.

Comments

  • Anonymous
    August 04, 2008
    PingBack from http://blog.a-foton.ru/2008/08/sqlserverexception-the-statement-did-not-return-a-result-set/

  • Anonymous
    May 17, 2009
    The comment has been removed

  • Anonymous
    July 19, 2013
    This error can be avoided if in case we can write all the select statement before any updates, insert or delete statement

  • Anonymous
    December 17, 2015
    i found a great solution in below post link scn.sap.com/.../3204708 check below post by Jaison Thomas This reply is too late for you, but may help someone else who is now facing asimilar issue. I am using Spring, Java and SQL Server 2012. The task was to migrate from Weblogic 9.2 + Sybase ASE to Tomcat 7.0 and SQL Server 2012. The stored procedures returned results in SQL server Mgmt Studio but not while using Java. Solution : Write   SET NOCOUNT ON just after the BEGIN of the stored procedure