2

Does any one have any idea why I wont get a resultset if I do an update before the select in a store procedure. Im running MSSQL server 2005 and the latest Microsoft JDBC driver.

Relevant java code:

CallableStatement cstmt = con.prepareCall("{call dbo.sp_groups_select}");
if (cstmt.execute()) {
    while (cstmt.getResultSet().next())

Does not get a resultset if the store procedure looks like this:

CREATE PROCEDURE [dbo].[sp_groups_select] AS
update Computers set ComputerName='Foo'  where ComputerName='bar';
select * from Computers;

But if it looks like this I do get a resultset

CREATE PROCEDURE [dbo].[sp_groups_select] AS
select * from Computers;
update Computers set ComputerName='Foo'  where ComputerName='bar';
Balroq
  • 487
  • 3
  • 6
  • 17
  • Have you run it on the database directly? (Trying to see if this is a SQL-Server issue or Java) – jzd Jan 26 '11 at 20:44

1 Answers1

8

Probably because you need SET NOCOUNT ON

The (x rows affected) equivalent is processed as a resultset in the client, which bollixes cstmt.getResultSet().next()

Shameless plug of my question about SET NOCOUNT ON

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Ahh that was it, now it works :) I was troubleshooting that one for a few hours and then it was solved in an hour here.. Got to love stackoverflow – Balroq Jan 27 '11 at 07:50
  • @gbn I'd buy you a drink. been banging my bonky head for the last four hours with Mr. DBA trying to fix this – chrome Jul 04 '13 at 14:25