How to fix SQL error: "Too many parameters were provided in this RPC request"


Recently we were confronted with a case where we received the following SQL error:


[Microsoft][SQL Native Client][SQL Server]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.

This error occurs if we are running into a SQL limitation which does not allow more than 2100 parameters in a SQL statement.


Usually it’s a Select statement where AX produces OR clauses with too many parameters like

WHERE RecId = xxxxxx OR RecId = xxxxxy OR RecId = xxxxyx …

or IN clauses like

WHERE ID IN (xxxxxx,xxxxxy,xxxxyx, …)


How to find where this select statements are comming from?
In the SQL statement trace log (Administration -> Inquiries – Tab ‘Use’) you will find the stack trace that shows you which method caused the SQL error.


How to fix this problem?
We have to add a forceliterals to the select statement in the relevant method.
Adding a forceliterals should avoid running into the SQL limitation of the 2100 parameters, because the statement is handled differently then.


The related statement could either be an X++ select stament or a X++ Query


X++ Stament

Change code from

select from TableName
to
select forceliterals from TableName

X++ Query

Activate forceliterals on the Query object

query.literals(true);

Comments (1)

  1. Chris Shah says:

    Hi,

    Thanks for insight.

    I have similar error from projectcontractdetails form.

    Tried with above options still no luck.

    Let me know if you want i can send error from log.

    Thanks,

    Chris

    ————-

    It's possible you are experiencing aonther issue not detailed above. Please raise it as an issue through your normal support channels if it is still of concern.

    –Anup