• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

exec of stored procedure not working with Microsoft sqljdbc 2.0

I am running an SQL Server stored procedure from a jsp program. It has been running fine with Microsoft sqljdbc driver version 1.1, but today I installed version 2.0. Now when I run the SP I get:

 DROPreport.jsp: 0, The statement did not return a result set.
exec dbo._DROPinterest_sp '546370',null

The last line above beginning "exec dbo. ..." is the query.

Any ideas?
0
jmarkfoley
Asked:
jmarkfoley
  • 3
  • 2
1 Solution
 
elimesikaCommented:
HI

Yes, actually , this is a FEATURE added with the 1.2 release.

But, you can still get it working

See
http://blogs.msdn.com/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx
0
 
elimesikaCommented:
another solution I have seen suggested to call
SET NOCOUNT ON
if your SP does not return a result set

try it

Full post
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6bd281b0-c9d7-4987-80e7-2901b72daafe
0
 
jmarkfoleyAuthor Commented:
elimesika - I checked out that link. You have got to be kidding me! Did someone have a contest to see how complex a jdbc query could be made? Hats off the to winner! That example is 70 lines of code instead of the less than 10 lines with the 1.1 Statement.executeQuery()

This stored procedure does return one result set, it is not empty and I do have SET NOCOUNT ON.

I have stored procedures all over this application. I can afford to go through and rewrite all my programs because of a driver change.

Is this the only possible reason this query isn't working? If so, I'll have to stick with version 1.1
0
 
elimesikaCommented:
HI

Sorry to say that I don't know any other workaround for that, anyway, I am still working with the 1.1 driver in some of our applications , so, if you can stay with the 1.1 driver , its seems to me OK.
0
 
jmarkfoleyAuthor Commented:
OK, I'll stick with 1.1 for now! It seems crazy for an driver upgrade to kill existing apps. Plus, it seems nonsensical in this case where the SP returns a simple result set. I can't imagine anyone cares if an SP "partially" works or some, but not all, of the component queries work. Either it works and returns the desired result set or the programmer has more work to do to fix the query!  But hey, that's just my opinion! Thanks.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now