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 … 😉

Using hardware station from Windows Phone MPOS

Microsoft released the MPOS for Windows Phone a while ago and although a bit rough to install it works very well.

Next step was to get it to work with my wireless receipt printer through a hardware station. As you might now the connection to a hardware station can be tested through the url https://<MyHardwareStationAddress&gt;:<port>/hardwarestation/ping. And as expected it didn’t work right away since I hadn’t installed the certificate on my phone.

I figured that it couldn’t be that hard, but since you are reading this you might have guessed that it wasn’t all that easy.

I had earlier on exported the certificate on my hardware station as a .pfx file and used that with success. But that wasn’t the case on the phone. I imported the .pfx file and used the Certificate app to see the status of the certificate.

I knew the certificate was working on other POS machines but here it wouldn’t accept it as valid:

certificate-not-working

After consulting google for a while I read that people had solved issues like this by using .cer exports instead, but that didn’t solve anything for me.

What I ended up doing was exporting it as .p7b. Only thing is that if checked the “Include all certificates in the certification path if possible” I still had a non valid certificate on the phone. So I exported it like this:

export-certificate

And now I have a working certificate:

certificate-working

Actually I think that it is more than just Okay; but anyhow …

The hardware station was now allowing pairing and selecting:

hardware-station-paired

 

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.

 

Background images on AX POS

The POS UI in AX can be customised in many ways and one of the more dramatic ones are changes in the background images. The steps are few and goes like this:

Open the Images form

imagesshortcut

 

Create the images in the Images table. In this case we create two. This may vary in your situation

images

Open the Visual profiles form

visualprofileshortcut

Create a visual profile and add the images here

visualprofile

Create a register and device and add the visual profile to the register

register

To get this running on the POS you need to distribute data. Job 1090 should be sufficient. When the data is applied you are ready to enjoy the results:

 

pos1

pos2

This is just the basics of what you can do, but I hope you get the idea of what is possible.

No icon on buttons in LCS or AX7

In LCS or New Dynamics AX you might have noticed that you have buttons missing their icon/image. It looks like this:

The solution is really simple. Add the sites as trusted sites and you’re good.

Open Internet Options in your browser and go to the Security tab. Select Trusted sites and click Sites:

internetoptionssecurity

Add the current site to the list:

trustedsites

You’ll need both LCS and AX on the list to keep them both looking good.

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.

 

Lifecycle Services and Azure Resource Manager – First bump on the road…

We have been using Lifecycle Services (LCS) for hosting environments for both AX 2012 and AX7 (sorry, New Dynamics AX) so it was somewhat a trivial task deploying new servers. Until we met Azure Resource Manager, that is. Adding servers to an old project ended up giving us this error message:

Lifecycle Services can’t connect to the Azure subscription using Azure Resource Manager. Click the Microsoft Azure Settings button and edit the selected Connector to provide the required permissions to ERP applications in your Azure subscription.

Error_message

The fix is fairly simple. Go to the Azure connector in your project. You’ll find it in the Project settings. Select it and click Edit. What you need to do is to flip this one to Yes:

Project_Flip_This

It will then lead you through a couple of steps regarding your management certificate and how you need to download it and upload it to the Azure Portal. No need to worry about that because you already did that when you created the connector so just click Next.

You could then end up with another obstacle in the form of missing rights on the Azure tier:

Project_Need_To_Add_Role

Go to Azure Portal and go to your subscription. Click this button to do add access:

Azure_Add_Access_Add

The following is pretty straight forward. Select a role and select the user:

Azure_Add_Access

Azure_Add_Access_Select_User

Azure_Add_Access_User_Done

Going back into LCS and click Next might present you with the same error like above with the Dynamics Deployment Service needs access but hang in there. It seems like there’s a bit of delay, so just give it another go after a minute or two.

As soon as your connector has been updated you’re good to go and can deploy the server.