Products affected: Confirm®
Product feature: Application Infrastructure
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.
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.
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, 2019In 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.
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.