Blog

Male reading an ArcherPoint blog on computer screen at a home setting
February 26, 2014

Microsoft Dynamics NAV Deadlocks Version 2.0

Microsoft Dynamics NAV Deadlocks Version 2.0
Submitted by Kyle Hardin
Reading time: 9 mins

“The men I killed needed killin’.”

Clint Eastwood

OK, Clint never actually had that line in a movie. But if anyone should have said that line, it was Clint. Western Clint, not San Francisco cop Clint. Inspector Callahan would have never bothered to take the time to explain why he had killed someone; he would describe how he was about to kill you (did he fire six shots or only five? Now, to tell you the truth, I forgot myself in all this excitement), but never a post-killing interview. But I digress before I even get started.

This blog post is a continuation of a previous post about dealing with deadlocks in NAV. That post presented two possible solutions of how to locate and terminate the offending session that was causing the deadlock. One solution was to view the Database Sessions in the classic client, and then delete the session. The other was to use SQL Server and use TSQL queries to locate and terminate.

There was an excellent blog reply that basically asked, “What if I’m using RTC, and I don’t want to dirty myself with SQL queries?”

Speaking of – I welcome comments and questions. I feel that helps us all learn, grow, and become better NAV bloggers and blog consumers. We all learn that way!

Anyway, when I started this little evil scientist project, I thought it would be easy. I can use .NET to make a SQL connection and use the KILL command to terminate without discretion. However, it turns out it is a lot more complicated than that because of the different versions of NAV, but I pressed on, mostly as an experiment to see what can be done with SQL integration.

2009R2 worked perfectly – in fact, it will let you delete a Session directly without using SQL at all. All I had to do was create a new Page layered on top of the hidden Session table (2000000009), and the normal NAV Delete did everything else.

Then I imported this Page into 2013, and that’s when the wheels came off. The Session table is different (they removed all of the statistical fields), and you can’t delete. On top of that, the Session ID no longer matched the SQL Process ID (SPID) on the SQL Server. The existing NAV Sessions Pages under Administration in 2013 and 2013R2 don’t allow Delete at all (or rather, the table itself blocks deletions).

I ended up having to do everything as SQL queries, including populating my own custom Session table.

Please note: This requires PROCESSADMIN or SYSADMIN server roles on the SQL server. Not a database role, but the SQL server role, since this connects to the Master database to do the killin’. This security cannot be done from NAV (SUPER is not good enough), so you’ll have to work with your DBA to get the security set correctly.

I was able to use the Upgrade Toolkit as a base for the SQL .NET code – if you’d like to see a more complicated bit of SQL code, look at codeunits 104049 and 104050. These two move old-school dimensions into the new Dimension Sets.

My new Session table is very straightforward and very similar to the hidden Session table, although I combined the login date and time fields into a DateTime. I was also able to populate Blocked, which was taken away in 2013. I cannot, however, tell which session is My Session. Since we are making a separate .NET connection to SQL outside of the normal NAV connection, it has no way of knowing which SPID belongs to the RTC connection. However, it doesn’t really matter – because of the separate connection to SQL, now it is possible to kill your own session (I don’t know why you would want to do that, but I’m not here to judge).

This solution also has another large limitation: I can’t tell who the actual user is. The actual connection to SQL from the RTC is done by the service tier, so what we see is the Service Tier User ID, not the end (impersonated) User ID. However, the Blocked flag will still let you know which session to kill.

Screenshot of the connection to SQL Server that needed killin’

Figure 1 – Screenshot of the connection to SQL Server that needed killin’

Let’s go through the code. Nothing fun happens in the table; in fact, I use it as a temporary table in the Page. Seven fields, one key. All of the good stuff is in the Page.

In OnOpenPage, we establish the connection to SQL.

OnOpenPage()

Session2.SETRANGE("My Session", TRUE);

Session2.FINDFIRST;

ConnectionString := STRSUBSTNO(Text001, Rec."Host Name", 'master');

SQLConnection := SQLConnection.SqlConnection(ConnectionString);

SQLConnection.Open();

QueryString := STRSUBSTNO(Text002, Session2."Database Name");

SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

SQLCommand.CommandTimeout(0);


SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLCommand);

SystemDataTable := SystemDataTable.DataTable;

SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);

SQLDataAdapter.Fill(SystemDataTable);

SystemDataRows := SystemDataTable.Rows;

SystemDataRow := SystemDataRows.Item(0);

TempVariant := SystemDataRow.Item(0);

DatabaseID := TempVariant;


PopulateSessions();

This code uses the hidden Session table to figure out the database server name and uses that to create a connection string using Text001:

Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI

