Friday, March 11, 2016

Reporting Server Subscription does not run on Schedule

I've setup a SSRS report that pulls data from two SQL Server databases across two different SQL Server instances. It runs under a SQL Server user and works like charm when run manually. However, when I try to setup a Subscription for this report, the report never runs according to the schedule. I don't even get an error in the Reporting Services log.

In order to understand what is going on, I need to go back to the SQL Server Agent and check the logs there. When you create a Report Server Subscription on a Schedule it creates a new Job for this Schedule and this Job initiates the Event that triggers the scheduled report(s) to run.You can read more about it here.

First I need to identify what Job ID is behind the schedule I want to investigate. The Jobs are created using a Unique ID (GUID) and in order to link the actual report to its schedule ID I run this SQL on the Reporting Server Database (Normally called "ReportServer_INSTANCENAME"):

select s.Description, us.UserName, s.LastStatus, s.LastRunTime, c.Path, uc.UserName, rs.ScheduleId,
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId

Going back to the Job, I can see from the Job History it doesn't even get to the first step of the Job. Since this is AX, the Job is run under the same account as the Business Proxy Account. That is how SSRS normally is configured in relation to AX. The error from the history log actually says "the owner of job does not have server access". The job is actually set to run in the context of a user that does have server access, but somehow (a bug maybe) this is ignored by the SQL Server Agent.

The solution is:
  1. Change owner of the job to the SQL Server User that has the necessary permissions to run the report
  2. Grant the SQL Server User permissions to operate on the the Report Server Database (It does needs permissions to inject data, however I gave it full db_owner permissions)
  3. Test the job by doing a manual run
If the job still fails, you can investigate the Job history for any errors. 

Friday, March 4, 2016

Login failed error while processing OLAP cubes

I was requested to help with solving a processing error from SQL Server Analysis Services today.

