List AOSs Supported by SQL Server Instance

I recently found myself in the situation of needing to know what Dynamics AX Application Object Servers (AOS) are online in an environment.  The situation was a bit simplified because all of the AOS were served by the same SQL Server.  So I set out to build a SQL script which would query all of the relevant AX databases, look at the online servers and clients then summarize what it found.  After some tinkering I was able to create the following result:

Here is the query should you want to use it!

• List all AOS.sql

Bottomline PrecisionForms: Cannot Send Failed Email Notice

The Bottomline PrecisionForms Email service can send the helpdesk an email whenever an error is encountered.  It is important that you configure this correctly otherwise errors will be shown in the trace log.

BTEmail,No from address available for SMTP, cannot send failed email notice

Steps to correct or configure

1. Open Bottomline PrecisionForms Email

2. Click Administration > Configure email

3. Validate/fill out the "On failures send email to"

4. Click SMTP button

5. Validate/fill out the "Default sender name" to be the email form which errors are sent.

Workflow Error: Application Cannot Be Started

Recently I was trying to set up a workflow in AX 7 / Dynamics 365 for Operations.  I was unable to open the workflow editor.

After clicking the details button I found the following:

Deployment Identity                : Microsoft.Dynamics.AX.Framework.Workflow.WorkflowEditorHost.application, Version=7.0.4307.16141, Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=amd64

...

System.Deployment.Application.InvalidDeploymentException (Zone) - Deployment and application do not have matching security zones.

The fix in this case was to add AX as a trusted web site in the Internet Explorer Internet Options and to use Internet Explorer instead of Chrome:

I also found there are other common issues with the workflow editor.  If adding AX as a trusted site did not help, you might try looking at the following post:
https://organicax.com/2016/10/19/working-with-the-workflow-editor-download-in-ax7/

Bottomline PrecisionForms Email: File Did Not Contain an Email Header

Bottomline PrecisionForms is a powerful tool for formatting and delivering beautiful reports and forms (invoice, PO, BOL, RFQ...) from Dynamics AX.  I have been using this tool for almost ten years now.  You might encounter the following issue when setting up the Bottomline PrecisionForms Email server.

File did not contain an email header or errors were encountered.

First step is to review the spooler file which the printer was trying to process:

C:\Bottomline Technologies\PrecisionForms\spool

As shown in the screen shot, the file is a PDF file with a .tmp extention.  The way that PrecisionForms knows where to send the file is that it will add some text within the .tmp file with the email details.  These details should be passed from the Director project as part of resubmitting to a different queue.  The director project confirms it should be filled out:

The fix?
Open PrecisionForms Email and configure the email queue's default project and change the default merge configuration to "PDF with Job Ticket."

Optimizing installing ISV solutions in AX 2012 (Compare Tool)

When integrating a comprehensive ISV solution into your AX environment there is always the possibility that there is a conflict: perhaps the ISV solution was built for a different version of AX, or you already have several other solutions installed.  This means spending significant hours comparing objects, almost to the point that you could have coded the ISV solution yourself, LOL!

How can we make this process faster?  How can we optimize it?

Right click > Compare, click compare button, expand, expand...expand, expand, click, review, scroll...repeat 100 times.

Modifications to AX Compare Tool-
The first thing I noticed is that when I hit compare, the screen is so tiny that I'm constantly resizing it.
Modify the following to support the resolution you are using:  \Forms\SysCompareForm.init()
For my 4k monitor I found that using 600 by 900 enabled me to see as much code as possible.  So I added:

html.prefColumnSize(600, 900);
Tree.prefColumnSize(300, 900); // wider by 100px

You can also modify which two layers it immediately chooses.  In the past I have changed it so that the washed "layer" was never selected.

Also you could consider running the comparison in CIL.  It sounds great, however read it carefully because the generated code may not be what you expect.

The compare tool as it comes with AX already sets focus on the Compare button, so hitting enter will cause it to start the compare.  However you could modify the form to automatically hit the compare button after a second, saving you a few milliseconds.

What other hacks/tricks have you done to speed up this process?  Let me know in the comments.

Check and Fix the Next AX Record ID for All Tables

** This was tested on AX 4.0 SP2

