Creating offline database for retail POS unit

When creating an offline database for a POS unit using the Retail Channel Configuration Utility I was met with an error saying “An error occurred while creating the offline database”.

An error occurred while creating the offline database

I could say positively that access rights and such were more or less correct since I could see the offline database popping up shortly when looking through the Management Studio. Googling a bit I found the error described as something with double DataAreaIds on some tables, but I could quickly rule that out.

The post:

http://blogs.msdn.com/b/axsupport/archive/2012/05/14/ax-2012-retail-creating-offline-database-fails.aspx

So looking in the log-file (it is found in the folder where the Retail Channel Configuration Utility is installed) I found errors like this:

Error: 0 : EmbeddedInstall: CheckDatabase failed:System.Exception: CheckDatabase failed while creating database schema:System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable “@nvc_INVENTLOCATIONDATAAREAID”.  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Errors like that could point in the direction of a collation mismatch. So I checked the collation of database I was referring to in the Retail Channel Configuration Utility. It was set to SQL_Latin1_General_CP1_CI_AS and the local SQL server I was trying to create the database on was set to Danish_Norwegian_CI_AS.

Database properties

After reinstalling the local SQL server with the collation set to match the parent database I could create the offline database.

Oh, and while I remember it: You need the SQL Server Express with Advanced Features to make it work.

Advertisements

Enabling index hints is deprecated in AX 2012 … almost

Sending index hints to the SQL server from AX has been around for a long time and often it has not done any good since the SQL server is pretty good at picking the best way it self.

So when it ended up as a parameter in the AOS server configuration in AX 2009 and then removed from the configuration in AX 2012 we seemed clear from the trouble it could cause. Microsoft stated that it was deprecated with AX 2012 and no longer available …

So it seemed a bit strange that the SQL server apparently received the information on a server I was working on recently.

While going through about all possible stuff to locate why it was acting like the non-existing index hint flag was enabled, I was going through the registration database to compare it against an AOS we knew was working as expected. And there it was … the registration key called “hint”.

I did a bit of research and I was not the only one struggling with this. As it appears there are these values to choose from :

Empty = Index hints are enabled and LTRIM (yes, it is there too) is disabled.

0 = Both index hints and LTRIM are disabled. This has to be the recommended setting.

1 = The same as empty. Does that make sense? No, not really. Anyways …

2 = Index hints are disabled and LTRIM is enabled.

3 = Both index hints AND LTRIM are enabled

 

And just for refreshing the memory: The location of the registration keys are

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\Dynamics Server\6.0

and in here find the right AOS and configuration.

Locate duplicate values (or the opposite) in a table

The way X++ handles SQL statement often lacks a bit compared to what you can do in standard TSQL.

One of the areas is if you want to get a set of records from a table where a specific value in a field only occurs once in the table. You could traverse through the records and compare values as you go along. That could pull down performance significantly depending on the record count. Another way of doing it is to group by the field while counting the records in e.g. RecId. Then traversing this result set and look at the value in RecId could give you either the unique or not unique value depending on what you need.

A better way would be to let the SQL server do some of the work and consequently return as few records as possible. Here is a job that illustrates this and the group by version mentioned above.

AX 2012 queries support the HAVING clause. That can in some scenarios do the same and a bit more elegant than this.

 

static void DEMO_NoDuplicates(Args _args)
{
    CustGroup custGroup;
    CustGroup custGroup2;
    PaymTermId lastPaymTermId;

    info("TRAVERSE COUNT");

    while select count(recid) from custGroup
        group by PaymTermId
    {
        if (custGroup.RecId == 1)
        {
            while select custGroup2
                where custGroup2.PaymTermId == custGroup.PaymTermId
            {
                info(custGroup2.PaymTermId);
            }
        }
    }

    info("USE JOIN");
    
    while select custGroup
        order by PaymTermId
        notexists join custGroup2
            where custGroup.RecId != custGroup2.RecId
               && custGroup.PaymTermId == custGroup2.PaymTermId
    {
        info(custGroup.PaymTermId);
    }
}


Getting SQL scripts from the wizards in the SQL Server Management Studio

Ok, this is not exactly AX but it IS in the category of related to. The thing is that you sometimes needs to take a backup of a database or other stuff on the SQL server and sometimes you need to either do this repeatedly every once in a while, have somebody else trigger it or simply document what you did.

