Error when pushing catalog data to the retail server

While implementing at a retail customer we were stuck for a while with an occasional error in the data distribution of job 1150 (Catalog). Going through the looong error message it indicated that it couldn’t truncate a text field on the RetailPubCatalog table.

I’ve added the error message in the bottom of this post as reference.

When looking at the table definitions on the SQL server we quickly discovered that on the D365O table the ModifiedBy field on the RetailPubCatalog was a 20 characters field and on the Retail server side it was only 8 characters long.

Going through the content of the data package we saw why it wasn’t failing every time. Only some of the catalogs where modified by a user with more than 8 characters meaning that when only a given user was the last to modify the job failed.

The fix is simple. Run this on the retail server database to expand the field:

alter table [ax].RETAILPUBCATALOG alter column MODIFIEDBY nvarchar(20) not null

You can’t change the field size through the designer, so you need to script your way out of this.

Hopefully, this error will be fixed in a future update. Going through the database it didn’t have the ModifiedBy field on any other table than this.

 

Error message:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> Microsoft.Dynamics.Retail.CommerceDataExchange.ProcessDataPackageException: Error occurred when running SqlTargetRequestHandler. —> Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunException: Failed to merge data into table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN  UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN  INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO])  VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunException: Failed to run SqlWriteRequestRunner for table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN  UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN  INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO])  VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> Microsoft.Dynamics.Retail.CommerceDataExchange.PerformWriteOperationException: Error when writing data to table [AX].[RETAILPUBCATALOG]. Query: MERGE [AX].[RETAILPUBCATALOG] AS dstUSING (SELECT [CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO] FROM [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]) AS srcON (dst.[RECID]=src.[RECID])WHEN MATCHED THEN  UPDATE SET [CREATEDDATETIME]=src.[CREATEDDATETIME],[ENABLESNAPSHOT]=src.[ENABLESNAPSHOT],[MODIFIEDBY]=src.[MODIFIEDBY],[MODIFIEDDATETIME]=src.[MODIFIEDDATETIME],[ORIGIN]=src.[ORIGIN],[OWNER]=src.[OWNER],[PUBLISHEDDATETIME]=src.[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM]=src.[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO]=src.[PUBLISHEDVALIDTO],[STATE]=src.[STATE],[VALIDFROM]=src.[VALIDFROM],[VALIDFROMDATETIME]=src.[VALIDFROMDATETIME],[VALIDTO]=src.[VALIDTO]WHEN NOT MATCHED THEN  INSERT ([CREATEDDATETIME],[ENABLESNAPSHOT],[MODIFIEDBY],[MODIFIEDDATETIME],[ORIGIN],[OWNER],[PUBLISHEDDATETIME],[PUBLISHEDVALIDFROM],[PUBLISHEDVALIDTO],[RECID],[STATE],[VALIDFROM],[VALIDFROMDATETIME],[VALIDTO])  VALUES (src.[CREATEDDATETIME],src.[ENABLESNAPSHOT],src.[MODIFIEDBY],src.[MODIFIEDDATETIME],src.[ORIGIN],src.[OWNER],src.[PUBLISHEDDATETIME],src.[PUBLISHEDVALIDFROM],src.[PUBLISHEDVALIDTO],src.[RECID],src.[STATE],src.[VALIDFROM],src.[VALIDFROMDATETIME],src.[VALIDTO]);DROP TABLE [#AX_RETAILPUBCATALOG_ea4a00d0-7897-4567-9e8f-ae569c284074]; —> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint ‘I_-1981983829_-162312163’. Cannot insert duplicate key in object ‘ax.RETAILPUBCATALOG’. The duplicate key value is (5637145329).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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection)   — End of inner exception stack trace —   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.PerformWriteOperation(SqlConnection connection)   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.Run(SqlConnection connection)   — End of inner exception stack trace —   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlWriteRequestRunner.Run(SqlConnection connection)   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunner.Run(SqlConnection connection)   — End of inner exception stack trace —   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlMergeRequestRunner.Run(SqlConnection connection)   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection)   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessDataPackage(DataPackage dataPackage)   — End of inner exception stack trace —   at Microsoft.Dynamics.Retail.CommerceDataExchange.SqlTargetRequestHandler.ProcessDataPackage(DataPackage dataPackage)   at Dynamics.AX.Application.RetailCdxChannelDbDirectAccess.runDownload() in xppSource://Source/Foundation\AxClass_RetailCdxChannelDbDirectAccess.xpp:line 215   — End of inner exception stack trace —

Advertisements

Automated startup and shutdown of Azure VMs

This document describes in headlines how to setup automated start and shutdown of Azure VMs. To go through the steps, you are required to have access to the management portal at https://portal.azure.com and administration rights.

By shutting down the servers when not used there is a noticeable amount to be saved.

Prerequisites

Automation Account

To run the automation scripts, you need an Automation Account. Notice, that the account must be created under the same subscription as the servers that should be managed by the account.

The fastest way to locate the accounts is to search for the Automation Accounts service:

account1

To create a new account, click the Add button:

account2

Give the account a name, specify a resource group (or create a new) and a location:

account3

When created (it takes a moment), select the new account:

account4

Shut down servers

Runbooks

All activity is kept in runbooks. The account comes with 4 default runbooks which we for this scope leaves as is.

shutdown1

We are going to create our runbooks based on a template that gives us the functionality we need out of the box. To do this click Browse gallery:

shutdown2

The list of available templates is long. The first one we are going to use is the “Stop Azure V2 VMs”.

shutdown3

When we click on it we can see a graphical presentation of the flow. This is not relevant to go through in this document. Click the Import button to use the template:

shutdown4

Give the new runbook a name and a description:

shutdown5

After the runbook has been created, click Edit:

shutdown6

From here we can change the runbook including default values for the runbook. We can also test the runbook with different values.

In this case we are not going to change anything so we publish it right away by clicking the Publish button:

shutdown7

Scheduled runs

Next step is to schedule the runbook to run every afternoon. To do this click the Schedule button:

schedule1

To do this we need to things: A schedule and the parameters to run the runbook with. First the schedule:

schedule2

In this scenario we want the VMs to be stopped every day at 18:00:

schedule3

To run the runbook we need to specify the resource group name and the name of the VM.

schedule4

This information is found on the information page of the VM in the Azure portal.

Add it to the parameters:

schedule5

Now the scheduler will shut down the server matching the parameters every day at 18:00.

If you leave the VM name blank all servers in the resource group will be affected.

Starting VMs

To create a runbook to start the VMs go to the gallery and select the Start Azure v2 VMs template:

startup1

Give it a name and description:

startup2

Click edit to access the runbook just created:

startup3

Publish the runbook. Again we do not want to change anything:

startup4

Create a schedule to start the VM every working day at 7:00:00. In this case we don’t start them up in the weekends; but if somebody starts a VM it is automatically shut down by the previously created runbook.

startup5

You just saved a lot of money on the VM…

 

Create scheduled runs for every VM you can shut down at night.