The following script will check and update the next record ID for every table in AX.  This is useful if you copied data from one environment to another.  After running the script you must restart the AOS.  At the end you'll see a log of what it changed, for example:

ADDRESS did not need to be updated.
APPACTION did not need to be updated.
....

The SQL Statement:

SETNOCOUNT ON
DECLARE @COMPANY VARCHAR(3)
SET @COMPANY = '510'

IF (NOT EXISTS ( SELECT *
FROM DATAAREA
WHERE DATAAREA.ID = @COMPANY ))
BEGIN
PRINT 'ERROR: Company doesn''t exist'
RETURN
END

PRINT 'You must restart the AOS after running this script'

DECLARE @NUMROWS INT
DECLARE @TABLE_NAME SYSNAME
DECLARE @SQL VARCHAR(MAX)



DECLARE table_name_cursor CURSOR
-- Determine which tables have a RecID column

FOR

SELECT tables.name
FROM sys.tables WITH (NOLOCK)
WHERE EXISTS ( SELECT *
FROM sys.columns WITH (NOLOCK)
WHERE columns.OBJECT_ID = tables.OBJECT_ID
AND columns.NAME = 'DATAAREAID' )
AND EXISTS ( SELECT *
FROM sys.columns WITH (NOLOCK)
WHERE columns.OBJECT_ID = tables.OBJECT_ID
AND columns.NAME = 'RecID' )
AND EXISTS ( SELECT *
FROM SQLDICTIONARY WITH (NOLOCK)
WHERE SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = tables.name )
AND tables.NAME NOT LIKE 'AIF%'
AND tables.NAME NOT LIKE 'DEL_%'
AND tables.NAME <> 'SYSDATABASELOG'
AND tables.NAME <> 'SYSTEMSEQUENCES'
ORDER BY tables.NAME



OPEN table_name_cursor

FETCH NEXT FROM table_name_cursor
INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DECLARE @MaxRecID BIGINT
DECLARE @NextVal BIGINT

IF ((

SELECT COUNT(*)
FROM [' + @TABLE_NAME + '] WITH (NOLOCK)
WHERE [' + @TABLE_NAME + '].DATAAREAID = ''' + @COMPANY + '''
) > 0

AND (
SELECT COUNT(*)
FROM SYSTEMSEQUENCES WITH (NOLOCK)
INNER JOIN SQLDICTIONARY WITH (NOLOCK)
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ''' + @TABLE_NAME + '''
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID
) = 1)

BEGIN

SELECT @MaxRecID = MAX(RECID)
FROM [' + @TABLE_NAME + '] WITH (NOLOCK)
WHERE [' + @TABLE_NAME + '].DATAAREAID = ''' + @COMPANY + '''


SELECT @NextVal = NEXTVAL
FROM SYSTEMSEQUENCES WITH (NOLOCK)
INNER JOIN SQLDICTIONARY WITH (NOLOCK)
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ''' + @TABLE_NAME + '''
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

IF (@NextVal > @MaxRecID)

BEGIN

