RetailTenantUpdateTool.ps1 misses prerequisites

After moving data from one server to another you need to run the RetailTenantUpdateTool powershell script. Doing this might present you with this error:

Please download and install below prerequisites:
Microsoft Online Services Sign-In Assistant for IT Professionals, download link: http://go.microsfot.com/fwlink/?LinkID=286152
Azure Active Directory Module for Windows PowerShell (64-bit version), download link: http://go.microsoft.com/fwlink/p/?linkid=236297

PowerShell error

One of them actually links to a proper download but the other one forwards you to a site telling you that what you’re looking for has retired …

To get moving install this module before executing the script:

Install-Module -Name MSonline

Install MSOnline

After completing this install you can run the script without any issues.

Login prompt

 

Advertisements

Updates tiles in LCS not updated

When looking at the hosted environments in LCS sometimes the tiles showing updates doesn’t seem to update probably. The symptoms could be that the numbers indicate that you’re missing some updates after updating it all or that the Last run date isn’t updating.

Tiles update outdated.PNG

The status is updated through a scheduled task on the server.

Task scheduler.PNG

It runs a powershell script in C:\LCSDiagnostics\ called CollectData.ps1

Script.PNG

You can run it manually (through an elevated powershell) and that should hopefully update the figures.

It would have been nice to be able to see the run history on the task in the scheduler but for some reason that has been disabled:

History disabled.PNG

That is easily fixed. Open the Task scheduler as Administrator and that gives you the option in the right most part of the form:

Enable task history.PNG

 

Changing Configuration keys in Dynamics 365 – Maintenance mode

Going into License configuration to enable or disable a configuration key you’ll probably see the warning:

This form is read-only unless the system is in the maintenance mode. Maintenance mode can be enabled in this environment by running maintenance job from LCS, or using Deployment.Setup tool locally

Not in maintenance mode.PNG

Here’s how to work around that in all but production environments:

  • Tell the other users working on that environment, since you’ll be restarting the IIS during the process.
  • Log on to the server running the AOS service and start up a command prompt in Administrator mode
  • Run the following statement where you change K to the correct drive for your AOS service:

K:\AosService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AosService\PackagesLocalDirectory –bindir K:\AosService\PackagesLocalDirectory\Bin –sqlserver . –sqldatabase axdb –sqluser <SQL admin user id> –sqlpwd <SQL users password> –setupmode maintenancemode –isinmaintenancemode true

  • Restart the IIS with the iisreset command

This leaves the environment in maintenance mode. This doesn’t mean non-functional; but you probably would like to leave the maintenance mode as soon as possible.

When you are done you run this script from a command prompt in Administration mode (again replace K with the appropriate drive letter):

K:\AosService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AosService\PackagesLocalDirectory –bindir K:\AosService\PackagesLocalDirectory\Bin –sqlserver . –sqldatabase axdb –sqluser <SQL admin user id> –sqlpwd <SQL users password> –setupmode maintenancemode –isinmaintenancemode false

Restart you IIS once more and you’re back on track.

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 —

Enabling index hints is deprecated in AX 2012 … almost

Sending index hints to the SQL server from AX has been around for a long time and often it has not done any good since the SQL server is pretty good at picking the best way it self.

So when it ended up as a parameter in the AOS server configuration in AX 2009 and then removed from the configuration in AX 2012 we seemed clear from the trouble it could cause. Microsoft stated that it was deprecated with AX 2012 and no longer available …

So it seemed a bit strange that the SQL server apparently received the information on a server I was working on recently.

While going through about all possible stuff to locate why it was acting like the non-existing index hint flag was enabled, I was going through the registration database to compare it against an AOS we knew was working as expected. And there it was … the registration key called “hint”.

I did a bit of research and I was not the only one struggling with this. As it appears there are these values to choose from :

Empty = Index hints are enabled and LTRIM (yes, it is there too) is disabled.

0 = Both index hints and LTRIM are disabled. This has to be the recommended setting.

1 = The same as empty. Does that make sense? No, not really. Anyways …

2 = Index hints are disabled and LTRIM is enabled.

3 = Both index hints AND LTRIM are enabled

 

And just for refreshing the memory: The location of the registration keys are

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\Dynamics Server\6.0

and in here find the right AOS and configuration.

Troubleshooting AX/SSRS

The purpose of this document is to list a troubleshoot path to follow when reports cannot be printed from AX 2012.

The contents are not fully covering all options available but points out a toolbox for handling often seen issues.

Troubleshooting

Is it all reports not printing?

