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.

Advertisements

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.

Locate duplicate values (or the opposite) in a table

The way X++ handles SQL statement often lacks a bit compared to what you can do in standard TSQL.

One of the areas is if you want to get a set of records from a table where a specific value in a field only occurs once in the table. You could traverse through the records and compare values as you go along. That could pull down performance significantly depending on the record count. Another way of doing it is to group by the field while counting the records in e.g. RecId. Then traversing this result set and look at the value in RecId could give you either the unique or not unique value depending on what you need.

A better way would be to let the SQL server do some of the work and consequently return as few records as possible. Here is a job that illustrates this and the group by version mentioned above.

AX 2012 queries support the HAVING clause. That can in some scenarios do the same and a bit more elegant than this.

 

static void DEMO_NoDuplicates(Args _args)
{
    CustGroup custGroup;
    CustGroup custGroup2;
    PaymTermId lastPaymTermId;

    info("TRAVERSE COUNT");

    while select count(recid) from custGroup
        group by PaymTermId
    {
        if (custGroup.RecId == 1)
        {
            while select custGroup2
                where custGroup2.PaymTermId == custGroup.PaymTermId
            {
                info(custGroup2.PaymTermId);
            }
        }
    }

    info("USE JOIN");
    
    while select custGroup
        order by PaymTermId
        notexists join custGroup2
            where custGroup.RecId != custGroup2.RecId
               && custGroup.PaymTermId == custGroup2.PaymTermId
    {
        info(custGroup.PaymTermId);
    }
}


Changing privileges on multiple SQL server tables for a user

This is not really a Dynamics AX thing but with BI in mind it kind of relates.

The thing is that sometimes you have users that needs access to AX tables and views on the SQL server to maintain and develop BI. And often these users should have access to almost every table except a few. Payroll tables are a good example.

In a normal situation you would use schemas to handle this but since AX creates all tables as part of the dbo schema this is not an option. So I created this little script for a colleague to accelerate the proces:

DECLARE @sqlStatement NVARCHAR(max)
DECLARE @user NVARCHAR(30)
DECLARE @filter NVARCHAR(10)

-- ==========
-- Initialize 
-- ==========

SET @sqlStatement = '';
SET @user = 'contoso\abc';
SET @filter = 'payroll%';

-- ================
-- Get REVOKE lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'REVOKE SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V')

-- ================
-- Get GRANT lines
-- ================

SELECT @sqlStatement = @sqlStatement + 'GRANT SELECT ON [' + NAME + '] TO [' + @user + '];' FROM SysObjects WHERE (TYPE = 'U' OR TYPE = 'V') AND NAME NOT LIKE '' + @filter + ''

-- ==============================
-- Execute privilege change query
-- ==============================

EXECUTE sp_executesql @sqlStatement;

What it does is that first it creates 3 variables. One for an SQL statement, one for the user identity and one for filtering what tables NOT to grant access to. In this case we want user ABC in the contoso domain to have SELECT rights on all tables except those beginning with “Payroll”.

It then starts by creating the REVOKE SELECT statements for all tables and views (User tables only) and adds them to the @sqlStatement variable.

Then it traverses through the tables and views to make the GRANT SELECT part on all tables NOT matching the filter in the @filter variable.

Finally the created statement is executed using the sp_executesql stored procedure.

It is not the fastest statement in the world; but it gets the job done.

Change which index is clustered – directly on the SQL server

Todays post is not exactly Dynamics AX relevant. However, in some cases where you need to do some immediate testing og hot fixing and you are not able to change it on a running AX environment you could end up making temporary index modifications directly on the SQL server.

The thing about this is that when you later on synchronise from Dynamics AX changes often are overwritten in that process. That can easily be ok as long as you are aware of it.

One of the things that needs a bit more work than just adding/removing indexes or fields is to change which index is clustered. For demonstration purposes I have created a small database called DemoDB with a table called PersonTbl.

It contains 3 fields and 2 indexes as shown here:

TableWithWrongClusteredIndex

The thing is that we in this case for some reason need to have the IdIdx as the clustered index and it is not an option to wait for the change to be released through the normal procedure in AX.

If we look at the clustered index NameIdx we cannot un-cluster it:

OldClusteredIndex

And clustering the IdIdx index is not possible from the properties form either:

NewClusteredIndex

To make this happen we need to modify the table through design mode. Right click on the table and select Design:

DesignTable

Right click anywhere on the columns overview and select Indexes/Keys:

SelectIndexesKeys

Now we can uncluster the NameIdx table like this:

UnclusterIndex

And make IdIdx clustered:

ClusterIndex

Click Close and save the table modifications.

Voila! The indexes have been changed. You might need to refresh to make it visible and should anyone be using the table and indexes while you make your customisations the saving part would fail.

TableWithCorrectClusteredIndex

 

Please remember that this procedure is not exactly best practice and should only be used as a last exit.

Using HAVING in a query

With AX 2012 we now have the option of using HAVING in a query. This allows us to limit a result set based on aggregated fields.

The advantage is that we let the SQL server do some filtering and receive less records compared to the old-school version were we had to receive all records and then use an IF or the likes to filter away the records that did not match the criterias.

In this simple example we want to get all sales ids on orders having lines with a line amount total of 100000 and beyond.

 