PRINT ''' + @TABLE_NAME + ' did not need to be updated.''
END

ELSE

BEGIN

PRINT ''Updated ' + @TABLE_NAME + ' from '' + CONVERT(VARCHAR(MAX), @NextVal) + '' to ''
+ CONVERT(VARCHAR(MAX), @MaxRecID + 1)

UPDATE SYSTEMSEQUENCES
SET NEXTVAL = @MaxRecID + 1
FROM SYSTEMSEQUENCES
INNER JOIN SQLDICTIONARY
ON SQLDICTIONARY.FIELDID = 0
AND SQLDICTIONARY.name = ''' + @TABLE_NAME + '''
AND SQLDICTIONARY.TABLEID = SYSTEMSEQUENCES.TABID

END

END'

--PRINT @SQL
EXEC (@SQL)



FETCH NEXT FROM table_name_cursor
INTO @TABLE_NAME
END

CLOSE table_name_cursor;
DEALLOCATE table_name_cursor;

Wrong Argument Types in Variable Assignment

During the development of a class occasionally we may receive this error message

wrong argument types in variable assignment

The error only occurs when during runtime.  I believe the issue is that the server has one copy of the class whereas your client has a different copy.  you can resolve the issue by clicking on the class and doing a full compile of the class.  If the class has a parent class (extends ___) then occasionally it is necessary to select the  parent class, right-click, and choose Add-ins > Compile forward.

Emailing embedded images from Dynamics AX

When I first tried sending an email with images it showed the standard image not found image from Microsoft internet exploder.

This was the image in all the emails sent by AX.

We are using AX 4.0 SP2 with hotfixes applied....I began to dig...I found that the image path is replaced with cid:1 because it is an embedded resource in the email.....then discovered two things needed to be true:

• Any images that you include in an email must exist in the directory specified at SysEmailParameters.AttachmentPath.
• You must change the code if the location is a shared directory starting with two forward slashes //

The code checking that the image exists in the correct directory has a BUG and well it is overly complex (KISS).  Just replace this method below...

public static boolean isFromAttachmentsFolder(str _pathName)
{
    str attachmentsFolder;
    str pathName;
    ;
    // Fix embedding images in emails
    attachmentsFolder = SysEmailParameters::find().AttachmentsPath;
    pathName = _pathName;

    attachmentsFolder = Global::strReplace(attachmentsFolder,'\\','/');
    pathName = Global::strReplace(pathName,'\\','/');

    // Fix embedding images in emails
    return Global::strStartsWith(strupr(pathName), strupr(attachmentsFolder));
}

SQL Server Reporting Services: Fixing "Could not generate a list of fields for the query"

Many times when creating an SSRS report I get the message "Could not generate a list of fields for the query" especially when using datasources which include temporary tables or very complex queries.  Sometimes clicking the Refresh fields button on the query toolbar does indeed fix the issue and other times it does not help.

I found the workaround for making it always work.

1. Close the design view of the report
2. Right-click the report and choose View code
3. Find the end of the SQL query that is giving you the error message...you can search for if you want.
4. Add the area with any  report parameters which this data source uses.
5. Save
6. Close
7. Reopen the report in design view

Determine table ID in SQL

Create this function to easily get the table number within a SQL statement.

-- =============================================
-- Create date: 2010.11.01
-- Description:    Gets the AX table ID for the table name
-- =============================================
ALTER FUNCTION [dbo].[fnAXTableID] 
(
    -- Add the parameters for the function here
    @tableName nvarchar(40)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @tableNum int

    -- Add the T-SQL statements to compute the return value here
    SELECT @tableNum = TableID
    FROM SQLDictionary
    WHERE [Name] = @tableName
        AND FieldID = 0
        AND Array = 0

    -- Return the result of the function
    RETURN @tableNum

END

Change default layers when comparing code

During the integration of system patches or third-party layers I found it annoying to continually select a certain layer when using the compare tool.  Multiply the time it takes by 400 or more nodes and you get an unhappy developer.
Fixing this problem is very easy.  Just add the following code to the end of the \Classes\SysCompare.initContext() method.

    if (comboBox1.getText(comboBox1.selection()) == comboBox2.getText(comboBox2.selection()) && comboBox2.items() > comboBox2.selection() + 1 )
    {
        comboBox2.selection(comboBox2.selection()+1);

        // 2010.11.30  Change default selectons for code comparison
        if (comboBox1.items() == comboBox2.items() - 1)
        {
            if (comboBox1.items() > 2 && Global::strEndsWith(comboBox2.getText(comboBox2.items() - 1), ' (Washed)')) // english only
            {
                // Compare last two layers (not selecting the washed version)
                comboBox1.selection(comboBox1.items() - 2); // Set first drop down to second-to-last possible option
                comboBox2.selection(comboBox2.items() - 2); // Set second drop down to second-to-last possible option
            }
        }
    }
    // 2010.11.30  Change default selections for code comparison when importing
    else if (comboBox1.items() > 1 && comboBox2.items() == 1 && Global::strEndsWith(comboBox2.getText(1 - 1), ' (xpo)'))
    {
        // Compare last layer to the imported XPO
        comboBox1.selection(comboBox1.items() - 1); // Set first drop down to last possible option
    }

Convert Axapta Time in SQL

AX 4.0 stores time in the database using the seconds since midnight.  So in order to view the time (military format) we must divide.  Here is an example:

SELECT TOP 20 StartTime AS [Seconds since midnight],

      CAST(StartTime/60/60 AS VARCHAR(2)) + ':' + RIGHT('0' + CAST(FLOOR((StartTime/60.0/60.0 %1)*60) AS VARCHAR(2)), 2) AS [Start Time]

FROM Batch

WHERE [Status] = 1

Disable users who are not active in Active Directory

Occasionally when auditors come by I like to disable all user accounts in AX which have been disabled in Active Directory.  Even though AD will not let them login auditors have a hard time understanding it, so I disable the users.  Many times we do not get notification that someone has left the company, or sometimes it does not reach the right people in charge of AX security.  So I made the job below which disables users in AX because they are disabled in Active Directory.  The job takes a little while to run.

static void disableUsersMissingInAD(Args _args)
{
    UserInfo                userInfoUpdate;
    xAxaptaUserManager      xAxaptaUserManager;
    xAxaptaUserDetails      xAxaptaUserDetails;
    #Guest
    
    xAxaptaUserManager = new xAxaptaUserManager();

    Global::startLengthyOperation();
    ttsbegin;

    while select forUpdate userInfoUpdate
    order by networkAlias
    where userInfoUpdate.Id != #GuestUser
       && userInfoUpdate.enable == 1
    {
        // Get the single user's details from the kernel class
        xAxaptaUserDetails = xAxaptaUserManager.getDomainUser(userInfoUpdate.NetworkDomain, userInfoUpdate.NetworkAlias);

        // Only show users who are enabled in Active Directory
        if (xAxaptaUserDetails == null || xAxaptaUserDetails.getUserCount() == 0 || !xAxaptaUserDetails.isUserEnabled(0))
        {
            userInfoUpdate.enable = 0;
            userInfoUpdate.update();
        }
    }

    ttscommit;
    Global::endLengthyOperation();
}

Line ###-Offset voucher does not exist in account ______.

If a vendor transaction is reversed but an AP check has been printed (and the payment journal not posted) you may get this error.  Essentially the record which was marked for settlement by the check has been deleted so it does not exist (VendTransOpen was deleted when the invoice was reversed).  If you go to the line in the accounts payable payment journal and click Inquiries > view marked transactions you will find it blank.  AX has lost the relationship of what invoices/credits were settled as part of the payment.  There are really two tables involved SpecTrans and VendTransOpen.

First thing we need to know is which transactions were settled by that payment.  The check will have the transactions shown in the Bank module.  Bank > Checks…select the check…Invoices button.  It lists all the transactions paid.  Go to the vendor transaction which was paid, click the Open button and remember the RecID of the VendTransOpen record for that transaction.

Find the reversal transaction and revoke the reversal (click the Reverse button when you have the reversal transaction selected).  The invoice should now have a balance like any other open invoice.

At this point if the SpecTrans record still exists you can fix it otherwise you must create it.  First open the SpecTrans table in the table browser and filter on SpecRecID = the journal line’s RecID.  If you find one then just fix the RefRecID to be the record ID of the VendTransOpen record.  Otherwise create the missing SpecTrans record by using a job.

static void Job1(Args _args)
{
    SpecTrans       specTrans;
    ;
    SpecTrans.clear();
    SpecTrans.initValue();
    SpecTrans.SpecTableId       = tablenum(LedgerJournalTrans);
    SpecTrans.SpecRecId         = 5638151884; // Record ID of the ledger journal line
    SpecTrans.LineNum           = 1.00;
    SpecTrans.Code              = "USD";
    SpecTrans.Balance01         = -78680.92; // Amount of the invoice which was paid
    SpecTrans.RefTableId        = tablenum(VendTransOpen);
    SpecTrans.RefRecId          = 5637622098; // Record ID of the open transaction
    SpecTrans.Payment           = NoYes::No;
    SpecTrans.PaymentStatus     = CustVendPaymStatus::Sent;
    SpecTrans.ErrorCodePayment  = "";
    SpecTrans.FullSettlement    = NoYes::No;
    SpecTrans.insert();
}

Get the next unique file name

Sometimes you are saving a temporary file so you don't want to delete or overwrite anything that already exists in a directory...there is a nice function to find the next unique file name.

fileNameTemp = Global::fileNameNext(fileOriginalsPath + fileName);