UCMDB SUPPORT TIP : Enable temporary table for database adapter
Sometimes some integration jobs which are using database adapter, such as SCCM adapter and AM adapter, would report an error as below:
com.mercury.topaz.cmdb.shared.fcmdb.dataAccess.exception.AdapterAccessGeneralException : java.sql.SQLException: [mercury][SQLServer JDBC Driver][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. : operation DataAccess Query: Retrieve By Pattern From Changes Source Adapter operation
This happens when the TQL has too many conditions. It would help by joining the target table to a TEMP table.
To do this, please go to Adapter Management (for example SCCM/SMS adapter) > Configuration Files > adapter.conf file and change the following settings:
Then go to Integration Studio, right-click on the integration to select Reload supported configuration for adapter and re-run the integration.
Enabling temporary tables allows the adapter to work more efficiently with the remote database, thus reducing stress on the database and network and also enhancing performance.
To enable temporary table support in General Database Adapter, the following conditions must be met:
• The credentials given to connect to the database, include permission to create, modify, and delete temporary tables.
• Configure the following settings in the adapter.conf configuration file:
Note: Temporary tables are only supported for Microsoft SQL and Oracle.
•temp.tables.enabled=true. Enables using temporary tables to improve performance. Only available when performance.enable.single.sql is enabled (only supported in Microsoft SQL and Oracle). Certain permissions in the database server may be required.
• temp.tables.min.value=50. Defines the number of condition values (or IDs) that are needed to use temporary tables.
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Click the KUDOS star on the left to say 'Thanks'