SQL statement caused blocked and looping JWPs : The server supports a maximum of 2100 parameters

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

If a complex filter search filter is applied against the process monitoring and the resulting query contains more the 2100 bind parameters this may block the JWP.

In the JWP trace file the following message is found:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Environment

Release : 12.3  Component : AUTOMATION ENGINE

Cause

The message "Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request." is passed from the JDBC driver, and is a limitation in the design of MSSQL Database.

If the user wants to have this limitation changed, he should contact the Vendor of their database.

 

Resolution

An improvement has been released with 21.0.3 that consists in:

-- A forced trace is no longer written when the problem occurs.

-- A message will pop up in AWI recommending to reduce the complexity of their search filter.  This message is returned when the number of bind parameters is too high (i.e. higher than 999):
U00045446 The query filter is too complex. Reduce the number of parameters and refresh the list.

This will also be implemented with the release of 12.3.9

Additional Information

The problem is a third party problem, specifically related to the MS SQL Database, it is not due to the Automation Engine or to the Automic Web Interface.