Problem:
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
Background:
Stored Procedure is inserting few records in temporary table and at the end returning record set. (See code snippet-01)
Environment:
SQL Server version - MS SQL Server 2008 R2
Java version - 1.7
Solution:
Use 'SET NOCOUNT ON' as very first statement inside stored procedure to stop returning insert/update/delete/select confirmation message. To demonstrate that I have switched output format to text in 'SQL Server Management Studio' (shortcut key is cntl+T). After that SP execution output looking similar as following where first and third lines are the confirmation message for INSERT and second SELECT. When 'SET NOCOUNT ON' is used, output should include only the table.
EXECUTE SPTest;
(3 row(s) affected)
id
-----------
13
14
15
(3 row(s) affected)
Code snippets:
Snippet-01: dummy stored procedure created to test purpose
IF OBJECT_ID('dbo.SPTest', 'P') IS NOT NULL
DROP PROCEDURE dbo.SPTest;
GO
CREATE PROCEDURE dbo.SPTest
AS
BEGIN
select ID from wk_ctr
create table #test(id INT);
INSERT INTO #TEST
select ID from wk_ctr;
select * from #TEST;
END;
Snippet-02: Java class to invoke the Stored Procedure
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTempTableOutput {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.println("before class load2");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("before connection creation2");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=dbname","username","password");
System.out.println("Before statement creation2");
stmt = conn.createStatement();
String sql = "EXECUTE SPTest";
rs = stmt.executeQuery(sql);
if(rs.next())
System.out.println("resultset found");
else
System.out.println("resultset not found");
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} catch (Exception e) {
e.printStackTrace();
try{
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception ex){}
}
System.out.println("finished");
}
}
Tips:
1. To change SQL Server Management studio output format to text, use shortcut (ctrl+t)
2. To change SQL Server Management studio output format to table, use shortcut (ctrl+d)
com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
Background:
Stored Procedure is inserting few records in temporary table and at the end returning record set. (See code snippet-01)
Environment:
SQL Server version - MS SQL Server 2008 R2
Java version - 1.7
Solution:
Use 'SET NOCOUNT ON' as very first statement inside stored procedure to stop returning insert/update/delete/select confirmation message. To demonstrate that I have switched output format to text in 'SQL Server Management Studio' (shortcut key is cntl+T). After that SP execution output looking similar as following where first and third lines are the confirmation message for INSERT and second SELECT. When 'SET NOCOUNT ON' is used, output should include only the table.
EXECUTE SPTest;
(3 row(s) affected)
id
-----------
13
14
15
(3 row(s) affected)
Code snippets:
Snippet-01: dummy stored procedure created to test purpose
IF OBJECT_ID('dbo.SPTest', 'P') IS NOT NULL
DROP PROCEDURE dbo.SPTest;
GO
CREATE PROCEDURE dbo.SPTest
AS
BEGIN
select ID from wk_ctr
create table #test(id INT);
INSERT INTO #TEST
select ID from wk_ctr;
select * from #TEST;
END;
Snippet-02: Java class to invoke the Stored Procedure
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTempTableOutput {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.println("before class load2");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println("before connection creation2");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=dbname","username","password");
System.out.println("Before statement creation2");
stmt = conn.createStatement();
String sql = "EXECUTE SPTest";
rs = stmt.executeQuery(sql);
if(rs.next())
System.out.println("resultset found");
else
System.out.println("resultset not found");
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} catch (Exception e) {
e.printStackTrace();
try{
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception ex){}
}
System.out.println("finished");
}
}
Tips:
1. To change SQL Server Management studio output format to text, use shortcut (ctrl+t)
2. To change SQL Server Management studio output format to table, use shortcut (ctrl+d)
