sql server alwayson Returns the result difference from singel instance

Question

sql server alwayson Returns the result difference from singel instance

jiajun su 1

When we execute a stored procedure, we find that the results of the SQL Server stand-alone and alwayson queries are different, and there are more results.
sqlserver 2005 single instance：

sqlserver 2016 single instance:

sqlserver 2016 alwayson:

This difference leads to alwayson execution when use jdbc driver：
[INFO] [2022-12-26 12:27:32,386] [logid:32cd4b40eb2d429b8c27d143c2468793] [sessionId:6E09147B978936DAE6000DAFBDF45F7F] com.citic.crm.ncrm.dao.complaint.ComplaintDaoImpl.dealComplaint(ComplaintDaoImpl.java:1452) -投诉单投诉单操作(审核/经办/复核/结案)sql:{?=call dbo.sp_ComplainList_JinBan (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
[ERROR] [2022-12-26 12:27:32,387] [logid:32cd4b40eb2d429b8c27d143c2468793] [sessionId:6E09147B978936DAE6000DAFBDF45F7F] com.citic.crm.ncrm.dao.complaint.ComplaintDaoImpl.dealComplaint(ComplaintDaoImpl.java:1476) -投诉单投诉单操作(审核/经办/复核/结案)时出现异常:java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
at net.sourceforge.jtds.jdbc.ParamInfo.getOutValue(ParamInfo.java:159)
at net.sourceforge.jtds.jdbc.JtdsCallableStatement.getOutputValue(JtdsCallableStatement.java:115)

Why this discrepancy？

Yitzhak Khabinsky 26,586 Reputation points

2022-12-28T13:55:24.397+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

We need to see body of your stored procedure with all its parameters.

2 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-12-28T13:55:24.397+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

We need to see body of your stored procedure with all its parameters.

Answer 1

Bjoern Peters 8,921

Hi @jiajun su

make sure the configuration for each session in SSMS is the same and please post the same screenshot for the last result (2016 AO) with active tab on the text results...

Are you 100% sure that all data on all machines is the same?
What about database compat level on those three databases? Are they the same?

That screenshot of 2016 AO doesn't mean that there are more results, it just shows you a resultset as a grid... as Yitzhak already stated, we need to see your DDL for the proc to understand the output... your results on 2005 and 2016 seems to be the same (0,0,1)... so there is at least one row as a result... and maybe it is just a difference in the optical view in SSMS and the text output says the same as on the other instances...

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

jiajun su 1 Reputation point

2022-12-29T07:06:23.317+00:00

hi, expert：
we upgrade it to sqlserver 2016 alwayson from 2005，and it is normal for us to test in a single instance(2005 and 2016),but when we test in 2016 alwayson,it issue java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().
At last we found there are discrepancies in the output between single and alwayson architecture.
Bjoern Peters 8,921 Reputation points

2022-12-29T08:05:07.187+00:00

Hi @jiajun su

Your reply does not help assist you with your problem.

As we don't know any details of your solution, we can only guess what is happening in your environment...
There are so many technical changes in the version jump from 2005 to 2008(R2), 2012, 2014, and then to 2016 that there may be a behavior change based on the execution and based on pure code.

Technically, suppose you change from a standalone to an availability group. In that case, no changes are possible in the resultset of your executed code because you just change how your app connects to the SQL Server and how the SQL server handles the database itself!

Availability groups are just "database mirroring with one listener."
So if you enable AvailabilityGroups for your database, you are not changing any data or code within your database!

So, as Erland already pointed out... there must be a difference in the behavior of your application. If you just took the database with backup and restore and moved it from the 2005 to 2016 server, your app code is not working correctly.
And from my point of view, it is naive to think that a change over five (!) major releases will work without any problems, and the code will still work...

You have to dive deeper into your stored procedure's code and data structure.

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

Obvious possible reasons for the differences:

1) Different code in the different databases.
2) Different data in the different databases.

The fact that one database is in an availability group as such has nothing to do with it. Although, hypothetically, the SP may be coded in such a way that it produces a result set if you are in an AG. But the reason for the unexpected result set is likely to be something else, for instance a stray debug SELECT. You will need to look at the code.

You real problem seems to be the failing Java code, and the error message is very clear:

java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().

And that is exactly what you need. Whatever API you are using, you should always ask to get the next result set until you get a negative answer. You should never make an assumption that you only have a certain number of result set. Not only because you can get error because of unexpected SELECT, but you can also miss exceptions from the SQL code this way.

jiajun su 1 Reputation point

2022-12-29T07:03:49.797+00:00

hi, expert：
we upgrade it to sqlserver 2016 alwayson from 2005，and it is normal for us to test in a single instance(2005 and 2016),but when we test in 2016 alwayson,it issue java.sql.SQLException: Output parameters have not yet been processed. Call getMoreResults().

1) Different code in the different databases.

2) Different data in the different databases.

-- the same code and data,just different is single instance and alwayson.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-12-29T22:04:09.157+00:00

the same code and data,just different is single instance and alwayson.

Or so you think. I've been answering questions in forums for many years, and it has happened more than once when users have been dead sure that X is true, but in the end it has turned out that this was not the case.

Script the procedure from the three instances to file and then use a diff tool to compare them. You may be surprised.

If they indeed are the same, please share the code here, so we can look at it. Else there is no way we can say anything than we have so far.

Then again, if your real problem is the JDBC error, the solution is what the error message says. Call getMoreResults until it returns false. (I assume that how it works. JDBC is not an API I have worked with, but I've worked with many others. And they tend to follow the same pattern.)

Share via

sql server alwayson Returns the result difference from singel instance

2 answers

Your answer