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,
