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:

ErrorMessage

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.

NewQuery

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.

UserMapping

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:

RetailServerTest

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

RetailServerTestXML

 

 

“Invalid package number range” error in AX Retail CDX

I stumbled across the above error running the P-jobs in an AX 2012 R2 installation the other day. It popped up in a test environment and to be honest we had been a bit rough to the message database; but nothing that should mess things up. The reason behind the error was probably a conflict between the current package number and a previously handled package number.

Starting the troubleshoot we quickly realise that there is no number sequence to pull the package numbers from (the package number is added to the records in the message database to identify the messages).

The quick (and dirty) solution was to manually increase the highest package number in both in- and outgoing to a number a certain amount higher. Make sure to mark the records with an error as finished, restart the service and start the distribution from AX again.

The service will now pull the next package number based on the new highest number + 1. If that does not do the trick try increasing the number even more.

 

Added 26th of April 2016:

Here’s a sql script in case you need to do a complete renumbering of the package numbers:

-- Add column for temporary package number
ALTER TABLE IncomingMessages ADD PackageNo2 INT NULL
GO

-- Fill temp package number. In this case start it of by 2000000
DECLARE @id INT
SET @id = 2000000

UPDATE IncomingMessages
SET @id = PackageNo2 = @id + 1
GO

-- Overwrite the package number on the outgoing packages using the old package number as reference
-- Notice: if you start the new numbering too low you can mix up the package numbers on the outgoing table
UPDATE outgoing
SET outgoing.PackageNo = incoming.PackageNo2
FROM OutgoingMessages AS outgoing
INNER JOIN IncomingMessages incoming ON outgoing.packageNo = incoming.PackageNo
GO

-- Update the package number on the incoming messages
UPDATE IncomingMessages
SET PackageNo = PackageNo2
GO

-- Get rid of the temporary column
ALTER TABLE dbo.IncomingMessages
DROP COLUMN PackageNo2
GO

Annoying error when configuring the Commerce Data Exchange service

Sometimes it is easy peasy … and sometimes it is not. This one leans towards the not and does it in an annoying way.

I was running the PowerShell script for configuring the Commerce Data Exchange. It prompts you for user, password, path for the binaries and the certificate thumbprint … not anything complicated. And although I had knew that I had done it successfully before I was stuck with this error:

Get-Content : Cannot find path 'C:\inetpub\DynamicsAxRetail\CommerceDataExchangeRealtimeService\Web.config' because it does not exist.
At C:\Program Files (x86)\Microsoft Dynamics AX\60\Commerce Data Exchange\Real-time Services\6.2\Sample Deployment Scripts\InstallCommerceDataExchangeRealtimeService.ps1:713 char:34
+         $WebConfigXML = [XML] (gc <<<<  $WebConfigFilePath)
    + CategoryInfo          : ObjectNotFound: (C:\inetpub\Dyna...vice\Web.config:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

I tried several times. Restarted. Tried again. Tried as another user. Tried beating my head against the wall. Nothing seemed to be working.

The solution was deleting one character from the binary path. I just used

C:\Program Files (x86)\Microsoft Dynamics AX\60\Commerce Data Exchange\Real-time Services\6.2

instead of

C:\Program Files (x86)\Microsoft Dynamics AX\60\Commerce Data Exchange\Real-time Services\6.2\

and after removing the backslash at the end of the path string it installed without any problems …. Grrrrrrr …..

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:

AX2012_RetailMsgBoxSQL

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:

RetailCDXLog

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

RetailCDXServiceSettings

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.

RetailCDXWorkDir

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

RetailCDXLogDir

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.

RetailCDXPackViewer

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:

RetailCDXPackDataModel

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

RetailCDXPackViewData

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.

RetailCDXPackViewDataContents

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.

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.