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,

what is the difference between index and indexhint in select statement in Dynamic AX

Hi,

While fetching data from the database we use select statement and for better performance in data fetching we use an index/indexhint.

Index: when we use index in select statement it implies that the declare index field should behave as an order by and it is optional for the database to use. Database can use its own preference.

Index hint: it force the DB to use that index for fetch the data whether it is right choice or not

Regards,

To take complete back up of SQL in single code

DECLARE @name VARCHAR(100)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'G:\BACKUP'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' +REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

AX2012 R2 Retail installation, DB profile, AOS profile and Commerce data syn service setups

Hi,

1. Architecture of the Microsoft Dynamics AX Retail 2012 R2 

We need to understand the modify architecture of Ax2012 R2 retail.  

Main Components

1.      Retail Headquarters.

2.      Commerce Data Exchange: Real-time Service (previously known as transaction service in Ax2009)

3.      Commerce Data Exchange: Synch Service.(previously known as Store connect in ax2009)

4.      Retail POS.

5.      .net Business connector.

Helping Component to do the configuration:

1.      Retail store database utility.

2.      Retail SDK.

3.      Retail online channel.

Open the Ax2012 R2 setup file and run (Before that Ax2012 R2 basic application should be install in your m/c).

Provide the name of the store DB and configure.

Same way do it for offline store DB.

Error: While configuring synch service you might get the following error.

Solution: Don’t configure the Synch service in setup time do it manually after successfully installation of all the retail components.

Go to the Programs>MicrosoftDynamicsAx2012> Retail store database utility

Open it and provide the below information to configure the store.

Don’t try to configure the offline DB after this because it will throw you an error.

Error: Store can’t not be found.

Reason: There is no data at this moment in the Store DB.

Configure the Synch service.

Go to the Programs>MicrosoftDynamicsAx2012> Retail synch service setting.

When you click add the server, it will prompt with Create a message database.

It will ask the database location and credential.

After successfully create of message DB, start the service and do the below setup.

Check that all the services are running stage or not.

Error: Sometime the Synch service shows starting stage and it remain unresponsive You can’t stop/start the service from here.

Solution: Go to the Task Manager and check the Service Processor ID.

Stop it from here and start.

Open Ax2012 R2 application do the following setup:

1. GO to the Retail>setup>Retail Scheduler>Channel integration.

Open AOS Profile

2.Open the Database profiles.

3.open the Synch Service.

  

Sometime we will get the following issue while doing test connection check.

 Reason:

Synch service is not started or we are not pointing the right Synch service or firewall related issue.

Check the setup and see the event viewer for more details.

Go to the System Administration>setup>Initial checklist>

 Go through all the step, main important is the schedule redeployment.

If it fails to insert certain data in some specific tables of Retail Store db, then we need to do it manually.

Go to the Retail>Periodic>Distribution schedule

Run the N-jobs

3 mandatory jobs need to be run 1st

1. N-1095

2. N-1090

3. N-1070

Then what are N- jobs you want to run ,run directly.

 Please check the below status once run the job.

Go to the Programs>MicrosoftDynamicsAx2012> Retail store database utility Open it and provide the below information to configure the store.

Configure offline DB.

You are ready to open the POS.

To configure the real time service you can follow the below link, it is quite helpful.

http://blogs.msdn.com/b/axsupport/archive/2012/12/18/ax-for-retail-2012-r2-installing-the-real-time-service.aspx

But I like to add some common troubleshooting on top of the above post which I faced while creating the web service.

 When you open the windows power shell open with Administrator and never use x86 power shell. Otherwise unable to install the Script in powershell, everytime it will fail.

 Enter Set-ExecutionPolicy Unrestricted –Force before the start any installation .\InstallCommerceDataExchangeRealtimeService.ps1

3.                 Be careful with Binary source path.

a.     C:\Program Files (x86)\Microsoft Dynamics AX\60\Commerce Data Exchange\Real-time Services\6.2\  \

4.                         ServerCertificateThumbprint: 

        You should remove the spaces from the 40-character string. Every time enter the value manually. Otherwise you will face the issue while opening the web service.

If you successfully install and configure the web service it should open like below.

For any other errors please check the below link.

 http://blogs.msdn.com/b/axsupport/archive/2012/12/31/ax-for-retail-2012-r2-troubleshooting-the-real-time-service.aspx

  Configuring the Real-time Service in Retail Headquarters

Now that we have confirmed the Real-time Service Web service is working correctly, we need to configure it in Headquarters and push it down to the POS.

In Dynamics AX navigate to Retail > Setup > Retail scheduler > Channel integration > Real-time Service profiles.

As in previous versions, the Real-time Service profile is assigned to a specific register. Navigate to Retail > Setup > POS > POS registers and open a register. The profile you created should be assigned under General > Profiles:

Basic functional setup:

1. Do the setup of the Address book and a warehouse which is link with a store.

2.      Go CEU/Human resources/Common/Workers/Workers

Create your own worker.

Enter the Address books.

Go to the Retail Tab

Do the below mandatory setup.

Set the POS permission.

  

3.      Go to the retail store and create your own store.

CEU/Retail/Common/Retail channels/Retail stores

Add the worker in the store through employee address book.

Do the Setup of the Distribution Location under Retail>setup>retail scheduler 

Dynamics AX 2012 Reports services extension components installation-failed

Steps to resolve;

1. Open AOT > Services > SRSFrameworkService 

register it

It will clear or else

2. Open AOT > Services group > BIServices

Deploy it

Open AOT > Services group > UserSessionService

