Running the CRT samples on AX7

The RetailSDK delivered with AX7 includes a lot of great sample code making it easier to get things rolling when learning how to customise the solution.

One of the sample projects is the CommerceRuntimeSamples. Before you are able to run it you need to do a couple of tweaks though. If not it will crash and burn with error messages that do point you in the right direction on how to fix it. This blog post is made to safe you some time and getting it fixed without having to go troubleshooting.

The first error that pops up is this one:

A configuration error in the Microsoft.Dynamics.Commerce.Runtime.dll. The reason is that it cannot find the right Operation Unit Id. It is specified in the commerceruntime.config and all you have to do is put in the operating id of the channel you would like to work with. In this case I use Houston, which has the id 039:

Next is this error:

A storage exception in the same dll and same piece of code. This time we have to look in the app.config file. In here we have a list of connection strings that all point at database names as they appeared in AX 2012 Retail. In AX7 demo servers all data for all channels is in the AxDB database so the fix is to change the database names in the connection strings to AxDB:

Now you are able to run the samples and start debugging to learn.

 

 

Retail designer – Creating a database role with database access

Using the designer in AX 2012 for designing POS-buttons, receipts and whatever usually works for us eggheads with admin rights to the servers. But when it comes to the real users they might end up with an error like this:

What you need to do is to grant the users that need to work with the designer some additional rights directly to the Dynamics AX database. Scary stuff and something we’d rather not do; but in this case there’s no way around it. The good news is that it is only a few tables.

Here’s what to do:

Log on to the Microsoft SQL Server Management Studio, right click on the Dynamics AX database and select New Query.

Paste in this script and execute it. It will create a database role with the appropriate rights to the tables

• RETAILBUTTONGRID
• RETAILBUTTONGRIDBUTTONS
• RETAILFORMLAYOUT
• RETAILIMAGES
• RETAILTERMINALCUSTOMFIELD
• RETAILTILLLAYOUT
• RETAILOPERATIONS

and select rights on these:

• RETAILSALESTAXOVERRIDE
• RETAILSTORETENDERTYPETABLE
• RETAILSALESTAXOVERRIDEGROUPMEMBER
• INVENTITEMBARCODE
• INVENTTABLE
• RETAILCHANNELTABLE
• ECORESPRODUCTTRANSLATION

Notice, that the names of these tables may vary from AX version to AX version, but you’ll probably be able to locate the right tables.

The script:

CREATE ROLE [AX_POS_Designer]
GO