Once the connection is open, we query what the current database name is using Text002:

select db_id('%1')

Then we call PopulateSessions, which fills our temporary table with SQL Session records:

QueryString := STRSUBSTNO(Text003, DatabaseID);

SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

SQLCommand.CommandTimeout(0);


SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLCommand);

SystemDataTable := SystemDataTable.DataTable;

SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);

SQLDataAdapter.Fill(SystemDataTable);

SystemDataRows := SystemDataTable.Rows;


WHILE RowCount < SystemDataRows.Count DO BEGIN

 SystemDataRow := SystemDataRows.Item(RowCount);


 CLEAR(Rec);

 TempVariant := SystemDataRow.Item('spid');

 "Connection ID" := TempVariant;

 TempVariant := SystemDataRow.Item('loginame');

 "User ID" := TempVariant;

 TempVariant := SystemDataRow.Item('login_time');

 "Login DateTime" := TempVariant;

 TempVariant := SystemDataRow.Item('database');

 "Database Name" := TempVariant;

 TempVariant := SystemDataRow.Item('program_name');

 "Application Name" := TempVariant;

 TempVariant := SystemDataRow.Item('hostname');

 "Host Name" := TempVariant;

 TempVariant := SystemDataRow.Item('blocked');

 INSERT(FALSE);

 RowCount += 1;

END;

Text003 is this:

select spid, rtrim(loginame) as loginame, login_time, db_name(dbid) as 'database', rtrim(program_name) as program_name, rtrim(hostname) as hostname, blocked from sys.sysprocesses where dbid = %1

Note that I had to use a variant to force the type conversion so that the strings, integers, and datetimes worked correctly.

And last we have the OnDelete trigger:

OnDeleteRecord() : Boolean

QueryString := STRSUBSTNO(Text004, Rec."Connection ID");

SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

SQLCommand.CommandTimeout(0);

SQLCommand.ExecuteScalar();


PopulateSessions();

CurrPage.UPDATE(FALSE);

Text004 is this:

kill %1

I tested with 2009 R2 build 32012, 2013 build 35201 and 2013 R2 build 36065. A full text export of both objects is included below.

As of NAV 2013, there is a new C/AL function called STOPSESSION. If you don’t care about 2009 R2, you can modify the existing Page 9506 per this Blog article: http://www.dynamicsblog.at/index.php/2012/10/18/nav2013-kill-session-in-session-manageme-1

If you’d like a FOB export of the objects, email me at kyle.hardin@archerpoint.com.

