Intermittent ODBC error "SQLSTATE: 42000, Native error: 4,083

Question

"SQLSTATE: 42000, Native error: 4,083, Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The connection was recovered and rowcount in the first query is not available. Please execute another query to get a valid rowcount." error intermittently. 

This is seen from the stored procedure call, we use ODBC from our C++ application to invoke the stored procedure and we have statements like "select @error_var = @@error, @rowcount_var = @@rowcount" in our stored procedure after DML statements to validate the Update/Insert/Delete query, as you can see sometimes the SELECT statement shown above returns and Native Error 4083 and our process fails to complete the transaction.

• Is there a known issue in MSSQL Server causing error 4083?
• Why does the SQL server return 4083?
• what are the possibilities for SQL Server to return 4083?
• Is there a standard procedure to collect the traces from the SQL Server machine to identify the causes for error 4083?
• Since this problem is seen in non-QA environments, is there an easy method to identify the root cause for this problem?

Answer 1

I don't think the rowcount in the error message has any relation to the SELECT statement where you read @@rowcount in the procedure.

Connection recovery relates to a connection that was lost and then recovered. If the client API starts to ask about a row count, I guess it could get this message.

My gut feeling is that the root cause is a network issue, but I will have to admit that I very much out on a limb here.

As for tracing exceptions, you can do this with extended events. I have an article on my web site that describes how to do this, and which also provides a view to read the result from the session: https://www.sommarskog.se/Short%20Stories/trace-exceptions.html