Microsoft SQL Server Management Studio offers a lot of nice UI to handle processes like backup, restore and index manipulation. And it is so much easier to do it here than trying to write it in a query window. But it can be a pain remembering – I mean documenting – every option set and redo these settings every time. So this often forgotten feature in most of these UI elements in the Management Studio could come in handy.

After doing all the setup of e.g. the backup process you can use the Script button in the upper left-ish corner:

The script button

We are now offered 4 different ways of getting our script including short cuts for us to remember. The top three gives us the script in respectively a query window, a file or in the clipboard:

Script to query

Now you can combine multiple scripts into one if you would like to for example backup one database and restore it over another by

  1. Create the script for the backup in a query window
  2. Create the script for the restore in the clipboard and paste it in query window with the backup
  3. Do whatever you need to do with the full script to backup/restore

The 4th possibility is to get the script to be a step in a new job if you need to repeat the script on for example a daily basis. Select the Script Action to Job:

Script to job

From here you just need to set up the schedule and you are ready to go.

The script is added to Step 1 so you can manipulate it or just see what is being executed.

The script is added to step 1

Remember that firing scripts directly on the SQL-server or through other clients can be extremely powerful and dangerous and can have fatal effects on the server and databases. So whatever you do … be carefull out there …

Using “Create document service” wizard results in a method xMLMapUnitId does not exist

And that is – in AX 2012 – absolutely correctly observed by the compiler. The method was present in earlier versions of AX but went missing in AX 2012 and was replaced by the method xmlMapUnitOfMeasureSymbol.

So if you use the wizard on for example on a query using ProdTable you might end up with an error telling you that this.mapPolicy().xMLMapUnitId() does not exist. Replace it with this.mapPolicy().xmlMapUnitOfMeasureSymbol() and you are good to go. It will also tell you that this.axUnitId() is incorrect which also is true. The correct method name is axUnitOfMeasureSymbol().

Once you know it … 🙂

Make your own add-ins in the AOT context menu

When spending a lot of your working day in the AOT you end up with a lot of ideas to tools that could make your development tasks easier, help you do things faster or just automate repetitive tasks.

In this blog post I will show you how to quickly make the foundation for a new add-in. The example will create a record counter that simply returns a record count in the info log when activated from a table node in the AOT.

To do so we need 4 things:

  1. The class that does the actual work
  2. A menu item that triggers the class
  3. A reference to this menu item in the context menu
  4. Few lines of code in a standard class to make sure the menu item only shows when positioned on a table

Let us start up with the class. In this example I have a class that looks like this:

class DemoAOTTool
{
}
private void run(Args _args)
{
    TreeNode treeNode;
    // Check that we are executing from a context menu
    if (SysContextMenu::startedFrom(_args))
    {
       // Get the tree node of the context menu
       treeNode = _args.parmObject().first();
      // Check that we have a hit and that we are on a table element
       if (treeNode && treeNode.utilElement().recordType == UtilElementType::Table)
       {
          // Present the record count to the user
          info(strFmt("@SYS97212", new SysDictTable(tableName2id(treeNode.treeNodeName())).recordCount()));
       }
    }
}
public static void main(Args _args)
{
   new DemoAOTTool().run(_args);
}

That is a very simple example; but to show the concept it should do.

Next step is to create a menu item. There is no fancy stuff here. Just a plain menu item.

In the AOT find the SysContextMenu under the Menus node. This is the menu shown when you right click in the AOT and select Add-ins. Drag your new menu item to the menu to the position in the menu you prefer.

We are almost there. All we need now is to make sure that the menu item only is shown when you right click on a table. To do so find the class SysContextMenu and the method called verifyItem.

What this method does is to look at the menu item and menu item type given as parameters and return a 0 or 1 equals hide or show. The method is structured fairly simple with switches to locate the switch on menu item type matching your above created menu item and in the nested switch add the following case :

case menuitemDisplayStr(DemoAOTTool):
    // Check that we are not on an old node and only one node is selected
    if (this.selectionCount() != 1 || firstNode.AOTIsOld())
       {
          return 0;
       }
       // Check that we are on a table node
       if (!docNode && _firstType==UtilElementType::Table)
       {
          return 1;
       }
       return 0;

And now your done. Test it by right clicking on a table in the AOT, select Add-ins and your new menu item.