If you are going to Convergence, stop by the ArcherPoint booth (#634).

Full text export:

OBJECT Table 99999 He Needed Killin' Session

{

 OBJECT-PROPERTIES

 {

  Date=02/21/14;

  Time=08:36:27;

  Modified=Yes;

  Version List=;

 }

 PROPERTIES

 {

  DataPerCompany=No;

 }

 FIELDS

 {

  { 1  ;  ;Connection ID    ;Integer    }

  { 2  ;  ;User ID       ;Text128    }

  { 4  ;  ;Login DateTime   ;DateTime    }

  { 6  ;  ;Database Name    ;Text128    }

  { 7  ;  ;Application Name  ;Text128    }

  { 8  ;  ;Host Name      ;Text128    }

  { 12 ;  ;Blocked       ;Boolean    }

 }

 KEYS

 {

  {  ;Connection ID              ;Clustered=Yes }

 }

 FIELDGROUPS

 {

 }

 CODE

 {


  BEGIN

  END.

 }

}


OBJECT Page 99999 He Needed Killin'

{

 OBJECT-PROPERTIES

 {

  Date=02/21/14;

  Time=10:17:45;

  Modified=Yes;

  Version List=;

 }

 PROPERTIES

 {

  InsertAllowed=No;

  ModifyAllowed=No;

  SourceTable=Table99999;

  PageType=List;

  SourceTableTemporary=Yes;

  OnOpenPage=BEGIN

         Session2.SETRANGE("My Session", TRUE);

         Session2.FINDFIRST;

         ConnectionString := STRSUBSTNO(Text001, Rec."Host Name", 'master');

         SQLConnection := SQLConnection.SqlConnection(ConnectionString);

         SQLConnection.Open();

         QueryString := STRSUBSTNO(Text002, Session2."Database Name");

         SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

         SQLCommand.CommandTimeout(0);


         SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLCommand);

         SystemDataTable := SystemDataTable.DataTable;

         SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);

         SQLDataAdapter.Fill(SystemDataTable);

         SystemDataRows := SystemDataTable.Rows;

         SystemDataRow := SystemDataRows.Item(0);

         TempVariant := SystemDataRow.Item(0);

         DatabaseID := TempVariant;


         PopulateSessions();

        END;


  OnClosePage=BEGIN

         SQLCommand.Dispose();

         SQLConnection.Close();

         SQLConnection.Dispose();

         CLEAR(SQLCommand);

         CLEAR(SQLConnection);

        END;


  OnDeleteRecord=BEGIN

           QueryString := STRSUBSTNO(Text004, Rec."Connection ID");

           SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

           SQLCommand.CommandTimeout(0);

           SQLCommand.ExecuteScalar();


           PopulateSessions();

           CurrPage.UPDATE(FALSE);

          END;


 }

 CONTROLS

 {

  { 1240060000;0;Container;

        ContainerType=ContentArea }


  { 1240060001;1;Group ;

        Name=Group;

        GroupType=Repeater }


  { 1240060002;2;Field ;

        SourceExpr="Connection ID";

        Editable=False }


  { 1240060003;2;Field ;

        SourceExpr="User ID";

        Editable=False }


  { 1240060005;2;Field ;

        SourceExpr="Login DateTime";

        Editable=False }


  { 1240060007;2;Field ;

        SourceExpr="Database Name";

        Editable=False }


  { 1240060008;2;Field ;

        SourceExpr="Application Name";

        Editable=False }


  { 1240060010;2;Field ;

        SourceExpr="Host Name";

        Editable=False }


  { 1240060004;2;Field ;

        SourceExpr=Blocked }


 }

 CODE

 {

  VAR

   Session2@1240060000 : Record 2000000009;

   SQLConnection@1240060002 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection";

   SQLCommand@1240060001 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand";

   Text001@1240060003 : TextConst 'ENU="Data Source=%1;Initial Catalog=%2;Integrated Security=SSPI"';

   SQLDataAdapter@1240060011 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataAdapter";

   SQLCommandBuilder@1240060010 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommandBuilder";

   SystemDataTable@1240060009 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataTable";

   SystemDataRows@1240060008 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRowCollection";

   SystemDataRow@1240060007 : .NET "'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.DataRow";

   ConnectionString@1240060004 : Text[1024];

   QueryString@1240060005 : Text[1024];

   Text002@1240060012 : TextConst 'ENU=select db_id(''%1'')';

   Text003@1240060013 : TextConst 'ENU="select spid, rtrim(loginame) as loginame, login_time, db_name(dbid) as ''database'', rtrim(program_name) as program_name, rtrim(hostname) as hostname, blocked from sys.sysprocesses where dbid = %1"';

   DatabaseName@1240060014 : Text[128];

   TempVariant@1240060015 : Variant;

   DatabaseID@1240060016 : Integer;

   RowCount@1240060017 : Integer;

   Text004@1240060006 : TextConst 'ENU=kill %1';


  PROCEDURE PopulateSessions@1240060000();

  BEGIN

   QueryString := STRSUBSTNO(Text003, DatabaseID);

   SQLCommand := SQLCommand.SqlCommand(QueryString, SQLConnection);

   SQLCommand.CommandTimeout(0);


   SQLDataAdapter := SQLDataAdapter.SqlDataAdapter(SQLCommand);

   SystemDataTable := SystemDataTable.DataTable;

   SQLCommandBuilder := SQLCommandBuilder.SqlCommandBuilder(SQLDataAdapter);

   SQLDataAdapter.Fill(SystemDataTable);

   SystemDataRows := SystemDataTable.Rows;


   WHILE RowCount < SystemDataRows.Count DO BEGIN

    SystemDataRow := SystemDataRows.Item(RowCount);


    CLEAR(Rec);

    TempVariant := SystemDataRow.Item('spid');

    "Connection ID" := TempVariant;

    TempVariant := SystemDataRow.Item('loginame');

    "User ID" := TempVariant;

    TempVariant := SystemDataRow.Item('login_time');

    "Login DateTime" := TempVariant;

    TempVariant := SystemDataRow.Item('database');

    "Database Name" := TempVariant;

    TempVariant := SystemDataRow.Item('program_name');

    "Application Name" := TempVariant;

    TempVariant := SystemDataRow.Item('hostname');

    "Host Name" := TempVariant;

    TempVariant := SystemDataRow.Item('blocked');

    INSERT(FALSE);

    RowCount += 1;

   END;

  END;


  BEGIN

  {

   AP0001 KSH 02/13/14: Sample Page for deleting NAV sessions by making a .NET connection to SQL

              and killing it there

  }

  END.

 }

}

Trending Posts

Stay Informed

Choose Your Preferences
First Name
*required
Last Name
*required
Email
*required
Subscription Options
Your Privacy is Guaranteed