BEX error when trying to connect to AX

Today we encountered a problem on our dev environment when trying to connect to AX. 

The error in the Eventviewer: 

Fault bucket , type 0
Event Name: BEX
Response: Not available
Cab Id: 0

Problem signature:
P1: Ax32.exe
P2: 5.0.1500.3761
P3: 4cd58bc2
P4: Ax32.exe
P5: 5.0.1500.3761
P6: 4cd58bc2
P7: 006172e8
P8: c0000417
P9: 00000000
P10: 

Attached files:

These files may be available here:
C:\Users\xxxx\AppData\Local\Microsoft\Windows\WER\ReportArchive\AppCrash_Ax32.exe_d55e154f46603fcd51ce8f1cc09170939b3699_2d75a946

Analysis symbol: 
Rechecking for solution: 0
Report Id: 1fb4056b-b90c-11e4-8361-005056b2000b
Report Status: 0

To fix there are some options, but the first one won't work on AX 

Option 1: 

• my computer
• go to properties
• click on advanced system settings
• under performance, click settings
• click the Data Execution Prevention tab, and then click Turn on DEP for all programs and services except those I select.
• click Add, browse to the executable file for the program and add it. 

Option 2:

In order to be able to start AX, you need to start CMD as admin and then execute the following command: bcdedit.exe /set nx AlwaysOff

Using wild cards in X++ select statements

Some time a ago I had to make a select statement, excluding some batchId's which started with BN14.

In a select statement in X++, you can use wildcards like this; 

    while select inventBatch
    where inventBatch.inventBatchId like 'BN14*'


However, if you'd like to exclude a set of batches which start with BN14 you need a slight different statement:

    while select inventBatch
    where !(inventBatch.inventBatchId  like 'BN14*')

Rebuild indexes

And another small post for the archive. If you'd like to rebuild an index (due to fragmentation or growth), you can use the following statement: 

USE [namedatabase]

ALTER INDEX ALL ON [dbo].[nametable] REBUILD;

Deploy reports AX 2012

Another small post for my own archive. To deploy a report, you can use the following command in powershell

Publish-axreport -servicesaosname 01@nameAOS -servicesaoswsdlport 8101 -reportname PurchPurchaseOrder

Inventsum recalculation

Due to different kinds of problems, it can be needed to recalculate the inventsum for an item. The cause is most of the time incorrect manipulation of the inventtrans records (due to manual adjustments or for example incorrect software modifications). 

There's the possibility to run the consistency check which will recalculate the onhand stock for all items. This is an operation which can take a serious amount of time. If you'd only want to recalculate for one item: 

InventSumRecalcItem InventSumRecalcItem;
; 
InventSumRecalcItem = new InventSumRecalcItem("itemnumber", true, checkfix::fix);
InventSumRecalcItem.updatenow();

Shrink logfile dynamics AX

Just a small post for my own archive. I ran into a problem of not having enough space left after deleting a company in AX (the logfile became too big) in my own testenvironment (recovery model is set to simple).  

Shrinking did not solve it. I needed a full backup first, but there's the problem again, no diskspace left :). There appears to be a backup faker for both the db and the db log and after running it, you can shrink the database. 

alter database "dbname" set recovery full

GO

backup database "dbname" to disk='nul'

GO

backup log "dbname" to disk='nul'

GO

alter database "dbname" set recovery simple

GO

And finally Shrink and the logfile is back in proportian. 



Edit: a small update, after a restore of another database, in order to be able to log in, use this script:

UPDATE dbo.UserInfo

SET networkdomain='domain',networkalias='networkname', sid='sidid'

WHERE id='admin';

Power Query and Dynamics AX

On May 28th, Microsoft released the latest version of Power Query. This is a free light version BI tool and you can attach a variety of datasources including SQL server. So I decided to link it to my to see if I could create a small report to be able to take a look at the usability.

First of all, you can download Power Query here and it is compatible with Excel 2010 and Excel 2013.

After installing it, you'll have a new add-in called Power query. You can get your data for the web (search through wikipedia for instance), files and datasources. For AX I'll choose From SQL Server database:

All you need to do is to enter the name of the server (on which SQL server is installed) and optional is the database name. If you do not enter the database name, you can select any database found on the entered server. 

As you can see below, the tables are on the right. A cool thing is the preview when hovering the table, a not so cool thing is the lack of ability to search for a table name. You cannot search or type the first couple of letters to quickjump to the table you'd like. In this example, I'll link SalesLine and SalesTable and my objective is to get an overview of sales and cost price per week in 2014 on sales order which have been fully invoiced.

So tick the option 'Select multiple items' and select SalesLine and SalesTable. On the Load Settings infopane, select Load to data model. You'll need to do this to create relation(s) between tables.

To create a relation, click to Merge button in the ribbon bar. Select your tables and select a field in both tables to create a relation. If you'd like more than one relation, hold the Control button and click the next to fields to relate. Hit enter to open the Query editor. 

Click the Choose columns button to include the field you'd like, since AX holds all information for all companies in one table, you'll probably want DataAreaId to be included in every query. 

In this example, I've choosen 7 fields and included dataareaid. Here I'll put a filter on the field as can be seen in the screenshot below. Every action you take is recorded on the right, there you can remove an action, or alter it if needed. I like this part, it's very userfrienldy.

You can create custom columns using formula's. In this example, I'll want the weeknumber from the CreatedDateTime. So insert a column and add the formula: =Date.WeekOfYear([CreatedDateTime]).All the formula options can be found here. Besides that, I created a new column CostPriceCustom and mulitplied SalesQTY and CostPrice.

Last two thing is to filter CreatedDateTime to This Year and SalesStatus to 3 (invoiced). So all is set, hit Apply and Close and the data is loaded in Excel. 

Now all that needs to be done is to create a simple PivotTable and from it a diagram it shows my costs and revenues per week in the current year:

You can save the worksheet. When reopening, you'll have the same data. In order to refresh it, go to the Data tab and refresh and if you use a pivot table, refresh that as well. 

My verdict: 
Pro:
- It's free!
- Quick to install and setup
- Easy to use

Con:
- The query screen is light weight, no dragging and dropping, linking is very basic and difficult to find your table. 
- Refreshing is basic. I'd like a more powerful option here, like scheduled refreshing and being able to quickly overview the tables / queries which can be refreshed.