While processing the engine fails to retrieve data from the Dynamics AX transaction database and throws the following error:
Login failed for user "DOMAIN\SERVERNAME$". Reason: Could not find a login mathcing the name provided (CLIENT: )"
(DOMAIN refers to the actual Active Directory Domain Name and SERVERNAME$ refers to the name of the server.



From the error it seems like the machine account is unable to login. Now in my scenario the SSAS service runs on the same server as the SQL Server Engine and the instance with the Dynamics AX database. In addition the SSAS service is NOT running using a dedicated Domain Service Account, but rather a local service account. The error then becomes sort of misleading if you read off the account name, because it is actually referring to machine account name.

The solution is simple, though.

Open the SQL Server Configuration Manager, find the Analysis Service you are using when processing the OLAP.


Open the properties and copy out the "Account name" which the service runs under. Normally this would be something like "NT Service\MSOLAP$INSTANCE_NAME" (INSTANCE_NAME refers to the name of the SSAS instance).


Now open SQL Server Management Studio, open Security and Logins. Add a new Login and paste in the Account name from previous step as the Login name.



Before you save this new login, open "User Mappings" and find the Dynamics AX database you are trying to use as source for your OLAP. Tick the checkbox in front of the database name and grant the login "db_datareader" role membership.


Now save the Login and jump back to the processing step and try run it again.

Saturday, December 12, 2015

Compiler warnings when using AXBUILD

When you run AXBUILD with AX2012 R3 you might notice there are some classes that throws warnings, even though you know they are not customized. Why is that?

Here is an example running CU10 and the following classes are flagged for investigation.

*** Bad element: \\Classes\AssetBookBonusMethod_IN
*** Bad element: \\Classes\PCImportModel
*** Bad element: \\Classes\VendInvoicePostBatchCleanup



The reason these classes throws warnings is the SysObsoleteAttribute where you can force compiler warnings with the second parameter.


There are system classes marked as obsolete and while you could argue they should be removed, there may be dependencies to them - out there somewhere -still.

Thursday, November 12, 2015

Error during install of Microsoft Report Viewer 2012

When installing the client for AX2012 R3 CU8 or later, one of the requirements is installing Report Viewer 2012. The prerequisite Validation step will inform you the component is missing, and it will provide the download link.

However, depending on your scenario you might need to download and install Microsoft System CLR Types for SQL Server 2012.

The error is "Setup is missing an installation prerequisite".


You can download the necessary components from Microsoft SQL Server 2012 Feature Pack.

Or you can download it directly using the following links:
32bit http://go.microsoft.com/fwlink/?LinkID=239643&clcid=0x409
64b http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409


Monday, June 1, 2015

Fixing firstDayOfWeek and firstWeekOfYear in AX2012

I was asked to have a look at why the date picker in AX still chose Sunday while the user expected Monday. I remember fixing this back in AX2009, so I was curious to see how this was solved in AX2012. The Global class and the methods firstDayOfWeek and firstWeekOfYear caught my attention. One of them used the current users language as key to find the best possible calendar setting, while the other picked the default system setting. Well, let us rewrite this and make it work a bit better, and since sharing is caring - here is how I solved it.

Global::firstWeekOfYear served as an inspiration, but I want it to differentiate between whatever languages my environment is serving. I can live with one cached result for each language, and the performance penalty is low and acceptable.


static int firstWeekOfYear()
{
    #WinAPI
    SysGlobalCache  cache   = classfactory.globalCache();
    int             clientFirstWeekOfYear;
    anytype         calendarWeekRuleValue;
    // Axdata.Skaue ->
    /*
    str             language;
    */
    str             language = currentUserLanguage();
    // Axdata.Skaue <-
    System.Globalization.CultureInfo        userCulture;
    System.Globalization.CalendarWeekRule   calendarWeekRule;
    System.Globalization.DateTimeFormatInfo userDateTimeFormat;

    // Axdata.Skaue ->
    /*
    if (cache.isSet(classStr(Global), funcName()))
    */
    if (cache.isSet(classStr(Global), funcName() + language))
    // Axdata.Skaue <-
    {
        // Axdata.Skaue ->
        /*
        clientFirstWeekOfYear = cache.get(classStr(Global), funcName());
        */
        clientFirstWeekOfYear = cache.get(classStr(Global), funcName() + language);
        // Axdata.Skaue <-
    }
    else
    {
        // Axdata.Skaue ->
        /*
        language = currentUserLanguage();
        */
        // Axdata.Skaue <-
        userCulture = new System.Globalization.CultureInfo(language);
        userDateTimeFormat = userCulture.get_DateTimeFormat();
        calendarWeekRule    = userDateTimeFormat.get_CalendarWeekRule();
        calendarWeekRuleValue = CLRInterop::getAnyTypeForObject(calendarWeekRule);

        switch(calendarWeekRuleValue)
        {
            case CLRInterop::getAnyTypeForObject(System.Globalization.CalendarWeekRule::FirstDay) :
                clientFirstWeekOfYear = 0;
                break;
            case CLRInterop::getAnyTypeForObject(System.Globalization.CalendarWeekRule::FirstFullWeek) :
                clientFirstWeekOfYear = 1;
                break;
            case CLRInterop::getAnyTypeForObject(System.Globalization.CalendarWeekRule::FirstFourDayWeek) :
                clientFirstWeekOfYear = 2;
                break;
        }

        // Axdata.Skaue ->
        /*
        cache.set(classStr(Global), funcName(),clientFirstWeekOfYear);
        */
        cache.set(classStr(Global), funcName() + language,clientFirstWeekOfYear);
        // Axdata.Skaue <-        
    }

    return clientFirstWeekOfYear;
}


Using the same ideas, I changed Global::firstDayOfWeek. Again, I allowed for one cached result for each language.
static int firstDayOfWeek()
{
    // Axdata.Skaue ->
    /*
    System.Globalization.DateTimeFormatInfo fi;
    */
    int dow;
    str             language = currentUserLanguage();
    System.Globalization.CultureInfo        userCulture;
    System.Globalization.DateTimeFormatInfo userDateTimeFormat;
    // Axdata.Skaue <-

    SysGlobalCache  cache   = classfactory.globalCache();
    int             clientFirstDayOfWeek;

    // Axdata.Skaue ->
    /* 
    if (cache.isSet(classStr(Global), funcName()))
    */
    if (cache.isSet(classStr(Global), funcName() + language))
    // Axdata.Skaue <-
    {
        // Axdata.Skaue ->
        /* 
        clientFirstDayOfWeek = cache.get(classStr(Global), funcName());
        */
        clientFirstDayOfWeek = cache.get(classStr(Global), funcName() + language);
        
    }
    else
    {
        // Axdata.Skaue ->
        userCulture         = new System.Globalization.CultureInfo(language);
        userDateTimeFormat  = userCulture.get_DateTimeFormat();
        dow                 = userDateTimeFormat.get_FirstDayOfWeek();        
        /* Removed
        fi = new System.Globalization.DateTimeFormatInfo();
        dow = fi.get_FirstDayOfWeek();
        */
        // Axdata.Skaue <-
        
        // The .NET API returns 0 for sunday, but we expect sunday to
        // be represented as 6, (monday is 0).
        clientFirstDayOfWeek = (dow + 6) mod 7;

        // Axdata.Skaue ->
        /*
        cache.set(classStr(Global), funcName(),clientFirstDayOfWeek);
        */
        cache.set(classStr(Global), funcName() + language,clientFirstDayOfWeek);
        // Axdata.Skaue <-
    }

    return clientFirstDayOfWeek;
}

So, for those of you who have an environment supporting potential multiple calendar setups, I recommend applying the fix above, or write your own fix. If you know a more efficient and better way, please comment below.

Tuesday, April 28, 2015

Reduce SSRS deployment time for static reports in AX2012

Are you wasting minutes deploying and redeploying static SSRS reports in all the languages provided with AX2012? If you only need a handful of them, you might just as well consider disabling the licenses for the unwanted languages. You can enable them back if you need them later.

Now disabling one language at a time manually might not be your cup of tea, so I would like to share a small job that disables all languages except the ones you want to keep enabled. Just create a new job and paste in the code below. Use at own risk of course, take backups and backups of the backups etc (you know the drill).

// Remove licence codes for unwanted languages
static void AdLanguageRemover(Args _args)
{
    SysConfig                   sysConfig;
    SysRemoveLicense            remLic;
    
    Query                       query;
    QueryBuildDataSource        qbd;
    QueryBuildRange             qbr;
    QueryRun                    queryRun;
    
    FormRun                     confirmForm;
    Set                         languagesToKeep = new Set(Types::String);
    Set                         licenseCodeSet  = new Set(Types::Integer);
    SetEnumerator               it;
    int                         confCount       = 0;
    boolean                     licenseChanged  = false;
    Args                        args            = new Args(formStr(SysLicenseCompareForm));    
    boolean                     proceed         = false;
    SysLicenseCodeDescription   codeDescription;
    str                         currentLanguageId;
    int                         pos, sysConfigId;    
    
    // List of languages to keep. Add, remove, change to fit your preference
    languagesToKeep.add('nb-no');
    languagesToKeep.add('en-us');
    languagesToKeep.add('sv');
    languagesToKeep.add('nl');
    languagesToKeep.add('fr');
    languagesToKeep.add('da');
    languagesToKeep.add('de');    
    
    query = new Query();
    qbd = query.addDataSource(tableNum(sysConfig));
    
    qbr = qbd.addRange(fieldNum(SysConfig,ConfigType));
    qbr.value(enum2Value(ConfigType::AccessCodes));
    
    qbr = qbd.addRange(fieldNum(SysConfig,Id));
    qbr.value(SysLicenseCodeReadFile::rangeLanguage());
    
    queryRun = new QueryRun(query);
    
    delete_from remLic;
        
    while (queryRun.next())
    {
        if (queryRun.changed(tableNum(sysConfig)))
        {
            sysConfig = queryRun.get(tableNum(sysConfig));
        }
        
        codeDescription     = SysLicenseCodeReadFile::codeDescription(sysConfig.Id);
        pos                 = strFind(codeDescription,'(',strLen(codeDescription),-strLen(codeDescription));
        currentLanguageId   = subStr(codeDescription,pos+1,strLen(codeDescription)-pos-1);
        
        if (!languagesToKeep.in(currentLanguageId))
        {
            warning(strFmt('Removing language %1',SysLicenseCodeReadFile::codeDescription(sysConfig.Id)));
            licenseCodeSet.add(sysConfig.Id);
            remLic.clear();
            remLic.LicenseCode = sysConfig.Id;
            remLic.Description = SysLicenseCodeReadFile::codeDescription(sysConfig.Id);
            remLic.insert();
        }
        else
        {
            info(strFmt('Keeping language %1',SysLicenseCodeReadFile::codeDescription(sysConfig.Id))); 
        }
    
    }
    
    if (licenseCodeSet.elements())
    {
        // if not valid code, then we should display the warning            
        confCount   = SysLicenseCodeReadFile::findConfigKeysFromLicenseCodeSet(licenseCodeSet);

        confirmForm = classfactory.formRunClass(args);
        confirmForm.init();
        confirmForm.run();        
        confirmForm.wait();  
        
        if (confirmForm.closedOk())
        {
            it = licenseCodeSet.getEnumerator();
            while (it.moveNext())
            {
                sysConfigId = it.current();
               
                update_recordSet sysConfig 
                    setting value = '' 
                    where sysConfig.id == sysConfigId;
                
            }
            
            SysLicenseCodeReadFile::codesModified();
        }
    }
}

Allow for a synchronization to run through after the licenses are modified. Remember that this may impact the database schema, but if you really do not want the (ie.) Norwegian language to be enabled, it should be safe to disable. Thanks for reading!

Saturday, March 7, 2015

ExchangeRateEffectiveView not returning cross rates to the Cubes in AX2012

I was asked to assist figuring out why exchange rates wouldn't always be retrieved to the Sales Order Cube in Dynamics AX2012. The solution became a journey through various interesting topics (for me at least):

  • Views in AX
  • View methods
  • Advanced Query range
  • Reverse engineering the SQL behind the views
Starting with the view that did not return all the expected data, we have the SalesOrderCube View. 
Now the Query in itself isn't all that fascinating, but I wasn't aware that you could add ranges across datasources like done in this Query. That is pretty handy!


Notice how the Query uses another View as DataSource. There are plenty of examples of Views and Queries being nested, and this is a powerful way to create results from a rather complex ERP data model. 
Notice also there is a custom Range on ValidFrom and ValidTo. The Ranges compare the Dates from ExchangeRateEffectiveView with the CreatedDateTime from SalesTable.



If we look at the definition of the ExchangeRateEffectiveView we see that ValidTo is a field of type Date. Furthermore we see the field is coming from a View Method. But we know CreatedDateTime on SalesTable is a DateTime.
How can it compare a Date with a DateTime? The answer is that actual date is stored in the database as a datetime where the time part is 00:00:00.000.   


So it compares the values and that works like charm. 

The problem

What happens if you have daily exchange rates in your system? Then your ValidFrom and ValidTo becomes the exact same date and more importantly the same time. This will not work correctly since CreatedDateTime also keeps track of what time on the day a Sales Order was created. 

So let's look at one specific example where we have rates for the 9th of December 2014.


And if we run the Sales Order Cube view, and modify the selected columns so we can see the problem, we will notice that the query is unable to collect the rates. The values from Exchange Rates is NULL.



The Solution

There are probably many ways to solve this, but the solution I went for was to make sure that the ValidTo always returns the time part at the max value, which is 23:59.59.000 (AX doesn't operate on the milliseconds, as far as I know).

So compare the results coming from the ExchangeRateEffectiveView before I apply the change.


By doing some small changes to the validTo View method, I can give the time part a better value.



And the result is that the Sales Order Cube now has the Cross Rates as expected. 


I hope you enjoyed this post. I sure enjoyed solving this challenge.
Here is the method body (ExchangeRateEffectiveView.validTo):
private static server str validTo(int _branchNum)
{
    str returnString, fieldString, fieldString2, fieldString3;
    boolean generateCode = false;
    DictView dictView;

    // Axdata.Skaue.04.03.2015 
    // Fix ValidTo with proper time 00:00:00.000 -> 23.59.59.000 ->
    str adFixValidToString = 'DateAdd(ss,-1,DateAdd(d,1,%1))';
    // Axdata.Skaue.04.03.2015 <-

    dictView = new DictView(tableNum(ExchangeRateEffectiveView));

    switch (_branchNum)
    {
        case 1:
            returnString = dictView.computedColumnString('VarToVarBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 2:
            fieldString = dictView.computedColumnString('DenToVarEuroBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToVarEuroBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString  = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2); 
            // Axdata.Skaue.04.03.2015 <-
            generateCode = true;
            break;

        case 3:
            fieldString = dictView.computedColumnString('DenToDenBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
            fieldString3 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate2', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2);
            fieldString3 = strFmt(adFixValidToString, fieldString3);
            // Axdata.Skaue.04.03.2015 <-
            returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 +
                ' and ' + fieldString + ' <= ' + fieldString3 + ' then ' + fieldString +
                ' when ' + fieldString2 + ' <= ' + fieldString3 + ' then ' + fieldString2 + ' - 1 ' +
                ' else ' + fieldString3 + ' - 1  end';
           break;

        case 4:
            returnString = dictView.computedColumnString('SameFromTo', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 5:
            returnString = '\'21541231\'';
            break;

        case 6:
            returnString = '\'21541231\'';
            break;

        case 7:
            returnString = dictView.computedColumnString('DenToVarAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 8:
            returnString = dictView.computedColumnString('DenToVarAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 9:
            returnString = dictView.computedColumnString('VarToDenAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 10:
            returnString = dictView.computedColumnString('VarToDenAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
            break;

        case 11:
            returnString = '\'21541231\'';
            break;

        case 12:
            fieldString = dictView.computedColumnString('DenToDenAfterStart1', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToDenAfterStart1', 'StartDate2', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString  = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2); 
            // Axdata.Skaue.04.03.2015 <-
            generateCode = true;
            break;

        case 13:
            fieldString = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'StartDate2', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString  = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2); 
            // Axdata.Skaue.04.03.2015 <-
            generateCode = true;
            break;

        case 14:
            fieldString = dictView.computedColumnString('DenToDenAfterStart2', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToDenAfterStart2', 'StartDate1', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString  = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2); 
            // Axdata.Skaue.04.03.2015 <-
            generateCode = true;
            break;

        case 15:
            fieldString = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
            fieldString2 = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'StartDate1', FieldNameGenerationMode::FieldList, true);
            // Axdata.Skaue.04.03.2015 ->
            fieldString  = strFmt(adFixValidToString, fieldString); 
            fieldString2 = strFmt(adFixValidToString, fieldString2); 
            // Axdata.Skaue.04.03.2015 <-
            generateCode = true;
            break;

    }

    if (generateCode)
    {
        returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 + ' then ' + fieldString +
            ' else ' + fieldString2 + ' - 1 end';
    }

    return returnString;
}