Database error during POS activation

For some reason – still to be discovered – I once in a while get a number sequence error while activating a POS.

getting last number sequence failed

It’s not super informative but going through the event log we find this:

An exception of type: 'Microsoft.Dynamics.Commerce.Runtime.StorageException' occurred while executing Microsoft.Dynamics.Commerce.Runtime.Messages.StartSessionRequest request by Microsoft.Dynamics.Commerce.Runtime.Workflow.Security.StartSessionRequestHandler. Error resource id: 'Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError', Exception: Microsoft.Dynamics.Commerce.Runtime.StorageException: Failed to read from the database. See inner exception for details

DatabaseErrorCode: 0 ---> Microsoft.Dynamics.Commerce.Runtime.Data.DatabaseException: Violation of PRIMARY KEY constraint 'I_-1558077251_-1679712867'. Cannot insert duplicate key in object 'ax.RETAILTRANSACTIONTABLE'. The duplicate key value is (<removed by me to anonymize the data>).

The statement has been terminated. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'I_-1558077251_-1679712867'. Cannot insert duplicate key in object 'ax.RETAILTRANSACTIONTABLE'. The duplicate key value is (<removed by me to anonymize the data>).

The statement has been terminated.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader()

   at Microsoft.Dynamics.Commerce.Runtime.DataAccess.SqlServer.SqlServerDatabaseProvider.ExecuteStoredProcedure(IDatabaseConnection connection, String procedureName, IEnumerable`1 parameters, IDictionary`2 outputParameters, Action`1 resultCallback, Nullable`1& storeProcedureResultValue)

   --- End of inner exception stack trace ---

   at Microsoft.Dynamics.Commerce.Runtime.DataAccess.SqlServer.SqlTypeHelper.HandleException(SqlException sqlException)

   at Microsoft.Dynamics.Commerce.Runtime.DataAccess.SqlServer.SqlServerDatabaseProvider.ExecuteStoredProcedure(IDatabaseConnection connection, String procedureName, IEnumerable`1 parameters, IDictionary`2 outputParameters, Action`1 resultCallback, Nullable`1& storeProcedureResultValue)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.SqlServerDatabaseContext.<>c__DisplayClass15_0.<ExecuteStoredProcedure>b__0()

   at Microsoft.Dynamics.Commerce.Runtime.Framework.RetryPolicy.<>c__DisplayClass13_0.<ExecuteAction>b__0()

   at Microsoft.Dynamics.Commerce.Runtime.Framework.RetryPolicy.ExecuteAction[TResult](Func`1 func, Action`3 onTransientError)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.SqlServerDatabaseContext.ExecuteStoredProcedure(String procedureName, ParameterSet parameters, ParameterSet outputParameters, Action`1 resultCallback, Int32& returnValue)

   --- End of inner exception stack trace ---

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.SqlServerDatabaseContext.ExecuteStoredProcedure(String procedureName, ParameterSet parameters, ParameterSet outputParameters, Action`1 resultCallback, Int32& returnValue)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.SqlServerDatabaseContext.ExecuteStoredProcedureScalar(String procedureName, ParameterSet parameters, ParameterSet outputParameters)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.TransactionLogSqlServerDataService.Save(RequestContext context, Int64 channelId, DataTable dataTable)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.TransactionLogSqlServerDataService.Save(SaveTransactionLogDataRequest request)

   at Microsoft.Dynamics.Commerce.Runtime.DataServices.SqlServer.TransactionLogSqlServerDataService.Execute(Request request)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context, IRequestHandler handler, Boolean skipRequestTriggers)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context, IRequestHandler handler)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context)

   at Microsoft.Dynamics.Commerce.Runtime.Services.TransactionLogService.SaveTransactionLog(SaveTransactionLogServiceRequest request)

   at Microsoft.Dynamics.Commerce.Runtime.Services.TransactionLogService.Execute(Request request)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context, IRequestHandler handler, Boolean skipRequestTriggers)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context, IRequestHandler handler)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context)

   at Microsoft.Dynamics.Commerce.Runtime.Workflow.AuthenticationHelper.LogTransaction(RequestContext context, TransactionType transactionType, String transactionId)

   at Microsoft.Dynamics.Commerce.Runtime.Workflow.Security.StartSessionRequestHandler.Process(StartSessionRequest request)

   at Microsoft.Dynamics.Commerce.Runtime.SingleRequestHandler`2.Execute(Request request)

   at Microsoft.Dynamics.Commerce.Runtime.CommerceRuntime.Execute[TResponse](Request request, RequestContext context, IRequestHandler handler, Boolean skipRequestTriggers).

 

So the problem/symptom is centred around RetailTransactionTable. I haven’t found the actual source of the problem and what I’m suggesting as quick’n’dirty fix is not to be used in a live environment or anywhere else but sandboxes where it won’t affect anything close to live data.

I create a new query on the SQL server running the retail server database and use the following TSQL to rename a transaction id:

update [AxDB].[ax].[RETAILTRANSACTIONTABLE]
set TRANSACTIONID = '<transactionid_found_in_event_log + something else like a B>'
 where TRANSACTIONID = '<transactionid_found_in_event_log>'

After that the retry button brings you back on track and the activation can continue:

Activation success

As said, it ain’t pretty and I do not suggest this as a solution to be used anywhere. But it will get you passed that error. There’s probably a better solution and maybe a root source, but until then …

Oh, and I do not take any responsibility of bad things coming out of this … 😉

Retail operations and user rights

In this post I will do a quick explanation of the user rights setup of the retail POS operations.

Operations is referring to the different actions available in the POS. They are listed in Retail and commerce/Channel setup/POS setup/POS/Operations. Each operation has the following information:

Operation id and Operation name identifies the operation.

Permission ID 1 and Permission ID 2: These permissions can be a required level of rights needed for the operation.

Check user access: If not set the operation does not require the user to have the required access.

User operation: If not set you cannot create a button on the POS for that operation. Notice that the form by default has a filter on that field requiring the field to be set true.

Confused? Let’s have a look at an example.

We are going to focus on the void product operation using worker 000120 (Andrew Lan) and 000110 (Dan Park). Dan is allowed to void transactions:

danp-pos-permissions

Andrew is not:

andrewl-pos-permissions

Scenario 1:

Check user rights = true

Permission IDs = 0

Andrew tries to do a void:

void-transaction-not-allowed

Since Andrew has limited rights he cannot void the transaction.

 

Scenario 2:

Check user rights = false

Permission IDs = 0

In this case the POS doesn’t care what about permissions when voiding transactions so it allows Andrew to void:

void-transaction-prompt

 

Scenario 3

Check user rights = false

Permission IDs = 1001 (Allow transaction voiding)

Like in scenario 2 the POS still doesn’t care about user rights so it allows Andrew to void the transaction:

void-transaction-prompt

Scenario 4

Check user rights = true

Permission IDs = 1001 (Allow transaction voiding)

The user rights are validated again and since Andrew isn’t allowed voiding he can’t do the void. But now we have added the feature that Andrew can call in his manager or anyone else with enough rights to allow transaction voiding:

cannot-void

In this case he gets Dan to type in his id and password and this opens up for voiding the transaction:

void-transaction-prompt

 

Peripheral Simulator in new release of Dynamics 365 Operations

With one of the latest releases of Dynamics 365 Operations we have the option of simulating hardware peripherals like printers, cash drawers and so on.

The idea is to allow the user to simulate the flow of the retail processes including printers, drawers, scales, payments and so on but without having to occupy your co-workers desk piling up all of your retail hardware.

Here is what you need to do. Start by going to the hardware profiles. In the demo data a new profile has been added called Virtual. It has been set up with a profile that will work pretty much out of the box.

Besides the data a new button has been added allowing the download of the simulator:

hardware-profile-and-download-button

The simulator download hasn’t received the prettiest name, but that aside it is working great.

peripheralsimulator

The installer is very much a next-next-next installer and does not require any action taken unless you have specific wishes regarding installation folder.

Once installed you get this new application:

retail-peripheral-simulator

Already at this point we get an idea of what to expect. But before getting everything up and running you need to do a couple of more things.

First, we need to tell the store that the hardware station should use the new hardware profile:

hardware-profile-o

Run your 1070 and 1090 jobs (depending on which setup changes you made).

Now, starting up the POS you might end up with a series of hardware station errors that could look something like this:

hardware-station-error-3hardware-station-error-2comm-error-cash-drawer

To get passed this we should shortly touch upon how this works. According to the hardware profile we are using all communication is OPOS based so we need to get that onboard our POS machine as well. I used this one that previously was recommended during the installation of the POS software: Link to OPOS software

With that done you should be ready to start up your POS. You might have to manually go in and enable the use of the hardware station.

So first test could be a safe drop. In this case I pull out 10 USD:

make-safe-drop

That leaves a safe drop receipt in the printer:

safe-drop-receipt

And the drawer needs to be closed again:

cash-drawer-open

Click the Close drawer button to close it:

cash-drawer-closed

Let’s sell something. In this case a shirt:

transaction

I would like to pay this with a credit card so I switch to my simulator and select the MSR.

no-card-created

Click the + button to create new card:

create-master-card

The number is found on this page: credit card generator

Next step is to click the Pay card button in the POS followed by the Swipe card button in the Simulator. This adds the card info to the tender information in the POS:

card-details-in-pos

Completing the transaction you get a receipt in the printer:

receipt-from-transaction

This will definitely change the way we make demos of the POS with Dynamics 365 Operations.

 

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:

operatinguniterror

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:

operating-unit

Next is this error:

storageerror

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:

catalog-db-name

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

debugactive

 

 

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.

Associate worker with AAD account – Service unavailable

When trying to associate a worker with an AAD user in New Dynamics AX you can in some of the releases get an error saying “The service you are trying to reach is currently unavailable. Please contact your System Admin…”.

Workers_--_Error

Now in the New Dynamics AX the System Admin is often pretty hard to find unless you have a mirror so here is something that might get you passed that error. There’s no fancy stuff here. More a matter of how to approach the task.

Click “Associate existing account”:

Workers_--_StartAssociate

Change “Filter using columns” to “Search using email” and put in the email of the user:

Workers_--_SearchByEmail

 

Click Search and select the user on the list:

Workers_--_FindUser

Click OK and you’re back on the worker with a association to your user:

Workers_--_AssociationDone

 

The good part is that you can do this within a few seconds compared to having to wait for the full AAD to be loaded before you can pick the user, which you are going to be searching for anyways.

There is a hot fix released for this error, but if you just need to get it fixed now then the above work-around could help you out.

 

Debugging Commerce Runtime in the New Dynamics AX (AX7)

 

The New Dynamics AX has certainly shown us new ways of working and for the developer the learning curve has been pretty steep.

With everything being Visual Studio for the developers new work routines are implemented and that goes for debugging as well.

Now, the Visual Studio debugger is great. So we are not losing any ground. We do however have to think a bit different in order to nail those tricky troubleshooting situations. In this post I will show how to debug the Commerce Runtime part of the Retail Server.

 

Step 1 – Getting the IIS ready

Navigate to the Retail Server folder. One way to get there is to open the IIS Manager. Locate and select the RetailServer in the Sites list to the left and select Explore. In the web.config file change

<compilation debug="false" targetFramework="4.5" />

to

<compilation debug="true" targetFramework="4.5" />

When you save the file you cannot save it to its original position. Save it to your desktop or somewhere else neutral and then copy it to the original folder.

Restart the IIS

 

Step 2 – Set the breakpoint

Now start up Visual Studio and open the solution CommerceRuntime in the RetailSDK folder \RetailSDK\Code\CommerceRuntime.

In the solution explorer locate the Discount.cs file in Runtime.PricingEngine project and add a breakpoint. Just to keep it simple I have put it into the constructor:Breakpoint Discount constructor

 

Step 3 – Start the Modern POS

Now it is time to fire up the Modern POS. Just do a login and leave it there for a while.

 

Step 4 – Get the debugger ready

Go back to Visual Studio, go to the Debug menu and select Attach to process.

Change the code type from automatic to Managed (v4.6, v4.5, v4.0)

AX7POSDebugCodeType

Mark Show processes from all users, select the w3wp.exe processes and click Attach.

AX7POSDebugProcess

I know that you probably only need to attach to one of them; but it is much easier just selecting them all.

Take a quick look at your breakpoints to make sure that they are active. If not you might be missing a reference in the commerceruntime file.

Breakpoint active Discount constructor

Step 5 – Back to the POS to get the debugger activated

What we need to do now is to get the POS to require an instance of the Discount object on the retail server. In this example I add a pair of sunglasses to the basket:

Add product to get the Discount object instantiated

That gets us into the debugger and we can do what we need to do in order to see how it works or start troubleshoot.

Debugger activated

I hope this helps you get a bit deeper into the retail server or other services related to the New Dynamics AX.