JDBC Query Not Returning a Resultset Error

[Community Alums]

I have a JDBC connection to execute a SQL query and it's returning the following error below. When I run the query in SQL Management Studio it returns the data just fine. Has anyone experienced this error message before?

MID Server reported error: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:800)

com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)

com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)

com.service_now.monitor.jdbc.JDBCRowSet.query(JDBCRowSet.java:53)

com.service_now.mid.probe.JDBCProbe.doSelect(JDBCProbe.java:285)

com.service_now.mid.probe.JDBCProbe.doQuery(JDBCProbe.java:175)

com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:101)

com.service_now.mid.probe.AProbe.process(AProbe.java:61)

com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:101)

com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

[Artemis15]

Hi,

Can you provide the version of sq l server that you are using?

Also please provide the connection string?

Thanks,

Akash Rajput

[Community Alums]

Hi Akash,

We're currently using SQL Server 2008. The connection string is just the settings that are on the Data Source page. We have 2 other Data Source connections that call different queries and they seem to work but this one is returning an error. The SQL query calls a fairly complex stored procedure that has a temp table but it should still return a result set that ServiceNow is not finding.

[Artemis15]

Hi Aryanos,

Are you appending any port number in connection string for this data source? From your explanation, I assume that your conn. string must be like :

.

DriverManager.getConnection("jdbc:sqlserver://servername\\Inst01:1433","user","pass");

sometime before, i also got this error and just change the connection string   by this

con=DriverManager.getConnection("jdbc:sqlserver://servername\\Inst01","user","pass");

by removing the port.

It was connecting to the default instants and not the named instance (Inst01).

Also, please provide the driver version you are using ? I again have to dig into that.

and also could you please provide your query that calls your storeproc? That will be helpful to find out the solution.

[kuligof]

I ran into this error myself today.   I was executing a Stored Procedure from SQL Server 2008, and received this error.   It appears that the way to fix that is to 1) make sure your proc returns some rows, and 2) SET NOCOUNT ON to keep the '# of rows affected' messages from appearing.  

My Stored Proc did several updates to a staging table (for an SCCM Import), and each of those would show a '# of rows affected' message.   Putting the SET NOCOUNT ON statement at the beginning of my Stored Proc (right after the BEGIN statement), stopped those messages.  

Then, I also had to do some type of SELECT at the end of my proc, to actually return a result set for Svc Now to process.   So I just did a SELECT COUNT(*) at the end to accomplish that.