Binding operation failed to allocate buffer space
So this error has been around for some time, searching online you can find some simple answers to use if you run into this, but no where really explains what’s happening here in detail. Well – I had to look at this myself recently and so here is the detail:
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.
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.
