POS not showing offline data jobs

In Dynamics 365 the offline story on the MPOS has been significantly improved since AX 2012. Now it’s more or less a click on the register in Dyn365, install the POS and distribute data. A bit simplified approach to life but anyways.

Here’s something I’ve seen a couple of times now. There’s just no jobs and nothing to process …No offline jobs

The event log gives it away a bit:

Failed to get offline sync data in offline database due to Exception. Error Details: Data Source=localhost\SQLEXPRESS;Initial Catalog=RetailOfflineDatabase;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True

offline error 1

And in the details it’s clear that we’re facing a rights issue here. But in SQL Express with no management tools on the POS machine we don’t have many configuration options and even less when we’re in a setup with a large number of registers.

All you need to do is add the user logged in to Windows to these two groups on the local machine:

User groups

Log off and on again to activate the changes. Start your POS and check the database connection status. Hopefully, you should see a lot of jobs now:

Offline jobs

Advertisements

Button grids and controls out of boundaries in the screen layout

When editing screen layouts you drag and drop controls in the (soon to be overhauled by Microsoft) editor. If you end up dragging a bit too aggressively you can actually throw the control off the grid and the visible area.

The funny part is that it’s not easily pulled back in since the editor doesn’t allow you to traverse through the active controls.

Here’s a quick description and fix. First I’ve created a simple layout with a single control:

Simple layout.PNG

Then I drag it outside the black area. Notice, that it is no longer visible but not available on the list to the left:

Dragged outside border.PNG

To get it back export the layout to an XML file:

Export layout.PNG

Edit the file in notepad. In this case the file is pretty simple. In real life you’ll be pretty pleased with the search function in Notepad.

The thing to notice here is that the left value has gone negative, since I dragged my control too much to the left:

Negative left.PNG

Change the value to a positive figure and save the document:

Fixed.PNG

Import the layout in the designer

Import layout.PNG

And now the control is visible again:

Button grid is back.PNG

If you end up with controls out of boundaries, you could close the editor without saving to prevent getting in the above situation.

Why my product images doesn’t show up on my eCommerce site

So here’s the scenario. We’ve got the POS running and the eCommerce site too; but somethings wrong with the product images. They’re not showing up on the eCommerce site. No matter how many times we restart the IIS and change the images on the media server. The thing is that the eCommerce site doesn’t pull the images from the media server. At least not for the products, that is.

So looking at these sunglasses we get the image of the sunglasses as expected:1 POS product image.png

Looking at the media server path here’s the image:

2 Image file on media server.png

Now we add a new image to the media server like this:

3 New image on media server.png

It’s ok to go “aaaawwww”…

Looking at the POS it picks up the change pretty fast and shows us two images:

4 New image on POS.png

If we go the the eCom site not much happens:

5 Image not shown on eCom.png

You can restart the IIS all that you want, it won’t change.

Here’s the explanation:

6_Folder_difference.png

The eCom site looks for the files in another folder than the POS. This opens up for having different levels of image resolution and so on, but that is another story.

So if we copy the image files to the Retail Storefront folder we might be out of the woods … but not quite.We still only see the old image on the site, thanks to caching.

Clear your browsers image cache and bingo: another moment of aaaawwwwww:

8 New image shown on eCom.png

This all might be documented somewhere … I just didn’t find it.

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