Pages

Tuesday, August 7, 2012

Database log throws error "Error executing code: Wrong argument types for comparison."

So we have had our database log turned on since AX 3.0, and we've upgraded to AX 4.0, then AX 2009.  An ongoing issue has been with the database log throwing "Wrong argument types for comparison", and I've finally gotten around to diagnosing it, and it seems like it could be a very common issue caused by an upgrade.

Error executing code: Wrong argument types for comparison.
(C)\Data Dictionary\Tables\SysDataBaseLog\Methods\getDataAslist - line 28
(C)\Forms\SysDatabaseLog\Methods\buildContents - line 30
(C)\Forms\SysDatabaseLog\Data Sources\DBHistory\Methods\active - line 7


The actual issue is this, when we were in AX 3.0, we made a chance to a record, which was logged to Tables\SysDatabaseLog and had the ModifiedDate stored as a type of Date.  Somewhere along the AX 3.0 to AX 4.0 to AX 2009, this data wasn't upgraded properly.

When the database log compare form tries to work with it, it's expecting to work with two utcDateTime objects for comparison, but it gets a Date and fails.

You can put this code in Tables\SysDatabaseLog\getDataAslist at around line 30 and see the issue in the debugger:

if (typeOf(conpeek(tmp, 2)) == Types::Date      &&
    dictField.baseType()    == Types::UtcDateTime)
{
    breakpoint;
}

You can write a hack with conPoke(...) and DateTimeUtil::newDateTime(...) to replace the Date with a utcDateTime, but you'll run into issues in other methods.  The best thing to do is correct the data and I wrote a job to do just that.  One specific thing to note, this is just proof of concept code...for our actual corrective actions, I wrote a class and table to do batches for various reasons since we have nearly 4 million records.  As always, run at your own risk and test.  Enjoy:




static void DatabaseLogFix(Args _args)
{
    SysDataBaseLog  log;
    container       tmp;
    container       newData;
    SysDictField   dictField;
    fieldId         fieldId;
    int             i, idx;
    boolean         updateCon;
    boolean         updateData;
    Counter         counter;
    #Properties;
    ;

    //select firstonly log where log.RecId == 5638018469;
    
    ttsbegin;

    while select forupdate log
        order by CreatedDateTime, RecId
    {
        updateData = false;
        if (typeof(conpeek(log.Data, 1)) == Types::Container)   //Version 3.0 format
        {
            newData = log.Data;

            for (i=conlen(log.Data); i; i--)
            {
                updateCon = false;
                tmp = conpeek(log.Data, i);
                fieldId = conpeek(tmp, 1);
                dictField = new SysDictField(log.Table, fieldId);
                if (dictField)
                {
                    if (dictField.name() == #PropertyModifiedTime
                    || dictField.name() == #PropertyCreatedTime)
                    {
                        continue;
                    }

                    // Type mismatch for "New" value
                    if (typeOf(conPeek(tmp, 2)) != dictField.baseType())
                    {
                        // If it's a date that should be a utcDateTime, upgrade it
                        if (typeOf(conpeek(tmp, 2)) == Types::Date      &&
                            dictField.baseType()    == Types::UtcDateTime)
                        {
                            tmp = conPoke(tmp, 2, DateTimeUtil::newDateTime(conPeek(tmp, 2), 0));
                            updateCon = true;
                        }
                        // Otherwise throw an error
                        else
                        {
                            info(strfmt("[RecId %1][TableID %5][FieldId %2][2 NEW] Type mismatch (%3/%4)", log.RecId, fieldId, typeOf(conPeek(tmp, 2)), dictField.baseType(), log.table));
                        }
                    }

                    // Type mismatch for "Old" value
                    if (typeOf(conPeek(tmp, 3)) != dictField.baseType())
                    {
                        // If it's a date that should be a utcDateTime, upgrade it
                        if (typeOf(conpeek(tmp, 3)) == Types::Date      &&
                            dictField.baseType()    == Types::UtcDateTime)
                        {
                            tmp = conPoke(tmp, 3, DateTimeUtil::newDateTime(conPeek(tmp, 3), 0));
                            updateCon = true;
                        }
                        // Otherwise throw an error
                        else
                        {
                            info(strfmt("[RecId %1][TableID %5][FieldId %2][3 OLD] Type mismatch (%3/%4)", log.RecId, fieldId, typeOf(conPeek(tmp, 3)), dictField.baseType(), log.table));
                        }
                    }

                    if (updateCon)
                    {
                        updateData = true;
                        newData = conPoke(newData, i, tmp);
                    }
                }
            }

            if (updateData)
            {
                counter++;
                log.Data = newData;
                log.update();
            }
        }
    }
    ttscommit;
    
    select count(RecId) from log;

    info(strfmt("%1 updates made against %2 records", counter, log.RecId));


}

Tuesday, June 26, 2012

How to find the maximum number of logged on users on a given day...

A very typical question that every client faces during their yearly Microsoft renewal is "How many user licenses are we actually using?"  There is no sense in paying for an extra 30 users that you don't need.

I wrote this simple job to find the maximum number of users logged in during a working day for the last 90 days.  You will most likely need to tweak the "addHours" functions for your working days.  I was having all sorts of problems with UTC and timezone offset, so I just added numbers until it was right.

This checks max users for every hour because my original task was to show user count by hour.  You can easily modify this to check every second or whatever if you need a much more detailed number.



static void JobFindMaxUsersLoggedInPerDay(Args _args)
{
    SysUserLog      sysUserLog;
    utcDateTime     utc = DateTimeUtil::addHours(DateTimeUtil::newDateTime(systemDateGet(), 0), 8);
    int             i;
    int             n;
    int             iUsers;
    ;

    utc = DateTimeUtil::newDateTime(systemDateGet(), 0);

    utc = DateTimeUtil::addDays(DateTimeUtil::addHours(utc, 13), -13);
//    utc = DateTimeUtil::applyTimeZoneOffset(utc, DateTimeUtil::getUserPreferredTimeZone());

    for (i=1; i<=90; i++)
    {
        iUsers = 0;
        for (n=1; n<=11; n++)
        {
            // Find the number of users logged in
            select count(recId) from sysUserLog
                where sysUserLog.createdDateTime < DateTimeUtil::addHours(utc, n)      &&
                      sysUserLog.LogoutDateTime  > DateTimeUtil::addHours(utc, n)      &&
                      sysUserLog.LogoutDateTime;

            if (!iUsers || sysUserLog.RecId > iUsers)
                iUsers = sysUserLog.RecId;
        }

        info(strfmt("%1, %2", DateTimeUtil::date(utc), iUsers));
        utc = DateTimeUtil::addDays(utc, -1);
    }
}

Tuesday, March 27, 2012

Job to export all AX 2009 security groups to files

I have been changing security a bunch lately and it's nice to have a backup of your previous settings if you make a mistake.  Here is a quick job I wrote to export your existing security settings to a folder.



static void ExportSecurityGroups(Args _args)
{
    SysSecurityUserGroup    sysSecurity = SysSecurityUserGroup::construct();
    UserGroupInfo           userGroupInfo;
    DomainInfo              domainInfo;

    #file
    Dialog                dialog  = new Dialog("@SYS14863");
    DialogField           dialogFileName;
    DialogField           dialogDomain;
    Object                formdialog;
    ;

    dialogFileName = dialog.addField(typeid(FilePath),"@SYS16423");
    dialogDomain    = dialog.addField(typeid(DomainId), "@SYS89852");
    dialog.doInit();
    formdialog = dialog.formRun();
    formdialog.filenameLookupTitle("@SYS53669");

    if (dialog.run() && WinAPI::pathExists(dialogFileName.value())  &&
        (dialogDomain.value() == ''     ||
         (select firstonly domainInfo where domainInfo.Id == dialogDomain.value()).recId != 0))
    {
        if (dialogDomain.value() == '')
            dialogDomain.value('Admin');
        
        while select userGroupInfo
        {
            sysSecurity.parmUserGroupId(userGroupInfo.id);
            sysSecurity.parmDomainId(dialogDomain.value());
            sysSecurity.load();
            sysSecurity.export(dialogFileName.value() + @'\' + dialogDomain.value() + '_' + userGroupInfo.id + '.asg');
        }
    }
    else
        warning("No action taken...");

    info("Done");
}

Wednesday, February 22, 2012

Reflection and recursion on the AOT to compare projects.

I wrote this really cool job a year or two ago to solve a fairly common, specific problem.  This job solves many more problems I never anticipated though.

The consulting company I worked for made it their best practice to make sure that every object they modified was in a project.  Our "Customization" project...the problem here is that not ever developer consistently remembers to put every object in the project 100% of the time, so import/export between environments would sometimes be missing objects.

So I use the base layer compare tool via Tools>Development tools>Version update>Compare layers to create a project of the "CUS" layer (normally the VAR layer for me).  This gets all of the objects modified in the CUS layer in one project.  Then I set that in the #layerCompareProject macro, then add my projects I want to check against in the lower lines of code.

I've used this tool countless times to compare two projects.  Another use I had was during an upgrade to Roll Up 7 from Roll Up 1.  Somehow I had deleted a modification to an obscure table...this made me worried that I could have accidentally deleted other objects that I would have no idea about.  To check this, I went into Live and created a layer compare project of the CUS layer, then went into my upgraded RU7 environment and made the same layer compare project.  Then all I had to do was run the job and it output the objects that were missing.

I think it's clever/fun the way I wrote it too using recursion to reflect on the AOT.  It's basic recursion for traversal.




/*
    How to use:
    Create a compare project of the layer you want to check and make it SHARED!

    MAKE IT SHARED
    MAKE IT SHARED
    MAKE IT SHARED

    Change the constant "layerCompareProject" to the name of the SHARED layer project
        you just created

    Modify the first line from the main body of the job to be the projects you're searching
    customProject           = infoLog.projectRootNode().AOTfindChild('Shared').AOTfindChild('Customizations');
    traverseAndUpdateMap    (customProject.getRunNode().AOTiterator());

    Just repeat those two lines to add more projects...it will update and search them
*/
static void ProjReflection(Args _args)
{
    #define.layerCompareProject('CUS_Live')

    ProjectNode layerCompareProject;
    ProjectNode customProject;

    Map map = new Map(Types::String, Types::Integer);
    MapEnumerator enumerator;

    void traverseAndBuildMap(TreeNodeIterator _tni)
    {
        ProjectNode pn = _tni.next();
        ;

        while (pn)
        {
            if (!pn.applObjectType())
            {
                // Recursively traverse the project
                traverseAndBuildMap(pn.AOTiterator());
            }
            else
            {
                // Fill the map with every object we found
                map.insert(pn.treeNodePath(), 0);
            }

            pn = _tni.next();
        }
    }

    void traverseAndUpdateMap(TreeNodeIterator _tni)
    {
        ProjectNode pn = _tni.next();
        ;

        while (pn)
        {
            if (!pn.applObjectType())
            {
                // Recrusively traverse the project
                traverseAndUpdateMap(pn.AOTiterator());
            }
            else
            {
                if (map.exists(pn.treeNodePath()))
                {
                    // We found an object in a project that already exists in the map
                    // ...mark it as found [1]
                    map.insert(pn.treeNodePath(), 1);
                }
            }

            pn = _tni.next();
        }
    }

    ;

    layerCompareProject = infoLog.projectRootNode().AOTfindChild('Shared').AOTfindChild(#layerCompareProject);
    traverseAndBuildMap(layerCompareProject.getRunNode().AOTiterator());


    // Modify these lines to search projects
    customProject           = infoLog.projectRootNode().AOTfindChild('Shared').AOTfindChild('Customization');
    traverseAndUpdateMap    (customProject.getRunNode().AOTiterator());

    /*
    // Add more lines if you want to search more
    customProject           = infoLog.projectRootNode().AOTfindChild('Shared').AOTfindChild('InstallProj');
    traverseAndUpdateMap    (customProject.getRunNode().AOTiterator());

    customProject           = infoLog.projectRootNode().AOTfindChild('Shared').AOTfindChild('BoltOn');
    traverseAndUpdateMap    (customProject.getRunNode().AOTiterator());
    */

    enumerator = map.getEnumerator();

    info("The following objects from [" + #layerCompareProject + "] were not found in searched projects.");
    while (enumerator.moveNext())
    {
        if (!enumerator.currentValue())
            info(strfmt("%1", enumerator.currentKey()));
    }
}

Monday, January 30, 2012

How to run AX 2012 HyperV on VirtualBox

In order to run the AX 2012 Beta VPC, it usually requires HyperV, or attaching the disk locally.  This is a great solution I was told about to run it locally.

http://dynamicsnavax.blogspot.com/2011/04/how-to-run-ax2012-hyperv-on-virtualbox.html

The basic idea is you just create a virtual machine, mount the three *.vhd's, and start it.  This blog post highlights two important caveats that you will come across.

  1. If you use the "Create new virtual machine wizard" and check the box to use an existing disk, it defaults the VHD as a SATA controller, which will result in the attached blue screen error over and over.  You need to attach all three disks as IDE controllers
  2. Sharepoint apparently doesn't work without adding to the hosts file
This is the blue screen I kept hitting over and over...very frustrating before seeing this blog post for the solution.

Wednesday, January 25, 2012

How to send emails from AX without requiring Outlook

Sending emails from AX has been somewhat of a pain when it tries to use Outlook.  This post is a simple code modification to one method in \Classes\Info\reportSendMail.  I did not develop this code, I merely tweaked it.  The original poster's blog has disappeared, and I can only find non-working remnants all around the web of this, but it is just too useful not to repost.

If you have Outlook 64bit edition, you might get the "Either there is no default mail client or the current mail client cannot fulfill the messaging request.  Please run Microsoft Outlook and set it as the default mail client."  Followed by an AX MAPI error.

Or sometimes you may get the "A program is trying to access e-mail address information stored in Outlook."...Allow/Deny/Hlep.

This change basically bypasses outlook.  Put it in and give it a shot.



void reportSendMail(PrintJobSettings p1)
{
    //SysINetMail m = new SysINetMail();
    System.Net.Mail.MailMessage mailMessage;
    System.Net.Mail.Attachment attachment;
    System.Net.Mail.AttachmentCollection attachementCollection;
    System.Net.Mail.SmtpClient myMail;
    System.Net.Mail.MailAddress mailFrom;
    System.Net.Mail.MailAddress mailTo;
    str userMailAddress;
    str receiverMailAddress;
    str mailBody;
    str smtpServer;
    fileNameOpen fileNameForEmail;
    str mail;
    FileIOPermission perm;
    userinfo userInfo;
    //end Declaration
    str fileName = 'axaptareport';

    ;
    if (p1.format() == PrintFormat::ASCII)
        fileNameForEmail = subStr(p1.fileName(),strLen(p1.fileName())-3,-999)+'TXT';
    else if (p1.format() == PrintFormat::RTF)
        fileNameForEmail = subStr(p1.fileName(),strLen(p1.fileName())-3,-999)+'RTF';
    else if (p1.format() == PrintFormat::HTML)
        fileNameForEmail = subStr(p1.fileName(),strLen(p1.fileName())-3,-999)+'HTM';
    else if (p1.format() == PrintFormat::PDF || p1.format() == PrintFormat::PDF_EMBED_FONTS)
        fileNameForEmail = subStr(p1.fileName(),strLen(p1.fileName())-3,-999)+'PDF';

    mail = subStr(fileNameforEmail,(strlen(fileNameforEmail)-8),9);

    select firstonly name from userInfo where userInfo.id == SysuserInfo::find().Id; // to find the user name

    if (isRunningOnServer())
        fileNameforEmail = winApiServer::getTempPath() + mail; // store attachment in a temp location
    else
        fileNameforEmail = winApi::getTempPath() + mail; // store attachment in a temp location


    perm = new FileIOPermission(fileNameforEmail,'w');
    if(!perm)
    {
        throw error("Cannot move attachment to temp location.");
        return;
    }
    try
    {
        perm.assert();
    }
    catch
    {
        throw error("Cannot gain access to Temp location.");
        return;
    }

    userMailAddress = SysUserInfo::find().Email; // find current users email address setup up in user //options
    receiverMailAddress = p1.mailTo();

    mailFrom = new System.Net.Mail.MailAddress(userMailAddress,userInfo.name);

    mailTo = new System.Net.Mail.MailAddress(receiverMailAddress,"");

    mailBody = "Email sent from " + CompanyInfo::name() + ", using Dynamics AX";

    smtpServer = SysEmaiLParameters::find(false).SMTPRelayServerName;// using the SMTP server ip //setup in email Parameters

    mailMessage = new System.Net.Mail.MailMessage(mailFrom,mailTo);
    mailmessage.set_Subject(p1.mailSubject());
    mailmessage.set_Body(mailBody);

    //move attachment file to Temp folder, might need to create WinAPIServer::moveFile
    winapi::moveFile(p1.fileName(), fileNameforEmail);

    attachementCollection = mailMessage.get_Attachments();
    attachment = new System.Net.Mail.Attachment(fileNameforEmail);
    attachementCollection.Add(attachment);

    myMail = new System.Net.Mail.SmtpClient(smtpServer);
    mymail.Send(mailmessage);

    mailmessage.Dispose();
    attachment.Dispose();

    if (isRunningOnServer())
        WinAPIServer::deleteFile(fileNameForEmail);
    else
        winApi::deleteFile(fileNameforEmail);

    CodeAccessPermission::revertAssert();
}

/*
void reportSendMail(PrintJobSettings p1)
{
    SysINetMail m = new SysINetMail();

    str fileName = 'axaptareport';

    if (p1.format() == PrintFormat::ASCII || p1.format() == PrintFormat::TEXTUTF8)
        fileName = fileName + '.txt';
    else if (p1.format() == PrintFormat::RTF)
        fileName = fileName + '.rtf';
    else if (p1.format() == PrintFormat::HTML)
        fileName = fileName + '.htm';
    else if (p1.format() == PrintFormat::PDF || p1.format() == PrintFormat::PDF_EMBED_FONTS)
        fileName = fileName + '.pdf';

    m.sendMailAttach(p1.mailTo(),p1.mailCc(), p1.mailSubject(),'Medulla Report', true, p1.fileName(), fileName);
}
*/