Deploy it

Now it will be cleared at last if not run complete (ful) CIL

AX 2012 Help Server error: Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

Hi,

To resolve this issue, run the following command line: 

aspnet_regiis.exe /iru

The Aspnet_regiis.exe file can be found in one of the following locations:

%windir%\Microsoft.NET\Framework\v4.0.30319

%windir%\Microsoft.NET\Framework64\v4.0.30319 (on a 64-bit computer)

HTTP Error 500.24 – Internal Server Error + HELP SERVER ERROR AFTER INSTALLING ON AX 2012 + ERROR – Unable to contact the server

Hi,

This error may arises at any of this name for same reason.

HTTP Error 500.24 – Internal Server Error,

The Location of the help server has not been specified.

ERROR – Unable to contact the server etc...

HTTP Error 500.24 – Internal Server Error

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

Detailed Error Information

Module	ConfigurationValidationModule
Notification	BeginRequest
Handler	svc-Integrated
Error Code	0×80070032
Requested URL	http://testserver:8080/DynamicsAX6HelpServer/HelpService.svc
Physical Path	C:\inetpub\wwwroot\DynamicsAX6HelpServer\HelpService.svc
Logon Method	Not yet determined
Logon User	Not yet determined

 Solution:

1. Check that Directory browsing is enabled(IIS MANAGER>SITES>DIRECTORY BROWSING(DOUBLE CLICK AND ENABLE IT)
2. Go to Ax 2012>System administration > setup>system>Help system parameters

(check the location should be as

Eg:-  http://ServerName/DynamicsAX6HelpServer/HelpService.svcthe

http://tcschennaiserver/DynamicsAX6HelpServer/HelpService.svc

1. Also when you browse the site in IIS manager it will tell you ASP.NET 4.0 check for Integrated pipeline mode
1. Check the below criteria it should be the same
2. IIS>Application Pools>
3. ASP.NET4.0                                  V4.0       CLASSIC
4. ASP.NET4.0 CLASS                        V4.0       CLASSIC
5. CLASSIC .NET APPLICATION           V2.0       CLASSIC
6. DEFAULT APP POOL                       V2.0       INTEGRATED
7. DYNAMICS AX HELP SERVER          V4.0       INTEGRATED(Version will be 2.0 change to 4.0 and check)  pls keep in mind DynamicAX  HelpSVC >ASP.NET IMPERSONATION SHOULD BE DISABLE(DON’T ENABLE)

Installation of Help Server AX2012

Microsoft Dynamics AX Help is a client and server based system that distributes and displays documentation.

1.       The Help client is the Help viewer application that requests and displays documentation and gets installed with the Microsoft Dynamics AX client application.

2.       The Help server responds to the Help viewer request for documentation. In addition, the Help server stores the files that contain the Help documentation.

Important: This Server-client Help system does not supply Help documentation for Enterprise Portal. You will have to install Enterprise Search to support help documentation for EP.

Other information about Ax 2012 Help server which is good to know:

ü  Typically, you can initiate a help request from either the client or developer workspace by pressing F1 /button / via command.

ü  The client identifies the Help topic to retrieve. To identify the documentation for the form from where Help is initiated, the documentation has an ID property that has the same value as the ID of the form.

ü  The client retrieves the URL of the Help web service. The first time that you request help, the client contacts the AOS to retrieve the URL of the help web service. The client then caches the URL and uses the cached URL for additional help requests.

ü  The client calls the Help viewer. If the Help viewer is not running, the viewer is started. The call to the Help viewer includes the URL of the help server and the ID of the form.

ü  URL can be updated/modified under path AX2012 > System Administration > Setup > System > Help system parameters.

How to Install the Help server:

1.       Start Microsoft Dynamics AX Setup. Under Install, select Microsoft Dynamics AX components.Advance through the initial wizard pages.

2.       On the Select installation type page, click Custom installation, and then click Next.On the Select components page, select Help Server, and then click Next.

3.       Check for prerequisites, When no errors remain, click Next.

4.       On the Connect to an AOS instance page, enter the name of the computer that is running AOS and other port. Click Next.Note that, If you entered AOS information for other Microsoft Dynamics AX components that you have installed on this computer, this screen is not displayed.

5.       On the Configure a Web site for Help Server page, select the web site that you have chosen to host the Help server. Verify that the location of the physical directory for the web site is displayed. Click Next.

6.       On the Specify the Help Server account page, enter a domain user account and password.This account must be the same as the .NET Business Connector proxy account for the AOS, and it must be a user in Microsoft Dynamics AX. This should be a service account that does not expire. Click Next.

7.       On the Language and content selection page, select the Help languages and content types to install. EN-US must be installed, and is checked by default. Click Next.

8.       On the Prerequisite Validation page, resolve any errors.

One possible error which you might observe when you have SharePoint / Enterprise portal already installed in the machine is shown below:

Error: Web site (Help Server) is shown because the Default web site is not started. And this could be because SharePoint installation has taken over the Port 80 and kicked off the Default web site.

Solution: Go to IISManager (inetmgr) and then Edit bindings for the Default site to change the port number from 80 to something else (say 81).

After doing so, you can start the Website. This should resolve the issue shown above.

9.       When no errors remain, click Next.

10.     On the Ready to install page, click Install.After the installation is complete, click Finish to close the wizard.

After the Microsoft Dynamics AX Help files are installed, they must be indexed by Windows Search Service before you can view them. Depending on system load and the number of files, it may take up to an hour for indexing to finish.

More information on how to Install the help server [AX 2012]