Stored procedure: Select Failed: 0 The statement did not return a result set.

SQL Server 2005

I have the stored procedure shown below. This has been working from within a Java program fine for months and just started giving me the error: "Select Failed: 0 The statement did not return a result set."

It does actually do the insert, however.

When I run this procedure in Server Management Studio as the same user I do get the memberId returned.

Any idea what could be wrong?

ALTER procedure [dbo].[addNewMember_sp] (
  @memberId				varchar(11) = null,
  @memberType			varchar(10),
  @lastName				varchar(20),
  @firstName			varchar(15) = '',
  @middleInit		    char(1) = null,
  @retireeId			varchar(11) = null,
  @relationToRetiree	varchar(3) = null
)

As

declare @newId varchar(11)

if @memberId is null select @newId = convert(varchar,convert(bigint,max(memberId) + 1)) from members
else set @newId = @memberId

insert into members (memberId, memberType, firstName, lastName, middleInit, retireeId, relationToRetiree) 
  values (@newId,@memberType,@firstName,@lastName,@middleInit,@retireeId,@relationToRetiree)

select @newId as memberId

Open in new window


and the java statements:

                query = "exec hprs.dbo.addNewMember_sp @memberId='" + memberId +
                    "',@memberType='ACT',@lastName=" + mkDbString(LastName) +
                    ",@firstName=" + mkDbString(FirstName) +
                    ",@middleInit=" + mkDbString(MiddleInit);

                    try {
                        rs = stmt.executeQuery(query); // leave as executeQuery(), returns memberId
                    }
                    catch ( SQLException sqe) {
                        System.err.println("Select Failed: " + sqe.getErrorCode() + " "
                            + sqe.getMessage() + "\n" + query);
                    }
'

Open in new window

LVL 1
jmarkfoleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
can you get the exact "query" variable content?

is your mkDbString method adds single quotes around your string?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Inserting a row is not the same as returning a result set.

>select @newId as memberId
This line does not result in a return, only setting the @newid variable to memberid.

>When I run this procedure in Server Management Studio as the same user I do get the memberId returned.
The above line doesn't return a value.  'SELECT memberid' does.
0
jmarkfoleyAuthor Commented:
Éric Moreau:
can you get the exact "query" variable content?
exec hprs.dbo.addNewMember_sp @memberId='011780',@memberType='ACT',@lastName='Ashbaugh',@firstName='Brian',@middleInit='D'

Yes, the mkDbString() function adds single quotes, doubles single quotes if the string contains them, or returns "null" if the string is empty.

Jim Horn:
Inserting a row is not the same as returning a result set.
Right, but as I said, it used to do this (return the memberId) and still does in SQL Server Management Studio. If there is a different way to do this that might work more consistently I'm open to suggestions.

I was wondering if there was some update to SQL Server, or to java that could have changed things from April when this last ran successfully. The sqljdbc.jar file is dated 2010, so that hasn't changed.

What might be a better way to do the query in order to return the memberId?
0
jmarkfoleyAuthor Commented:
OK, I solved it. This time the java program ran used a new sqljdbc41.jar file downloaded when the more recent tomcat 8.0.23 was installed. I changed the sqljdbc.jar back to the one used before the tomcat update and it ran OK. I hate when they make changes to existing functionality that breaks things! Not sure who to complain to on this sort of thing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmarkfoleyAuthor Commented:
Thanks for playing!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.