SQL: Microsoft Dynamics NAV "kill" an idle session

One issue with applications that use a concurrent license model is the dreaded “idle user”. This is the user that had gone to lunch, to a meeting or even home for the and didn’t “logout” of the application. Under the concurrent license model this user does take up a user session, which prevents other users from accessing the application if the maximum number of seats has been reached or lead to the purchase of additional and unnecessary user licenses due to the perception of not enough seats.

Microsoft Dynamics NAV is an example of application that uses a concurrent license model and is also one application that people often ask “How can I disconnect an idle user?” While there isn’t a magic “kill idle” user setting there are a several options available. A few of these options are:

  • If the users are using Remote Desktop you can set an idle timeout to force a disconnect or manually force a disconnect of idle remote users
  • Manually remove idle users from the Microsoft Dynamics NAV session list
  • Kill a user’s SQL Server connection

Let’s jump into the last option that is listed above. In Microsoft SQL server there isn’t an “idle time” value defined for a user, however there is a last_batch value that indicates the last time the users session sent an execution to the SQL server. With that information we can easily create (and schedule) a script that can compare the current date and time to the last_batch value to see how long a user has been “idle” and kill their session if it is over a certain amount of time (script should be adjusted for your implementation):

set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @spid as varchar(20)
declare @loginname as varchar(255)
declare @kill as int
declare @cmd as varchar(max)

-- set the variable @kill to 1 to actually perform the “kill or set it to 0 to see a list of users that
-- would be killed
set @kill = 0

declare users insensitive scroll cursor
for    
      select
            spid
            ,loginame
      from
            sys.sysprocesses
      where
            -- NAV Db Name    
            (db_name(dbid) = N'CRONUS')
            and (program_name = N'Microsoft Dynamics NAV Classic client')
            -- number of seconds idle -- remember this does not mean there isn't a long job running
            -- 3600 is 1 hour
            and (datediff(ss, last_batch, getdate()) > 3600)


open users
while (1=1)
      begin
            fetch next from users into @spid, @loginname;
            if @@fetch_status <> 0 break;
            
            set @cmd = 'kill ' + @spid
            if @kill = 1 
              exec (@cmd)
            else
              print @cmd + N' ' + @loginname;
      end

close users;
deallocate users;

go

 

One thing to point out is that a user may be idle because their session is performing a lengthy operation and they have not had any session activity while they are awaiting results for their command. There tends to be some disconnect and excitement when someone is in the middle of some operation and their session is killed.

It should be evident that there is a risk when abnormally terminating a user’s session. There is the risk of data loss or corruption so be certain to understand the environment that you’re in and the implications of terminating a user’s session in a not so elegant way (i.e. properly logging off).

Microsoft Dynamics NAV: Activity Button Images (RTC)

When adding a new Activity Button to a page in the Role Tailored client (RTC) the default image is basic and a bit boring. The image property of the action is used to specify the image that is displayed for the action. Where is the list of images? Microsoft was kind enough to place a list of the images on MSDN (http://msdn.microsoft.com/en-us/library/dd568709.aspx).

Activity Button Image Library

Microsoft Dynamics NAV: Excel Automation AddPicture

The ability to use Automation Objects has added to the power and flexibility of Microsoft Dynamics NAV.  Automation can be used to integrate NAV business logic with external processes and extend the functionality of the product, such Microsoft Office.  This is easily demonstrated through the use of Table 370, Excel Buffer.  Using the “Excel Buffer” table you can easily create Excel Workbooks with Dynamics NAV data.

One thing that is not included in the “Excel Buffer” table, which could be useful, is the option to insert a picture into a worksheet.  Fortunately, the Excel Automation control exposes the Shapes.AddPicture method than can be easily extended into the Dynamics NAV “Excel Buffer” table by creating a function for XlWrkSht.Shapes.AddPicture(filename,1,1,Left,Top,Width,Height);

 

This method can be implemented several ways and it is easy enough to add a field ImageName to the table.  The CreateSheet code can then be altered to add an image to the current sheet based on the value entered in the field. 

 

Microsoft Dynamics NAV: Send XML Document via HTTP Post

I had previously discussed exporting an XMLPort to a file, web service and as BigText.  There may also be a need to exchange XML data via HTTP Post.  I will build on the XMLPort to BigText example.

 
CLEAR(TempBlob);
TempBlob.Blob.CREATEOUTSTREAM(OS);
Customer.GET('30000');

//Set the view for the XMLPort
 CustomerXML.SETTABLEVIEW(Customer);
 
// Use the TempBlob record to get a blob stream
TempBlob.Blob.CREATEOUTSTREAM(OS);
CustomerXML.SETDESTINATION(OS);
// export the contents of the XMLPort to a blob
CustomerXML.EXPORT;
 
TempBlob.CALCFIELDS(Blob);
TempBlob.Blob.CREATEINSTREAM(IS);


IF ISCLEAR(XMLHTTP) THEN //'Microsoft XML, v6.0'.XMLHTTP60
  CREATE(XMLHTTP);
IF ISCLEAR(XMLDoc) THEN //'Microsoft XML, v6.0'.DOMDocument
  CREATE(XMLDoc);

XMLDoc.load(IS);

XMLHTTP.open('POST', WebAddress, FALSE);
XMLHTTP.setRequestHeader('Content-Type', 'text/xml');
XMLHTTP.send(XMLDoc);

IF XMLHTTP.status = 200 THEN BEGIN
  IF (XMLHTTP.responseText <> '') THEN BEGIN

  END;
END ELSE BEGIN
  ERROR('Status %1 %2',XMLHTTP.status,XMLHTTP.statusText);
END;

CLEAR(XMLDoc);
CLEAR(XMLHTTP);

 

The variables are declared as:

 

Microsoft Dynamics Nav: XMLPort through BigText

With the addition of Web Services, exchanging data and processing code with Microsoft Dynamics NAV from an external source became a relatively simple task.  By exposing an XMLPort, working with class object and binding to the underlying data allows for an easy way to exchange data with Microsoft Dynamics NAV.  If the requirement is to pass the text (string) output of the XMLPort as text you can pass it through as a BigText variable. 

// Get Recordset
Customer.GET('30000');

//Set the view for the XMLPort
CustomerXML.SETTABLEVIEW(Customer);

// Use the TempBlob record to get a blob stream
TempBlob.Blob.CREATEOUTSTREAM(OS);
CustomerXML.SETDESTINATION(OS);
// export the contents of the XMLPort to a blob
CustomerXML.EXPORT;

TempBlob.CALCFIELDS(Blob);
TempBlob.Blob.CREATEINSTREAM(IS);
// Read the blob contents to the big text
Response.READ(IS);

 

Screenshot of XMLPort to BigText Microsoft Dynamics NAV

The variables are declared as follows:

Microsoft Dynamics NAV variables for sample code

Reading Stuff

Information in this document subject to change without notice.
All Software source code published is for demonstration and knowledge sharing purposes only. The Code is supplied "as is" without warranty as to result, performance or merchantability. Use at your own risk.
The opinions expressed herein are the opinions of the author and do not reflect those of any other entity.