Try printing one of the most simple reports available. I usually use the Departments report found in “Organization administration / Reports / Base data / Departments”. If that one prints successfully it is probably not the SSRS causing the problem.

Is SSRS set up correct within AX?

AX has a very flexible setup regarding SSRS. Each AOS has – and must have – its own relation to an SSRS. All AOS’s can point towards the same SSRS but it is also possible to address the reports from each AOS to its own SSRS instance. One AOS can on top this have multiple relations to different SSRS instances. However, only one relation can be default and active.
So how do we make sure all this works?
1. Make sure that all AOS servers have a relation (with default mark set) to an SSRS instance in the SSRS list (Appendix 1 – Locating the SSRS names)
2. Validate the relations using the “Validate settings” button.
3. Verify that the specified URLs are valid.

Is SSRS set up correct regarding connection to AX?

With AX pointing towards the right SSRS instance we need to make sure that the SSRS instance points towards a valid AOS. With only one SSRS instance on the server it – by default – looks up the default client configuration on the server to access AX. Having multiple SSRS instances on one server requires each instance to have an configuration file with the name Microsoft.Dynamics.AX.ReportConfiguration.axc. Make sure that this configuration file is valid. For instance by opening a client with it if possible. Also make sure that the WCF part of the file is fully updated. To rebuild the WCF part follow the description in the section Rebuild the WCF part of the AX configuration for the SSRS.
Another way to test that multiple instances points towards the correct instances is to create eg. a department called “Development” in a development environment and one called “Test” in the test environment and then verify that these are shown when printing from the relevant AX environments or the SSRS report manger site (see description of how to print from outside the AX client).

Does it print from outside the AX client?

SSRS offers a web frontend allowing us to test – at least some – reports from outside AX pretty easily. To do so you need to find the URL address to the report manager site. This is listed in the Report server list found within AX. To find this list please read the description in Appendix 1 – locating the SSRS names.
From here navigate to the URL specified in the field Report Manager URL.
If this does not open up succesfully go directly to restarting the SSRS service.
aIf it opens up successfully click on the DynamicsAX folder and use the Search field in the upper right corner to locate the report design HcmDepartmentReport.DepartmentDesign

Printing reports from the SSRS Report Manager

Printing reports from the SSRS Report Manager

Click on the report to show it. If a report is shown the report server and the connection works.
If not printed successfully you might be presented with an error description pointing you in a useful direction.
Rebuild the WCF part of the AX configuration for the SSRS
The WCF part of the AX configuration describes the service ports available. It can be usefull to rebuild these in some circumstances eventhough you do not think they have been altered. To update this open the Microsoft Dynamics AX Client Configuration on the server running the SSRS. Click Manage and “Open a configuration file…”. Navigate to C:\Program Files\Microsoft SQL Server\MSRS11.<Name of the relevant instance>\Reporting Services\ReportServer\bin and select the file Microsoft.Dynamics.AX.ReportConfiguration.axc.

Rebuild the WCF part of the configuration file

Rebuild the WCF part of the configuration file

On the Connection tab click the Refresh Configuration button and wait …

Waiting for the WCF to be rebuild.

Waiting for the WCF to be rebuild.

When completed click Apply and “Manage / Save configuration file”.
After saving the file restart the SSRS-service.

Appendix 1

Locating the SSRS names

The name of the server can be found from within AX by looking up the reporting server(s) listed in “System administration / Setup / Business intelligence / Report servers”.
Notice, that the list can contain multiple SSRS instances and not all of them are related to the environment you are trying to fix.

Restart the SSRS-service

This is a fairly quick service when it comes to restarting so you will not be troubling users for that many seconds.
Log on to the server running SSRS. From here locate the service “SQL Server Reporting Services (<Name of the relevant instance>)” and restart it.

Change installation folder for the AOS server

Working for a company that handles development of multiple modules/add-ons to AX we end up having a lot of AOS-services on one server. We do that to separate development and release environments and keeping modules isolated from each other.

The problem that we sometimes end up with is that the C-drive reaches max and we need to install some of the AOS services on a different drive.

Going through the normal setup routine does not offer any options of specifying the installation folder. So we need to do two modifications before it works.

First step is to open the regedit.exe and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dynamics\6.0\Setup\Components\InstallDir and change the value to where you would like the AOS to be installed.

Second step is to make sure that the Server Setup files is present in the new directory. To do this simply copy the folder C:\Program Files\Microsoft Dynamics AX\60\SetupSupport to the new drive so you have a D:\Program Files\Microsoft Dynamics AX\60\SetupSupport or wherever you want the AOS servers installed.

Run the AX setup and your AOS is now installed on your new drive.