A practitioner’s musings on Dynamics 365 Finance and Operations
Exception has been thrown by the target of invocation in dynamics Ax.
I got this error on my VM. On closely examination, I found that In AOT Data dictionary is not synchronize with Sql Server . I perform following steps to resolve this error
• Right click on data Dictionary and synchronize the data Dictionary.
• Right click on AOT and compile it, it step take longer time. In the case of any error in any class, fix the issue and compiled.
• Generate full CIL.
Recently one of my assignments, I have to explore and use Cross company queries to fetch data. Although cross company queries have performance overhead, for example, if my custom AIF service return result in 4 seconds , Cross company returns in 30 seconds. I found multiple design pattern, The fastest cross company query are by setting allow cross property to set true in static AOT Query or by setting Adding In Query Object. Lets explore these patterns
Static AOT Query:
You can set Allow cross company true By properties of AOT Query .
X++ Query object:
One way to create query in dynamics Ax is Query object. You can add by following way.
Query query; QueryRun queryRun; QueryBuildRange queryBuildRange; QueryBuildDataSource queryBuildDataSource; query = new Query(); queryBuildDataSource = query.addDataSource(TableNum(CustTable)); query.allowCrossCompany(true); //Executes query built so far queryRun = new QueryRun(query); queryRun.allowCrossCompany(true);
This is most fastest way to get cross company data.
While select Query:
There two ways for Get cross Company query and or Query inside the change company statement.
Cross company Keyword:
Add cross company query inside the while select statement to extract data all legal entities in Dynamics AX as follow code.
Custtable _CustTable;
While select crosscompany * from _Custtable
{
Info (_Custtable.AccountNum + “ : “ + int642str(_Custtable.DataAreaId);
}
If you want to restrict the query to fetch data from limited legal Entities you have to use a container as follow.
Container _ListOfLegalEntites ={ ‘mazang’,’Jubergi’,’Urdu Bazar’};
While select crosscompany:_ListOfLegalEntites * from _Cust
{
Info(_Custtable.AccountNum + “ : “ + _Custtable.DataAreaId);
}
Change Company Keyword:
One method is to use change company keyword. It is most time consuming method. To reduce its time, we have rest the table to null when Change Company happened. In Dynamics All legal entities name stored at DataArea table we use this table to get the list of datarea and used inside the changeCompany query on it as follow.
DataArea _DataArea;
Custtable _Custtable;
While select Id from _DataArea
changeCompany(_DataArea.Id)
{
_Custtable= null;
While select * from _Custtable
{
Info(_Custtable.AccountNum +” : “ + _Custtable.AreaId);
}
}
If you did not reset the table buffer , result will be too heavy, reason buffer contains old data as well. To reset or set null inside the change company statement and before query on buffer will be performance bust as well as design pattern.
Usually we have to convert date to UTCDateTime. That is usally case when we have to query on createdatetime filed of very table, which filled when new row is created.
We for this purpose we have to create use DateTimeUtil::newDateTime function. This function takes two values one for date, and second for time. So time values will be range beween 0 and 86400. It means when value time is 00:00:00 value will be 0 and when time is 23:59:59 then value will be 86400. Consider following code which creates start and end date for same date.
Date _Currentdate;
utcDateTime _UtcStartPeriod;
utcDateTime _UtcEndPeriod;
;
_currentdate=today();
_UtcCurrentPeriod = DateTimeUtil::newDateTime(_currentdate,0);
_UtcEndPeriod = DateTimeUtil::newDateTime(_UtcEndPeriod,86400);
What if we have to convert UCTDateTime to DateTime, We can do it like
_currentdate = DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(_UtcEndPeriod, DateTimeUtil::getUserPreferredTimeZone()));
Following links are best starting point for ESB Toolkit.
http://abhijitmahato.wordpress.com/2010/09/10/biztalk-esb-toolkit-2-0-samples-content-based-routing/
http://www.codeproject.com/Articles/293385/Dynamic-message-processing-and-routing-in-Itinerar
http://www.codeproject.com/Articles/288285/Hello-World-in-BizTalk-ESB-Itinerary-using-a-Purch
I found this error, when I just complete the installation of ESB toolkit on my machine. I found that I missed the following Filters on “All.Exceptions” send port
ErrorReport.FailureCode – Exists
Microsoft.Practices.ESB.ExceptionHandling.Schemas.Property.FaultCode – Exists
When I set the these filters on send port. All exception port successfully enlisted and start
While installing ESB portal on 64 bit machine I found following errors
File Management_Install.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details. At line:1 char:2
The reason for this error is the security setting on your pc that does not allow you to execute a script. This is the so-called Execution Policy. By default, the Execution Policy is set to Restricted. This setting means that you may not run any PS1 script at all.
An overview of the policy levels: Set-ExecutionPolicy Unrestricted
But result is same, then its comes in my mind that machine is 64 bit.
I create a new file with cmd as follow and run it with administers.
%SystemRoot%syswow64WindowsPowerShellv1.0powershell.exe Set-ExecutionPolicy Unrestricted
For 32 bit machine at cmd write powershell and enter then just unrestricted policy level
Set-ExecutionPolicy Unrestricted
for 64 bit machine use following string at cmd open with administrator rights.
%SystemRoot%syswow64WindowsPowerShellv1.0powershell.exe Set-ExecutionPolicy Unrestricted
This will solve my problem.
open cmd with accessories with run as administer option.
When command prompot open. Write powershell. And press enter.
Powershell is opened. Now write set-executionpolicy unrestricted. It will run successfully.
After hundreds of time failure on BizTalk configuration failed at configuring SSOS with error
Failed to connect to the SQL database SSODB on SQL Server
The best solution I found was to re-register the ssosql.dll on the following manner:
1. start the visual studio command prompt
2. go to the ssosql.dll C:Program FilesCommon FilesEnterprise Single Sign-On
3. regasm ssosql.dll
and probably you are finished.
On 64 bit machine somehow or other regasm did not gac the ssosql.dll, when checked I assembly folder it contains old dll, so on 64 machine you have to regac ssosql.dll manually.
I decided to write a simple kick start tutorial for WCF-SQL Adapter for beginners.
In this tutorial where I just perform single operation insert. A simple BizTalk solution which read a value from file location and insert this value into table.
First of all I create a very simple category table. Which hold two fields. As
USE [Experiments]
GO
/****** Object: Table [dbo].[CategoryWar] Script Date: 03/25/2012 22:12:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategoryWar](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_CategoryWar] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Now I create a new solution with Name WCF_SQLAdapter. You can use as you like.
On solution explore, I right click on add and then select generate items.
Then select consumerAdapterServices as follow.
Select SQLBinding from Consume Adapter Services Wizard. Like as
Then click on Configure Button new popup windows like that
Inter the Sql server Name. And Database Name in Initial Catalog. And press save button. Now URI look like as
Click on test button to verify the connection established. If connection is successful. Then Category section will below the connected button will populate as select from table all possible option appears at right side as follow.
Some Schemas and bind files generated as follow. Open schema name with
TableOperation.dbo.CategoryWar.xsd
Now we create one more schema, which will used as input to BizTalk solution. Like as
Now I create a map for send value from input schema to Request input message.
I used source input schema. And output schema I used to insert schema from TableOperation.dbo.CategoryWar.xsd. Map will be like
After that I create new Orchestration with name InputProcessOrchestation.odx.
I create three message with One for input message, and two with Insert Request Method and one for Insert response. Orchestation will be look like as
It is very simple orchestration, which reading input from file location transform it into request message. And request response call to WCF-SQL adapter and received message drop in other location.
For wcf-sql call I created send request port with specify later option. Remember the name of operation will be the same which will describe in binding. You can later change it. When I deploy the solution I found three ports on BizTalk console. I created two file ports one for receiving input and second for droping output. I import the binding which is created at the time of consuming the SQL Adpater. It will as
Generate instance form input schema and modify it and drop. This solution is works fine for me. You can download solution from here.
