AX to SQL data dictionary synchronization issues
One of my clients restored their AX database from PROD to DEV environment for AX 2009 and on database sync following error message was thrown Cannot execute a data definition language command on (). The SQL database has issued an error. Problems during SQL data dictionary synchronization. The operation failed. Synchronize failed on 1 table(s) This error message does not tell you which table is failing during sync, when I checked from event log I found following error logs which tells you table name but error message was totally misleading. “Object Server 01: The database reported (session 5 (#####)): [Microsoft][SQL Native Client][SQL Server]There is already an object named ‘<TABLENAME>’ in the database” “Object Server 01: The database reported (session 23 (#####)): [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table ‘<TABLENAME>’, because it does not exist or you do not have permission.. The SQL statement was: “DROP TABLE <TABLENAME>” NOTE: In my case it was MarkupTrans table Now, let’s play around and try to find the exact issue and resolve it. Take table properties in AOT and check ID – For me it was 30088 Open SQLDictionary table from SQL Management Studio and filter it with your table name, put ‘Markuptrans’ in name column. TableId column there will tell you the ID for MarkupTrans table, for my case it was 30086 which is different as shown from AOT You need to change TableID value in SQLDictionary table same as we have in AOT – I changed it to 30088. You cannot change directly this TableId in SQLDictionary Table, either you can update it from SQL server management studio or make TableId field in SQLDictionary Table editable by changing its property AllowEdit = YES. You MUST have to change it back to previous state after updating tableId. You can also check IDs for all fields in AOT for your table, these must be same as we have in SQLDictionary Table. Synchronize your application again – it will be all good and will not throw such errors
Dynamics AX “7” Insights
http://www.bdosolutions.com/ca/insights/microsoft-convergence-update-part-3-dynamics-ax-roadmap-and-strategy-microsoft-dynamics-ax-7-0/
Error when validate settings in Report servers by any admin account which is not account used to install the AX reporting services extensions.
First thing you need to verify the Service account and Execution Account are properly configured under Reporting Services Configuration Manager. After some troubleshooting, I found UAC was not turned off. I was running on MS Windows Server 2012. UAC has to be turned off via registry by changing the DWORD “EnableLUA” from 1 to 0 in “HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionpoliciessystem”. You will get a notification that a reboot is required. After the reboot, UAC is disabled. After UAC is disabled, the issue is resolved. social.technet.microsoft.com/…/13953.windows-server-2012-deactivating-uac.aspx
How to create Info part and use it on List page
This post is about how to create info Part in AX and use it on list page. For this example; I am using Projects List page and will add Revenue Info Part on it. Right click on AOTPartsInfo Parts and create New Info Part Assign Query to this info part I have assigned ProjTable_NoFilter query Add New Group under Layout Name it ProjectRevenue and set few properties as shown Note I haven’t assigned DataSource to this group, I will be using data methods to get data here. Add new fields under ProjectRevenue group and name it RevenueAtSales and RevenueAtOrders I assigned ForecastRevenueAtSalesSw data method to field RevenueAtSales, this data method is written on at Data DictionaryTablesProjTableMethodsforecastRevenueAtSalesSw display AmountCur forecastRevenueSw() { ProjForecastOnAcc projForecastOnAcc; real total; // Sum all the prices with quantity of 1. select sum(SalesPrice) from projForecastOnAcc where projForecastOnAcc.ProjId == this.ProjId && projForecastOnAcc.Qty == 1; total = projForecastOnAcc.salesPrice; // Support non-unity quantities. while select SalesPrice, qty from projForecastOnAcc where projForecastOnAcc.ProjId == this.ProjId && projForecastOnAcc.Qty != 1 { total += (projForecastOnAcc.salesPrice * projForecastOnAcc.qty); } return total; } Add this Info part to ProjProjectsListPage as I want to see this on Project List page. Right click on Parts node in ProjProjectsListPage Name it FF_ProjectRevenue Set IsLinked Property to YES, this property will link this part to the list page based on Datasource (ProjTable) Create new display menu item and name it FF_ProjectRevenueMenuitem Assign this menu item as Part property in ProjProjectListPage Time to see this info Part on List page, Open All Projects form and here it goes Happy Daxture!ng
Cannot open the datafile file axapden-us.alt
Faced following error after restoring Dynamics AX databases from one AOS server to other. Cannot open the datafile “C:Program FilesMicrosoft DynamicsAX60ServerDAX2012R3binApplicationapplStandardaxapden-us.alt” Error code from the openrating system: errno = 13. Look in file ‘errno.h’ or in the operating system documentation for an explanation. Resolution: It was the account permssion issue. Make sure AOS service account must have added into Dynamics AX databases with full permissions (at least db_owner). This AOS service account manages the files located at above mentioned path in error. In my Case; Source AOS service was running with domain user account (DOMAINAOSService) and Destination AOS service was running under Network Service account. When I restored AX databases from source to destination it overwrite all user accounts in destination database, which means it deleted Network Service account. I had two options EITHER to add Network Service account which already has all the permissions as AX was installed from this account as mentioned above OR I have to provide full permissions to the domain user account (DOMAINAOSService) in destination Dynamics AX databases.
Export Import Model – Power Shell & Command line statements
I myself often require following commands and worth to have these at one place to get it more quickly. Export a Model File Windows PowerShell Export-AXModel -Model <name> -File <Filename.axmodel> Example: daxture is a model name and it exist in C drive Export-AXModel -Model daxture -File C:daxture.axmodel Export-AXModel -Server SERVERDB -Database DataBaseName –Model “Model Name” -File c:tempxxxxx.axmodel AXUtil Command axutil export /model:<modelname> /file:<filename> /verbose Example: daxture is a model name and it exist in C drive axutil export /model:“daxture” /file:”C:daxture.axmodel” /verbose Import a Model File Windows PowerShell Install-AXModel -File <Filename.axmodel> -Details Example: daxture is a model name and it exist in C drive Install-AXModel –File C:daxture.axmodel -Details Install-AXModel -Server SERVERDB -Database DatabaseName -File c:tempxxxxx.axmodel -DETAILS -Conflict Overwrite In case of conflict we can use this command; Intall -AXModel -File <Filename.axmodel> -Details -Conflict Push AXUtil Commandaxutil import /file:<filename> /verbose Example: file exist in C drive axutil import /file:”C:daxture.axmodel” /verbose AxUtil commands Creating a model in layer axutil create /model:”daxture” /Layer:CUS Delete a model axutil delete /model:”daxture” Delete a layer axutil delete /layer:ISV Delete a layer from database and AXServer axutil delete /layer:ISV /db:<database> /s:<server>
Calculate timing in X++ operations [WinAPI::getTickCount()]
While reading some lengthy operations e.g. reading data from CSV files, fetching data in query and uploading data etc I wanted to know the time consuming in each opertion. This helped me to optimize my logic or query in making client-server calls. We can use following function to know about time consumption; int startTime, endTime; startTime = WinAPI::getTickCount(); [Perform X++ operations] endTime = WinAPI::getTickCount(); info(strFmt(‘It took %1 minutes’, ((endTime – startTime)/1000)/60)); Note: WinAPI::getTickCount() tells time in milliseconds you need to change accordingly as per your required unit.
Building query AND using query object in X++
Create and add datasource with range in X++ // Code using X++ to build the query Query query; QueryRun queryRun; QueryBuildDataSource qbds; ProjTable ProjTable; ; query = new Query(); // Add a datasource to the query qbds = query.addDataSource(tableNum(ProjTable)); // Add a range to the newly added datasource. qbds.addRange(fieldNum(ProjTable, ProjId)).value(“00403_1036..00412_1036”); queryRun = new QueryRun(query); while(queryRun.next()) { projTable = queryRun.get(tableNum(ProjTable)); info(projTable.ProjId + “, ” + ProjTable.Name); } Use query object to retrieve AOT query // Code using a query string static void UseAOTQuery(Args _args) { Query query; QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; ProjTable projTable; query = new query(queryStr(ProjTable)); queryRun = new QueryRun(query); while (queryRun.next()) { projTable= queryRun.get(tableNum(ProjTable)); info (strFmt(“%1 – %2”, ProjTable.ProjId, ProjTable.Name)); } }
Conditional IIF Sum in SSRS report at group level
I got a requirement where I want to show sum amount based on differnent conditions. Simple Sum of amount can be shown with following expression; =Sum(Fields!Amount.Value) I tried following expression to show conditional sum on a group level; It did not work 🙁 =Sum(IIF(Fields!name.value = “Standard”, Fields!Amount.value, 0)) I tried following expression after getting idea from this blog and it worked =Sum(VAL(IIF(Fields!name.value = “Standard”, Fields!Amount.value, 0)))
Use ReportItems in SSRS report
Sommetime we require to use values from textboxes in SSRS for some calculations or data hiding and some other manipulations. Textbox or any other object on report design is an item of the report and can be accessed through ReportItems!TextBox.value This can be used as on textbox expression =ReportItems!Amount.Value – ReportItems!Amount1.Value