Reset tts level - An unbalanced x++ ttsbegin/ttscommit pair has been detected

Sometimes a user may receive the message of "An unbalanced X++ ttsbegin/ttscommit pair has been been detected"

The following job will clear the issue and reset the tts level. I did not create this job originally and it is wildly used if you do a simple search on the internet. I am just posting this on here because I find myself looking for the script every now again 

static void resetTTS(Args _args)

{

    if (appl.ttsLevel() > 0)

    {

        info(strFmt("Level %1 aborted", appl.ttsLevel()));

        ttsAbort;

    }

}

Get all users missing a worker relationship assignment or retail channel

I found that sometimes when setting up a large amount of users in AX for retail it can lead to sometimes missing a worker relation configuration or missing a retail channel assignment. When this happens it wont break anything but some odd ball things can start to happen. I use the following job to make sure all of the users have at least a worker assignment and are assigned to a retail channel (when valid)

static void CheckUserSetup(Args _args)
{
    UserInfo userNames;
    DirPersonUser userRelations;
    MCRChannelUser retailChannelUsers;


    //find any users that are missing either a user relation or retail channel setup
    while select * from userNames
        outer join userRelations
        where userRelations.User == userNames.id
        outer join retailChannelUsers
        where retailChannelUsers.User == userNames.id
    {
        //check to see if there is a user relation
        if(!userRelations)
        {
            info(strFmt("No User Relation: %1 (%2)", userNames.id, userNames.name));
        }

        //check to see if the user is in a retail channel
        if(!retailChannelUsers)
        {
            info(strFmt("No Retail Channel: %1 (%2)", userNames.id, userNames.name));
        }
    }
}

Database index analysis

I find myself fine tuning the ax database more frequently to get the best possible performance. I have noticed that sometimes the default dba scripts to rebuild, reorganize indexes and update statistics for tables don't always run properly.

