Azure SQL firewall access

Some things were easier in the good old days before Dyn365 and Azure and some are better. Often the things that were better before tends to be things we need to relearn.

Allowing firewall access to Azure SQL (for example regarding BI) is easily scripted and here are 3 useful scripts for that purpose:

List current firewall rules:

SELECT * FROM sys.firewall_rules

If you need to grant access to the IPs 111.222.111.222 to 111.222.111.250:

EXEC sp_set_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';
Getting rid of the above rule is just as simple:
EXEC sp_delete_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

The three above is all server rules so you need to run them towards the master database.

You can do the same tricks on database level:

Listing

SELECT * FROM sys.database_firewall_rules

Granting

EXEC sp_set_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';

Deleting

EXEC sp_delete_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

 

Remember to be run them on the desired database.

 

Please keep in mind that you are messing with security and I – as usual – don’t take any responsibility in any damage you might make by using the above.

Advertisements

Prompting for values in SQL script

This is something that I forget 2 seconds after I’ve used it so now I’ll put it here as a note to self.

I have a series of TSQL scripts that I use once in different circumstances and often it’s a while between each go. In some of the I need to add some environment specific information and instead of having to scroll down through the text the query editor offers a way of prompting for parameters through a tagging of text like this:

select <Parameter name, Data type, Default value>

Before executing the script you press CTRL+SHIFT+M (this is the part I usually forget) and that makes the editor prompt you like this:

Screenshot 2019-02-19 at 13.49.43

Replace the Default value with the appropriate text and click OK. This will update your SQL script to this:

select 1234

Not the most mind-blowing thing in the world, but a great help.

 

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 —

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 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.

Troubleshooting the Commerce Data Exchange

In AX 2012 troubleshooting the Commerce Data Exchange is necessary. It’s somewhat a blackbox but we do have a few tools in the belt and handles to pull.

This post describes a couple of these in AX 2012 R2. The following versions are a bit more informative; but some of the tips are still relevant.

 

What’s in the queues?

The communication flow is passed through the SQL server and during installation you are asked for a message box database. When looking into this database two of the tables are showing you the flow of messages and a few related (general) pieces of information. I use the following SQL statements as a standard approach to these tables:

SELECT JobID, Status, TryCount, ErrorNo, ServerMsg, FinishedDateTime, PackageNo, ServiceName 
FROM [RetailMsgDB].[dbo].[IncomingMessages]
ORDER BY FinishedDateTime DESC
SELECT JobID, Status, TryCount, ErrorNo, ServerMsg, FinishedDateTime, PackageNo, ServiceName 
FROM [RetailMsgDB].[dbo].[OutgoingMessages]
ORDER BY FinishedDateTime DESC

/*
delete from IncomingMessages
delete from OutgoingMessages
*/

The result of the query looks something like this:

AX2012_RetailMsgBoxSQL

The first result set shows the messages going in to the server and the second result set shows the outbound. As long as the ErrorNo is 0 you are good. If it does show up with an error you need to start digging a bit. The JobID column shows you in which job to look.

Notice that I have to delete statements disabled by comment-tags. These are nice when you would like to reset the contents of the queue-tables. To execute the you need to mark them (without the /* and */) and execute.

 

Getting info from the log file and packages

The data exchange service offers a couple of tricks regarding its log file and the packages. The level of informations is setup in the Service Settings:

RetailCDXLog

This offers lot of settings for the service but in this post I will only be touching a couple of them.

RetailCDXServiceSettings

First, make sure you customize the right service and click Next until you get access to the Working Directory. This is where the files are dumped for processing by the service.

RetailCDXWorkDir

Moving on we can change the settings for the log file.

RetailCDXLogDir

We have several relevant settings in this part. First of all the directory for the log file and the Log Level. As default it logs the errors only and that is often enough. If you want it to be more chatty you just click in what you want to know from the service. Notice, that the log file does get a bit overwhelming to look at if you click on everything.

Reading the log file can give you a very specific error to work with. With a field mismatch it could look a bit like this:

2016.2.24 9:40:27:412 (17528) [1] CSockConn::Listen: bind failed
2016.2.24 9:40:27:417 (17528) [1] AdminThread: ERROR: Listen failed on port 23. Check the Retail Store Connect setup for server HJHVIN02
2016.2.24 9:44:43:318 (14236) [1] CCommMgr::HandleException: Target request handler failed to process target request header: Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessTargetRequestHeaderException: ProcessTargetRequestHeader failed to execute all write requests. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessWriteRequestException: Write request on table:[dbo].[RETAILPOSITIONPOSPERMISSION] failed to execute. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.RunException: Run() failed while creating temporary table. ---> Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.CreateTempTableException: Query: SELECT TOP 0 [ALLOWBLINDCLOSE],[ALLOWCHANGENOVOID],[ALLOWCREATEORDER],[ALLOWEDITORDER],[ALLOWFLOATINGTENDERDECLARATION],[ALLOWMULTIPLELOGINS],[ALLOWMULTIPLESHIFTLOGON],[ALLOWOPENDRAWERONLY],[ALLOWPASSWORDCHANGE],[ALLOWPRICEOVERRIDE],[ALLOWRESETPASSWORD],[ALLOWRETRIEVEORDER],[ALLOWSALESTAXCHANGE],[ALLOWTENDERDECLARATION],[ALLOWTRANSACTIONSUSPENSION],[ALLOWTRANSACTIONVOIDING],[ALLOWXREPORTPRINTING],[ALLOWZREPORTPRINTING],[MANAGERPRIVILEGES],[MAXIMUMDISCOUNTPCT],[MAXLINEDISCOUNTAMOUNT],[MAXLINERETURNAMOUNT],[MAXTOTALDISCOUNTAMOUNT],[MAXTOTALDISCOUNTPCT],[MAXTOTALRETURNAMOUNT],[NAME],[POSITION],[POSPERMISSIONGROUP],[RECID],[USEHANDHELD] INTO [#dbo_RETAILPOSITIONPOSPERMISSION_6a5067cd-ba66-41c4-bf8f-da7dc918b25e] FROM [dbo].[RETAILPOSITIONPOSPERMISSION];
 ---> System.Data.SqlClient.SqlException: Invalid column name 'ALLOWPASSWORDCHANGE'.

 

 

Another feature is the “Keep Packages Files”. It keeps the files in the working folder for later review. Again, this will leave you with a lot of data in the directory.

With the Keep Package Files set you get a couple of files in the work folder with the .tmp extension. One that ends with an I and one that ends with an R. The I file is the definition of the data model and the R file is the actual data. As is they are close to unreadable. So to get the result we can use the Pack Viewer tool. It is started from the Start menu next to the Service Settings.

RetailCDXPackViewer

Select the file you want to convert. The target folder is automatically filled in. You can change it without any problems. Mark the Open Folder to open the target folder when done and click the Convert button.

The I file gives you one file that looks like this:

RetailCDXPackDataModel

Converting the R file you get a multiple files depending on the number of tables in the package:

RetailCDXPackViewData

They are named with the table name for easy access. The SCTargetRequestHeader file is a content and action overview. The data files are easy(-ish) read XML files with the complete data set to be transferred.

RetailCDXPackViewDataContents

Remember to disable the keep package flag when done and restart the service.

 

This is not the full and complete troubleshooting guide but a quick intro to a couple of entry points when struggling with an AX 2012 R2 Retail.

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.