Errors containing "Embedded SQL error" and "SQLSTATE = 01000" or "ODBC connection lost" in Confirm

Products affected: Confirm®
Product feature: Application Infrastructure

Issue

In Confirm® you receive an error message similar to the following:

Error message: Embedded SQL error:
Executing SET QUOTED_IDENTIFIER ON
SQLSTATE = 01000
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Error number: 2
Object: nb_transaction
Function / event: sqlok
Line number: 32
Software version: V14.10a.AM
Client name: <CLIENT NAME>
Licence number: <LICENCE NUMBER>
Data set: CONFIRM
User Id: <USER ID>
User Name: <USERNAME>
Date: 02/07/2014 14:02:43
Details: Y <DETAILS>

You may have been confronted by the error message shown above (or a slight variation thereof) upon trying to retrieve or update data in the Confirm Client application. This specific error would indicate that the ODBC connection has been lost between the Confirm Client application and the database.
 

Cause

The SQLSTATE = 01000 part of the error shows that the ODBC connection between the client and the database has been lost. There are a number of reasons the ODBC connection between the client and the database might be lost and, in most cases. this will be due to internal network infrastructural factors so, as such, does not indicate an issue with Confirm itself. 

In the particular example in this article the network and database were functioning as they should, however the user had started an database update, or query, then left the connection idle for an extended period of time. This was evident in the logs:

2014-07-02 11:01:16.839    da_section_future (vb_DataWindow) - Retrieve Start    
2014-07-02 11:01:16.852    da_section_future (vb_DataWindow) - Retrieve End, 0 rows     
2014-07-02 11:01:35.186    Activated window Selected Feature (wa_feature) 
2014-07-02 14:02:41.518    Activated window Selected Feature (wa_feature) 
2014-07-02 14:02:43.211    Activated window Selected Feature (wa_feature) 
2014-07-02 14:02:43.225    db_entity_modify (nb_DataStore) - Update Start 
2014-07-02 14:02:43.226    db_entity_modify (nb_DataStore) - Update End   
2014-07-02 14:02:43.226    SQL - Executing SET QUOTED_IDENTIFIER ON
2014-07-02 14:02:43.277    Error - Embedded SQL error:~n Executing SET QUOTED_IDENTIFIER ON~nSQLSTATE = 01000~r~n[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).  
2014-07-02 14:02:43.336    Activated window Enterprise Infrastructure Management System Application Error (wa_errorbox)
2014-07-02 14:02:46.825    Activated window What were you doing when the error ocurred (wa_error_box_comments)   
2014-07-02 14:02:55.373    Activated window Enterprise Infrastructure Management System Application Error (wa_errorbox)

 
Note the considerable gap in time between the first Activated window Selected Feature (wa_feature) at 11:01 and the update attempt at 14:02. The extended time in an idle state has caused the database connection to time out (as per the organisation's local database settings) and the error was thrown when the update attempt was made.

Resolution

UPDATED: August 12, 2019
In conclusion, if the user is confronted with an error such as the one in the example above, or an error stating problems with SQLSTATE = 01000 or the ODBC connection, the user should log off then log back on and retry the action that resulted in the error message.

Please check with your DBA for any potential problems with the Confirm database at around the time of the error. Also check with your Networks team whether there were any issues that could have affected the connection between the computer or server running Confirm and the server running the Confirm database.

Finally, log off and back onto Confirm and re-attempt the action that caused the error message to be thrown. In all likelihood the same error will be continuously thrown (with slight differences in specifics such as the executed function "Executing...") until the user logs off from the system and then logs back on.