Steps taken -
- sql traces performed (stored procedure was being executed against db)
- sp returns data (proved this by running the traced code manually in a query window)
- became puzzled...
Solution - SQLServerException: The statement did not return a result set when sp uses cursors
The Java code was using executeQuery rather than execute . This meant that executeQuery was effectively seeing 'inside' the stored procedure and was looking at a recordset in use by a cursor, rather than the final recordset returned when the query was run manually.
"The method executeQuery is designed for statements that produce a single result set, such as SELECT statements"
"The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two."
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1710309&SiteID=1
