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

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.
 

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.