Monday, October 10, 2016

Using PowerShell to list the 10 most recent KBs installed in AX2012 R3

I was asked to get an overview of the most recent KBs installed on an AX2012 R3 environment.
One way to do this is using PowerShell.
We know each KB is deployed as a single model, and we know each model installed will have a unique ID. This ID is incremented automatically by the system, so why not just sort on it, in a descending fashion.

Look at the following PowerShell command:

axmodel | 
where {$_.Name -match 'KB'} | 
sort modelid -Descending | 
select modelid, displayname, version, description, details -first 10 | 

I assume the default environment picked up by the command "axmodel" (alias for Get-AXModel) is the one we want to query. You can put in a "-Verbose" after the command if you'd like to see some verbose information about what ModelStore database the command operates on.

I then add a filter on the list, making sure I only get models having a match on "KB" in the Name property. I could also have looked for the word "hotfix" in the Description property. The string is not case sensitive.

I then pipe the result to a sorting where I want the list sorted descending on the ModelId.

I select out only some of the columns, and I also pick out the first 10 of the result.

Finally I send the result to the GridView Window, just because I like to see the result in a nice window (which allow for resizing, column resizing, filtering, etc).

Notice also that I can have this entire command on multiple lines, and the trick is that PowerShell will allow this when you use Pipe (|) like I am doing above. Otherwise, a line-break is interpreted as execution of a command, so be aware of that when running a "multi-line" PowerShell command.

You can also use the example above to do other things, like looking for specific KBs and check if they are already installed.


Friday, October 7, 2016

Kicking off a dedicated podcast with my friend, colleague and fellow MVP Fredrik

It is with great excitement I am happy to announce the Dynamics AX Podcast!

It is a common effort with MVP Fredrik Sætre to generate another channel where all you Dynamics AX geeks, both techies and funkies (yea, functional consultants) can tap into thoughts, ideas, tips, insights, fun AX stories, and the list goes on and on.

Bare in mind, we invite anyone who is willing to share their insights, and while we don't necessary have to agree on anything, the primary aim is to share thoughts and have fun!

I really hope you enjoy it, and please feel free to comment, either here on this blog, or on the Youtube channel videos. Feel free to Tweet questions and topics you'd like to be discussed.

The first Podcast is available here:

Thursday, July 7, 2016

Stored Procedure for listing who is running AX queries on the SQL Server

I want to share a small nugget I've created for listing who is currently running queries against AX at any point in time. It builds on two prerequisites, one of which is from one of my suggested answers on the Dynamics AX Community Forum and secondly a solution provided by Microsoft in their own blog.

For the sake of completeness I will include the "sp_lock3" stored procedure in this post, but it is only fair to include that I got it from the LearnSQLForum, posted by a Scott Wigham here:

The sp_lock3 looks like this:


USE master
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
    DROP PROC dbo.sp_lock3
CREATE PROC dbo.sp_lock3 (
    @spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */ 
    , @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */ 

CREATE TABLE #locktable (
    spid SMALLINT
    , loginname NVARCHAR(128) 
    , hostname NVARCHAR(128)
    , dbid INT
    , dbname NVARCHAR(128) 
    , objId INT
    , ObjName  NVARCHAR(128)
    , IndId INT
    , Type NVARCHAR(4)
    , Resource NVARCHAR(16)
    , Mode NVARCHAR(8)
    , Status NVARCHAR(5)


IF @spid2 IS NOT NULL AND @spid1 IS NULL
    SET @spid1 = @spid2

DECLARE @object_id INT,
  @dbid INT,
  @DynamicSql NVARCHAR(255)

/***** @spid1 is  provided so show only the locks for @spid1 and @spid2 *****/ 
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (,  1,  8)
        , SUBSTRING (,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS'      AND  u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)

ELSE /***** @spid1 is not provided so show all the locks *****/ 
    INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
    SELECT CONVERT (SMALLINT, l.req_spid) 
        , COALESCE(SUBSTRING (s.loginame,  1,  128), '')
        , COALESCE(SUBSTRING (s.hostname,  1,  128), '')
        , l.rsc_dbid
        , SUBSTRING (DB_NAME(l.rsc_dbid),  1,  128)
        , l.rsc_objid
        , ''
        , l.rsc_indid
        , SUBSTRING (,  1,  4)
        , SUBSTRING (l.rsc_text,  1,  16)
        , SUBSTRING (,  1,  8)
        , SUBSTRING (,  1,  5)
    FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
        ON l.rsc_type = v.number
    JOIN master.dbo.spt_values x
        ON l.req_status = x.number
    JOIN master.dbo.spt_values u
        ON l.req_mode + 1 = u.number
    JOIN master.dbo.sysprocesses s
        ON l.req_spid = s.spid
    WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)

    Because the locks exist in any database, you must USE  before running OBJECT_NAME 

    We use a dynamic SQL loop to loop through each row from #locktable

    A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

        SELECT @DynamicSql = 
         'USE ' + DB_NAME(@dbid) + char(13) 
         + 'UPDATE #locktable SET ObjName = OBJECT_NAME(' 
            + CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId) 
         + ' AND objid = ' + CONVERT(VARCHAR, @object_id) 

        EXEC sp_executesql @DynamicSql

        SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
        SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''

SELECT * FROM #locktable 
    WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table 
        AND objid > 100 -- do not return system table locks
        AND objname <> 'spt_values'

If you first install this procedure, simply by running it on the SQL Server instance, it will be globally available on that instance.

Next step is to run the script below to install my "sp_whoInAx":

sp_whoInAx    This script lists out current users running queries against 
      any Dynamics AX database on this SQL Server Engine instance.
      Please report any issues and improvements back to the author.
Witten By:    Tommy Skaue (email: add the @-sign between first and last name, and end with .com)
      Microsoft Dynamics AX MVP
Version:    1.0
Comments:    This procedure requires sp_lock3 in order to work.
This script is presented "AS IS" and has no warranties expressed or implied!!!

USE [master]
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_whoInAx'), 'IsProcedure')) = 1
    DROP PROC [dbo].[sp_whoInAx] 

CREATE PROC [dbo].[sp_whoInAx] 


 INSERT INTO #locktable
 EXEC sp_lock3

 CREATE TABLE #CurrentAxSessions (
  CI VARCHAR(128) 
  , LOGIN_TIME datetime
  , STATUS VARCHAR(30)    

 INSERT INTO #CurrentAxSessions
 WHERE 1=1
 FROM #CurrentAxSessions AX


It takes the result of sp_lock and combines it with the "AX sessions". Again, it does require that registry change mentioned in Microsofts blog, in addition to a restart of the AOS service.

To reiterate the steps for including the user in the session context, they are:

  1. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Dynamics Server\6.0\01\Original (installed configuration). The last key, Original (installed configuration), is the key name for the current server configuration. If your system uses a different configuration that the original installed configuration, navigate to the currently active configuration.
  2. Create a string registry value called ‘connectioncontext’ and set the value to 1.
  3. Restart the AOS.
Now you can run sp_whoInAx from any query window on that SQL Server instance, and see from the context the user id and also see what database and tables are involved. 

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: