Binding operation failed to allocate buffer space - Dynamic AX 2012

Hi,

QueryRun and Query Objects - Binding operation failed to allocate buffer space

So if you ever work with the QueryRun and Query objects within X++, and I mean work with them a lot. You might come across an issue.

If for some reason, your start to see the following in thrown errors or in the event viewer, Application event log on the AOS, then you have an issue most likely with the QueryRun and Query Objects.:

Binding operation failed to allocate buffer space

The issue that your facing is the query that your creating. The query your creating, to make use with your QueryRun object is causing the AOS to hit a buffer limit. You can increase the buffer limit at the AOS. But most likely what you will need to do is force literals for the query your building in X++.

It's simple to do, after your create an instance of the Query object simply do a:Query.literal(1); call.

This will 'force the hand' of the AOS to accept the buffer allocation requirement, and allow your advanced query to run. Therefore allowing your QueryRun operation to finish as desired.

Well that's all for now. Ran into this, and thought it would be worth putting out here. If anyone has ran into this before, and wants to share feel free to leave a comment. Also if you have more details about literals and the use as described above, feel free to share that as well.

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 coming 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 statement 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);

Error Message (17:05:20) Stack trace: Binding operation failed to allocate buffer space.
(C)\Classes\QueryRun\next
(C)\Jobs\Job4 - line 29

This relates back to the maximum buffer size set in the database tuning tab of the server configuration utility. This buffer is used for binding of input and output parameters for SQL statements. So for example:

Select * from MyTable where field1= @p1 <- this is the input parameter

Ax declares input parameters based on size of the field, rather than on the size of the value being passed. The size of the value used is the total length of the field plus one. The default buffer is 24576 bytes. Each character is allocated 2 bytes. This equates to a maximum of roughly 12288 characters length of input parameters. It is also necessary to be mindful of the RPC upper limit of 2100 parameters, so it is not possible to have more than 2100 input parameters, or the error below will appear:

SQL error description: [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 relationship between buffer size, field length and the number of input parameters can be expressed as below:

MaximumBufferSize / ((FieldLength + 1)*2) = NumberOfPossibleInputParameters

To avoid this limitation altogether it is possible to set the query to use literals, this will mean that input parameters are not used, and therefore the buffer limit does not apply.
More information on the RPC error is available here:

Note: please do not confuse the maximum buffer size in the AOS configuration utility (which applies here) with the maxBufferSize registry key, they are unrelated.

Regards,