I use the following to break down the current avg fragmentation for a specific table

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N',<database name>'),
      OBJECT_ID(N'dbo.<table name>'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b
      ON a.object_id = b.object_id AND a.index_id = b.index_id; 

I also use the following script to look at overall indexes which  may need attention. The below will generate the commands to rebuild or reorganize a fragmented index along with updating a specific tables index instead of relaying on the dba scripts.

DECLARE @fragmentPercent int = 30;
SELECT OBJECT_NAME(ind.OBJECT_ID) AS Tablename,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REBUILD;' as RebuildIndex,
'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE;' as ReorganizeIndex,
'UPDATE STATISTICS ' + OBJECT_NAME(ind.OBJECT_ID) + ' WITH FULLSCAN;' as UpdateStatics
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >= @fragmentPercent  and indexstats.index_type_desc <> 'HEAP'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

R3 Retail Async Client + Server Error: Unable to communicate with server for upload. Please check username/password, server and database connections.

Recently I came across a strange error when dealing with the new Async Server + Client feature within AX 2012 R3 to support a channel database for a retail store/pos setup.

This environment had been working just fine with no errors for the past couple of months but I just noticed that none of the download/upload files were being processed by the server and client for the past couple of days. Nothing had changed in the AX, SQL or account access that pertained to this environment.

When looking in the event login I saw the following


CDX Client: Unable to communicate with server for upload. Please check username/password, server and database connections. Error Details: System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail. ---> System.ServiceModel.FaultException: Invalid username or password
   --- End of inner exception stack trace ---

CDX Server: Invalid username or password

However I knew that my username/password combination was valid and my certs for the aos + cdx server were still valid.

After doing some basic troubleshooting I found that the following fixed the error on both the server + client.

Retail > Setup > Parameters > Retail scheduler parameters > Sync metadata

I then restarted the async client service + did an issreset on the async server. I am not sure the last part was necessary but restarting them forces it to try to reconnect to AX.

AX 2012 R3 - Retail POS - Clear all existing transactions from the POS DB/CDX Server/CDX Client

When reloading environments sometimes it is necessary to reset the retail pos, cdx server, cdx client and back office databases to show none of the previous transactions that once existed within the system.

In order to clear it out we need to do the following (with CDX server + client services offline)

POSDB - AXRetailPOS

--current transactions within the pos system

truncate table ax.RetailPosBatchAccountTrans

truncate table ax.RetailPosBatchTable

truncate table ax.RetailPosBatchTenderTrans

truncate table ax.RetailTransactionBankedTenderTrans

truncate table ax.RetailTransactionTenderDeclarationTrans

truncate table ax.RetailTransactionDiscountTrans

truncate table ax.RetailTransactionIncomeExpenseTrans

truncate table ax.RetailTransactionInfocodeTrans

truncate table ax.RetailTransactionLoyaltyRewardPointTrans

truncate table ax.RetailTransactionOrderInvoiceTrans

truncate table ax.RetailTransactionPaymentTrans

truncate table ax.RetailTransactionSafeTenderTrans

truncate table ax.RetailTransactionSalesTrans

truncate table ax.RetailTransactionTable

truncate table ax.RetailTransactionTaxTrans

CDX_AsyncClient DB

--clear previous syncs

truncate table dbo.datastore

truncate table dbo.downloadsession

truncate table dbo.downloadsessionLog

truncate table dbo.offlinedataexportlog

CDX_AsyncServer DB

--clear previous syncs

truncate table dbo.UPLOADSESSIONLOG

truncate table dbo.UPLOADSESSION

truncate table dbo.DOWNLOADSESSIONDATASTORELOG

truncate table dbo.DOWNLOADSESSIONDATASTORE

truncate table dbo.DOWNLOADSESSION

--clear base info (optional)

truncate table dbo.FILESTORAGEPROVIDER

truncate table dbo.JOB

truncate table dbo.SCHEDULERINTERVAL

truncate table dbo.SCHEDULEPARAMETER

truncate table dbo.SCHEDULEJOB

truncate table dbo.SCHEDULEDATAGROUP

truncate table dbo.DATASTOREHEARTBEATLOG

truncate table dbo.DATASTORE

truncate table dbo.DATAGROUP

truncate table dbo.AUTHENTICATIONLOG

If there are transaction with the back office AX then you may need to do the following

Company/Retail/Journals/Open statements > remove all statements that pertain to the pos system

Then run the following job

static void ClearRetailTransactions(Args _args)

{

    RETAILTRANSACTIONBANKEDTENDERTRANS retailTable1;

    RETAILTRANSACTIONTENDERDECLARATIONTRANS retailTable2;

    RETAILTRANSACTIONDISCOUNTTRANS retailTable3;

    RETAILTRANSACTIONINCOMEEXPENSETRANS retailTable4;

    RETAILTRANSACTIONINFOCODETRANS retailTable5;

    RETAILTRANSACTIONLOYALTYREWARDPOINTTRANS retailTable6;

    RETAILTRANSACTIONORDERINVOICETRANS retailTable7;

    RETAILTRANSACTIONPAYMENTTRANS retailTable8;

    RETAILTRANSACTIONSALESTRANS retailTable9;

    RETAILTRANSACTIONTABLE retailTable10;

    RETAILTRANSACTIONTAXTRANS retailTable11;

    

    ttsbegin;

    delete_from retailTable1;

    delete_from retailTable2;

    delete_from retailTable3;

    delete_from retailTable4;

    delete_from retailTable5;

    delete_from retailTable6;

    delete_from retailTable7;

    delete_from retailTable8;

    delete_from retailTable9;

    delete_from retailTable10;

    delete_from retailTable11;

    ttscommit;

    info('done');

}

Finally you will want to remove all of the previous cdx sync files located in 

Company/Retail/Setup/Retail scheduler/Channel integration/working folders

Once this is completed you should follow the normal store setup if it is not already done.

Note: if you run the sql under --clear base info (optional) you will need to run the sync metadata function (button) that is located @

Company/Retail/Setup/Parameters/Retail scheduler parameters

Update: depending on if you are transferring data from one environment to the next  you may run into an issue where the data will download but wont upload you may need to clear out/update the following tables depending on what you are trying to do.

Clearing these tables will force a complete refresh of all pos transactions into the back office.

POSDB - AXRetailPOS:
truncate table [crt].[TABLEREPLICATIONLOG]
truncate table [crt].[UPLOADSESSION]