static void SimpleQueryWithHavingExample(Args _args)
{
    Query query;
    QueryRun queryRun;
    QueryBuildDataSource qBDS_SalesTable;
    QueryBuildDataSource qBDS_SalesLine;
    SalesTable salesTable;
    SalesLine salesLine;
 
    // Init query
    query = new Query();
 
    // Add datasources and use standard relations
    qBDS_SalesTable = query.addDataSource(tableNum(SalesTable));
    qBDS_SalesLine = qBDS_SalesTable.addDataSource(tableNum(SalesLine));
    qBDS_SalesLine.relations(true);
 
    // Add a group by on SalesTable
    qBDS_SalesTable.addGroupByField(fieldNum(SalesTable, SalesId));

    // Add aggregation on LineAmount
    qBDS_SalesLine.addSelectionField(fieldNum(SalesLine, LineAmount), SelectionField::Sum);
 
    // Add the having filter
    query.addHavingFilter(qBDS_SalesLine, fieldStr(SalesLine, LineAmount), AggregateFunction::Sum).value(SysQuery::range(100000, ''));
 
    // Create and run the queryRun object
    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesTable = queryRun.get(tablenum(SalesTable));
        salesLine = queryRun.get(tableNum(salesLine));
        info(strFmt("%1 %2", salesTable.SalesId, salesLine.LineAmount));
    }
}

 

I am sure that this will come in handy. 🙂

 

New feature in AX 2012 R3 – Inserting data in table directly from query

A new version/release of Dynamics AX is finally gone RTM and one of the new cool features is the ability to insert data from a query directly into a table. And why is that so clever you might think? Well, consider the performance boost of not having to loop through thousands of records including the roundtrips between SQL server, AOS server, client and back. That is why this is so interesting.

Here is a quick demo of how it works:

I have created a table called TESTQuery2Record. It contains 4 fields: CustGroupId, CustGroupName, PaymTermId and PaymTermDesc. It should be fairly obvious what to expect from these fields. 🙂

The purpose of the demo scenario is to get customer group data including the corresponding payment term information inserted in our test table. To do this I have written this job:

 

static void DEMO_Query_InsertRecordSet(Args _args)
{
    TESTQuery2Record testTable;
    Map fieldMapping;
    Query query;
    QueryBuildDataSource qbds_custGroup;
    QueryBuildDataSource qbds_paymTerm;
    QueryBuildFieldList fldList_custGroup;
    QueryBuildFieldList fldList_paymTerm;

    // Empty the target test table
    // ---------------------------
    delete_from testTable;

    // Build the query
    // ---------------
    query = new Query();
    qbds_custGroup = query.addDataSource(tableNum(CustGroup));
    qbds_paymTerm = qbds_custGroup.addDataSource(tableNum(PaymTerm));
    qbds_paymTerm.addLink(fieldNum(CustGroup, PaymTermId), fieldNum(PaymTerm, PaymTermId));

    // Field lists are required
    // ------------------------
    fldList_custGroup = qbds_custGroup.fields();
    fldList_custGroup.addField(fieldNum(CustGroup, CustGroup));
    fldList_custGroup.addField(fieldNum(CustGroup, Name));
    fldList_custGroup.dynamic(QueryFieldListDynamic::No);

    fldList_paymTerm = qbds_paymTerm.fields();
    fldList_paymTerm.addField(fieldNum(PaymTerm, PaymTermId));
    fldList_paymTerm.addField(fieldNum(PaymTerm, Description));
    fldList_paymTerm.dynamic(QueryFieldListDynamic::No);

    // Specify the mapping between target and source
    // ---------------------------------------------
    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupId), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, CustGroup)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, CustGroupName), [qbds_custGroup.uniqueId(), fieldStr(CustGroup, Name)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermId), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, PaymTermId)]);
    fieldMapping.insert(fieldStr(TESTQuery2Record, PaymTermDesc), [qbds_PaymTerm.uniqueId(), fieldStr(PaymTerm, Description)]);

    // Let AX handle getting data from the query to the target table
    // -------------------------------------------------------------
    query::insert_recordset(testTable, fieldMapping, query);

    // Done!
    // -----
}

So what is happening when we execute the above. Besides the delete_from we will see to queries executed on the SQL server.

1. Execution of the query and adding the data to a temporary table:

SELECT T1.CUSTGROUP AS f1,
       T1.NAME AS f2,
       T2.PAYMTERMID AS f3,
       T2.DESCRIPTION AS f4,
       N'usmf' AS DATAAREAID,
       1 AS RECVERSION,
       5637144576 AS PARTITION,
       IDENTITY(BIGINT, 1, 1) AS RECID
INTO [##ax_tmp_usmf8_113_103617]
FROM CUSTGROUP T1
CROSS JOIN PAYMTERM T2
WHERE (
        (T1.PARTITION = @P1)
    AND (T1.DATAAREAID = @P2)
      )
    AND (
    (
        (T2.PARTITION = @P3)
    AND (T2.DATAAREAID = @P4)
    )
    AND (T1.PAYMTERMID = T2.PAYMTERMID)
      )
ORDER BY T1.CUSTGROUP

2. Inserting the data from the temporary table in the target table

INSERT INTO TESTQUERY2RECORD (
    CUSTGROUPID,
    CUSTGROUPNAME,
    PAYMTERMID,
    PAYMTERMDESC,
    DATAAREAID,
    RECVERSION,
    PARTITION,
    RECID
 )
SELECT F1,
       F2,
       F3,
       F4,
       DATAAREAID,
       RECVERSION,
       PARTITION,
       RECID + 5637144596
FROM [##ax_tmp_usmf8_113_103617]

The effect of this is x number of records queried and inserted in 1 roundtrip to the SQL server and still based on a query.