GRANT DELETE ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILBUTTONGRID] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILBUTTONGRIDBUTTONS] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILFORMLAYOUT] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILTILLLAYOUT] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILIMAGES] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILTERMINALCUSTOMFIELD] TO [AX_POS_Designer]
GRANT DELETE ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT INSERT ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT UPDATE ON [dbo].[RETAILOPERATIONS] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSALESTAXOVERRIDE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSTORETENDERTYPETABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILSALESTAXOVERRIDEGROUPMEMBER] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[INVENTITEMBARCODE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[INVENTTABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[RETAILCHANNELTABLE] TO [AX_POS_Designer]
GRANT SELECT ON [dbo].[ECORESPRODUCTTRANSLATION] TO [AX_POS_Designer]
GO

Now you’ve got the role. Next step is to create the required logins, map them to a database and the newly created role.

Log in to AX and check that the designer starts up successfully.

Test access to Retail server on The New Dynamics AX

Just a quick heads-up on a feature allowing you to test the access to the Retail server in The New Dynamics AX.

From a browser open the site:

https://<my server name>ret.cloudax.dynamics.com/healthcheck?testname=ping

It shows you a ping test result looking like this:

If you need the results in a more developer-friendly format you can add a &resultFormat=xml which gives you something like this:

 

 

Tracking down deployment errors in The New Dynamics AX

When we started using AXUtil.exe in Dynamics AX 2012 it was a bit scary in the beginning but I consider it easy to use after having spent hours upon hours massaging model files on different installations. It is a bit of a black-box, I know. But still it went from causing sweaty armpits to being easy-peasy handling modelstore updates using AXutil.

Now with the New Dynamics AX we are introduced to AXUpdateInstaller.exe and we are back to dark spots on the t-shirt where the arms meet the torso. But I bet that we will get used to this like we did with AXutil … I hope …

One thing that I have learned the hard way is to not have Visual Studio and the Application View opened on the same machine I am deploying to. That gives an error when the installer tries to unzip the files from the package to the service folders.

The error is something like this:

 <Log>
     <Time>2016-03-02T09:32:43.5149415+00:00</Time>
     <MachineName>mymachine</MachineName>
     <StepID>3</StepID>
     <Message>The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: A positional parameter cannot be found that accepts argument 'Package deployment failed as the extraction of the package zip file failed.'.</Message>
 </Log>

The text is picked up from the runbook file.

So I went through all the normal stuff such as disk space, access rights to the folder, test-expanding the folder and so on. Nothing looked suspicious.

Next step was to look at what step 3 actually does that makes it stop. Going a bit upwards in the runbook file we get the actual contents of each step. For step 3 it is:

<Step>
    <ID>3</ID>
    <Description>Update script for service model: AOSService on machine: AX7SORAS02-1</Description>
    <MachineName>AX7SORAS02-1</MachineName>
    <ServiceModelName>AOSService</ServiceModelName>
    <ScriptToExecute>
        <FileName>AutoUpdateAOSService.ps1</FileName>
        <Automated>true</Automated>
        <Description>update AOS service</Description>
        <RetryCount>0</RetryCount>
        <InvokeWithPowershellProcess>false</InvokeWithPowershellProcess>
    </ScriptToExecute>...

The interesting part is the filename AutoUpdateAOSService.ps1. The scripts run by AXUpdateInstaller for this is located in the deployment folder in AOSService\Scripts.

Using Windows PowerShell ISE you are actually able to debug your way through the script.

When you have located the error you can use AXUpdateInstaller to mark the step as completed and it will automatically move on to the next step.

Troubleshooting the Commerce Data Exchange

In AX 2012 troubleshooting the Commerce Data Exchange is necessary. It’s somewhat a blackbox but we do have a few tools in the belt and handles to pull.

This post describes a couple of these in AX 2012 R2. The following versions are a bit more informative; but some of the tips are still relevant.

 

What’s in the queues?

The communication flow is passed through the SQL server and during installation you are asked for a message box database. When looking into this database two of the tables are showing you the flow of messages and a few related (general) pieces of information. I use the following SQL statements as a standard approach to these tables:

SELECT JobID, Status, TryCount, ErrorNo, ServerMsg, FinishedDateTime, PackageNo, ServiceName 
FROM [RetailMsgDB].[dbo].[IncomingMessages]
ORDER BY FinishedDateTime DESC
SELECT JobID, Status, TryCount, ErrorNo, ServerMsg, FinishedDateTime, PackageNo, ServiceName 
FROM [RetailMsgDB].[dbo].[OutgoingMessages]
ORDER BY FinishedDateTime DESC

/*
delete from IncomingMessages
delete from OutgoingMessages
*/

The result of the query looks something like this:

The first result set shows the messages going in to the server and the second result set shows the outbound. As long as the ErrorNo is 0 you are good. If it does show up with an error you need to start digging a bit. The JobID column shows you in which job to look.

Notice that I have to delete statements disabled by comment-tags. These are nice when you would like to reset the contents of the queue-tables. To execute the you need to mark them (without the /* and */) and execute.

 

Getting info from the log file and packages

The data exchange service offers a couple of tricks regarding its log file and the packages. The level of informations is setup in the Service Settings:

This offers lot of settings for the service but in this post I will only be touching a couple of them.

First, make sure you customize the right service and click Next until you get access to the Working Directory. This is where the files are dumped for processing by the service.

Moving on we can change the settings for the log file.

We have several relevant settings in this part. First of all the directory for the log file and the Log Level. As default it logs the errors only and that is often enough. If you want it to be more chatty you just click in what you want to know from the service. Notice, that the log file does get a bit overwhelming to look at if you click on everything.

Reading the log file can give you a very specific error to work with. With a field mismatch it could look a bit like this:

2016.2.24 9:40:27:412 (17528) [1] CSockConn::Listen: bind failed
2016.2.24 9:40:27:417 (17528) [1] AdminThread: ERROR: Listen failed on port 23. Check the Retail Store Connect setup for server HJHVIN02
2016.2.24 9:44:43:318 (14236) [1] CCommMgr::HandleException: Target request handler failed to process target request header: Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all write requests. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessWriteRequestException: Write request on table:[dbo].[RETAILPOSITIONPOSPERMISSION] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Run() failed while creating temporary table. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.CreateTempTableException: Query: SELECT TOP 0 [ALLOWBLINDCLOSE],[ALLOWCHANGENOVOID],[ALLOWCREATEORDER],[ALLOWEDITORDER],[ALLOWFLOATINGTENDERDECLARATION],[ALLOWMULTIPLELOGINS],[ALLOWMULTIPLESHIFTLOGON],[ALLOWOPENDRAWERONLY],[ALLOWPASSWORDCHANGE],[ALLOWPRICEOVERRIDE],[ALLOWRESETPASSWORD],[ALLOWRETRIEVEORDER],[ALLOWSALESTAXCHANGE],[ALLOWTENDERDECLARATION],[ALLOWTRANSACTIONSUSPENSION],[ALLOWTRANSACTIONVOIDING],[ALLOWXREPORTPRINTING],[ALLOWZREPORTPRINTING],[MANAGERPRIVILEGES],[MAXIMUMDISCOUNTPCT],[MAXLINEDISCOUNTAMOUNT],[MAXLINERETURNAMOUNT],[MAXTOTALDISCOUNTAMOUNT],[MAXTOTALDISCOUNTPCT],[MAXTOTALRETURNAMOUNT],[NAME],[POSITION],[POSPERMISSIONGROUP],[RECID],[USEHANDHELD] INTO [#dbo_RETAILPOSITIONPOSPERMISSION_6a5067cd-ba66-41c4-bf8f-da7dc918b25e] FROM [dbo].[RETAILPOSITIONPOSPERMISSION];
 ---> System.Data.SqlClient.SqlException: Invalid column name 'ALLOWPASSWORDCHANGE'.

 

 

Another feature is the “Keep Packages Files”. It keeps the files in the working folder for later review. Again, this will leave you with a lot of data in the directory.

With the Keep Package Files set you get a couple of files in the work folder with the .tmp extension. One that ends with an I and one that ends with an R. The I file is the definition of the data model and the R file is the actual data. As is they are close to unreadable. So to get the result we can use the Pack Viewer tool. It is started from the Start menu next to the Service Settings.

Select the file you want to convert. The target folder is automatically filled in. You can change it without any problems. Mark the Open Folder to open the target folder when done and click the Convert button.

The I file gives you one file that looks like this:

Converting the R file you get a multiple files depending on the number of tables in the package:

They are named with the table name for easy access. The SCTargetRequestHeader file is a content and action overview. The data files are easy(-ish) read XML files with the complete data set to be transferred.

Remember to disable the keep package flag when done and restart the service.

 

This is not the full and complete troubleshooting guide but a quick intro to a couple of entry points when struggling with an AX 2012 R2 Retail.

Changing privileges on multiple SQL server tables for a user

This is not really a Dynamics AX thing but with BI in mind it kind of relates.

The thing is that sometimes you have users that needs access to AX tables and views on the SQL server to maintain and develop BI. And often these users should have access to almost every table except a few. Payroll tables are a good example.

In a normal situation you would use schemas to handle this but since AX creates all tables as part of the dbo schema this is not an option. So I created this little script for a colleague to accelerate the proces:

DECLARE @sqlStatement NVARCHAR(max)
DECLARE @user NVARCHAR(30)
DECLARE @filter NVARCHAR(10)

-- ==========
-- Initialize 
-- ==========

SET @sqlStatement = '';
SET @user = 'contoso\abc';
SET @filter = 'payroll%';

-- ================
-- Get REVOKE lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'REVOKE SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V')

-- ================
-- Get GRANT lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'GRANT SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V') AND NAME NOT LIKE '' + @filter + ''

-- ==============================
-- Execute privilege change query
-- ==============================

EXECUTE sp_executesql @sqlStatement;

What it does is that first it creates 3 variables. One for an SQL statement, one for the user identity and one for filtering what tables NOT to grant access to. In this case we want user ABC in the contoso domain to have SELECT rights on all tables except those beginning with “Payroll”.

It then starts by creating the REVOKE SELECT statements for all tables and views (User tables only) and adds them to the @sqlStatement variable.

Then it traverses through the tables and views to make the GRANT SELECT part on all tables NOT matching the filter in the @filter variable.

Finally the created statement is executed using the sp_executesql stored procedure.

It is not the fastest statement in the world; but it gets the job done.

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.