Error with too many parameters

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Which IIQ version are you inquiring about?

8.3p3

Please share any images or screenshots, if relevant.

2024-08-09T01:21:03,999 ERROR Thread-187 sailpoint.task.IdentityRefreshExecutor:1649 - RefreshWorker 1 exception: org.hibernate.exception.SQLGrammarException: could not execute statement
sailpoint.tools.GeneralException: org.hibernate.exception.SQLGrammarException: could not execute statement
at sailpoint.persistence.HibernatePersistenceManager.removeObjects(HibernatePersistenceManager.java:1976) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.persistence.ClassPersistenceManager.removeObjects(ClassPersistenceManager.java:303) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.server.InternalContext.removeObjects(InternalContext.java:852) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.api.Entitlizer.removeOrphans(Entitlizer.java:1174) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.api.Entitlizer.finish(Entitlizer.java:771) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.api.Entitlizer.refreshEntitlements(Entitlizer.java:496) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.api.Identitizer.finishRefresh(Identitizer.java:3030) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.api.Identitizer.refresh(Identitizer.java:2491) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.task.IdentityRefreshExecutor$RefreshWorker.refreshAndProcess(IdentityRefreshExecutor.java:1610) [identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.task.IdentityRefreshExecutor$RefreshWorkerPool.queue(IdentityRefreshExecutor.java:1928) [identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.request.IdentityRefreshRequestExecutor.refresh(IdentityRefreshRequestExecutor.java:487) [identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.request.IdentityRefreshRequestExecutor.execute(IdentityRefreshRequestExecutor.java:209) [identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
at sailpoint.request.RequestHandler.run(RequestHandler.java:163) [identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1681) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at sailpoint.persistence.HibernatePersistenceManager.removeObjects(HibernatePersistenceManager.java:1972) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
… 12 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:80) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:50) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:177) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:458) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:377) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1438) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1690) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1672) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at sailpoint.persistence.HibernatePersistenceManager.removeObjects(HibernatePersistenceManager.java:1972) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
… 12 more
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.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:512) ~[mssql-jdbc-12.4.1.jre11.jar:?]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) ~[commons-dbcp2-2.7.0.jar:2.7.0]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:80) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:50) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:177) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:458) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:377) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1438) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1690) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1672) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]
at sailpoint.persistence.HibernatePersistenceManager.removeObjects(HibernatePersistenceManager.java:1972) ~[identityiq.jar:8.3p3 Build d5deab2519b-20230629-092050]
… 12 more

Please share any other relevant files that may be required (for example, logs).

During standard Refresh Identity Cube task I saw in the server logs that there is some error/issue SQLGrammarException. I was looking for that in Sailpoint Community and I found something similar: 'The incoming request has too many parameters." error on remove Access Request - Compass (sailpoint.com). It looks like it is still not resolved in my version. Do you perhaps know anything more about this?

2

I read the community post as well, maybe processing in batches less than 2100 records might work? Do you have logging in sql-side enables if so then can you share that as well?

3

Hi @ASuska,

It can be caused because you are writing a value bigger then the column size or you are using a grammatically incorrect query on SP db.

First of all you can activate the hibernate logs for give more info. Also, you need to revise all the identity attribute you are updating and find the value or values that create this problem.

Whne you find it, you can increse the column size or limit the value can be wirte in this attribute

4

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.