intermittent error - CAUAJM_E_18601 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

Products

• Autosys Workload Automation

Issue/Introduction

Intermittent errors are seen on Autosys scheduler during job processing.  This started happening after migrating from on-premises Autosys to Azure VM's and Azure SQL Database instance. 

 

CAUAJM_E_18802 Error from SQLExecute() Failed with SQL_ERROR.
[04/08/2024 09:00:00]     CAUAJM_E_18601 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.
[04/08/2024 09:00:00]     CAUAJM_E_18607 Event Server: <AEDB.example.com,1433:AEDB_DV1>  Failed Stored Procedure: <{? = CALL ujo_post_event (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} <<'DV10040foo01',25516,0,1,25514,-1,1712602800,549167,1,0,'','','',1,0,'','Monitored for CREATE'>>>

 

[04/08/2024 23:30:58]     CAUAJM_E_18802 Error from SQLExecute() Failed with SQL_ERROR.
[04/08/2024 23:30:58]     CAUAJM_E_18601 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.
[04/08/2024 23:30:58]     CAUAJM_E_18607 Event Server: <AEDB.example.com,1433:AEDB_DV1>  Failed Stored Procedure: <{? = CALL ujo_sendafm (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} <<'20240408 21305529+0700 DV1_SCH AutosysServerHOstname 25549.549172_1/WAAE_WF0.1/MAIN STATE Monitor Status(Monitored for CREATE) SetStart JobLogId(E9048668CEB6D79DF9EB663A4C8A633C30355A10) Plugin(filemon)  User(AutosysServerHOstname) Host(AutosysServerHOstname.example.com)',1,1712655058,2,57073,0,33,'AutosysServerHOstname AutosysServerHOstname.example.com','AutosysAgent.example.com',24533,1>>>

Environment

Workload Automation Autosys in Azure SQL environment 

Cause

Queries without ConnectRetryCount on the SQL Server Driver level is not being handled properly 

Resolution

Microsoft Support recommended that, we can configure ConnectRetryCount in Autosys configuration files.

1) Backup %AUTOUSER%\config.%AUTOSERV% file

2) Open %AUTOUSER%\config.%AUTOSERV% file to edit

3) Find MSSQLDriverConnString variable, this should be add ConnectRetryCount=0 to the string as shown:

If the MSSQLDriverConnString is set as {ODBC Driver 17 for SQL Server} as: 

MSSQLDriverConnString={ODBC Driver 17 for SQL Server}

change it to 

MSSQLDriverConnString={ODBC Driver 17 for SQL Server};ConnectRetryCount=0

Note: Semi-colon (;) and variable name spelling are important.

4) Save the file

5) Restart Autosys Application Server, Scheduler services.  

 

Monitor for more occurrences of the below error, they should not happen anymore:

CAUAJM_E_18601 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.