To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.

This can be done by looping through the Session Virtual Table and check the Idle Time field. I like to add the field “Maximum Idle Time” to the User Setup table and compare that field to the Idle Time in the Session table before deciding to kill the session.

The use running this batch must have permission to kill sessions on the SQL Server.
[code htmlscript=”false”]IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
‘Driver={SQL Server Native Client 10.0};’ +
‘Server=<ServerName>;’ +
‘Database=<DatabaseName>;’ +
‘Trusted_Connection=yes;’);
ADOConnection.CommandTimeout(0);

Session – OnAfterGetRecord()
IF "Application Name" <>
‘Microsoft Dynamics NAV Classic client’
THEN
CurrReport.SKIP;
BackSlashPos := STRPOS("User ID",’\’);
IF NOT
UserSetup.GET(
UPPERCASE(COPYSTR("User ID",BackSlashPos + 1)))
THEN
CurrReport.SKIP;
IF FORMAT(UserSetup."Maximum Idle Time") = ” THEN
CurrReport.SKIP;
Idle := "Idle Time";
MaxIdle := (UserSetup."Maximum Idle Time" – 000000T);
IF Idle < MaxIdle THEN
CurrReport.SKIP;

ADOConnection.Execute(STRSUBSTNO(‘KILL %1’,"Connection ID"));

Session – OnPostDataItem()
ADOConnection.Close;
CLEAR(ADOConnection);[/code]

Name DataType Subtype Length
UserSetup Record User Setup
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
MaxIdle Decimal
Idle Decimal
BackSlashPos Integer

This can be added as a Job Queue Batch.

See here to check for required permission to use the kill statement.

27 thoughts on “Kill Idle Connections

  1. Dynamics NAV says:

    Gunnar,

    I am getting error message:

    —————————
    Microsoft Dynamics NAV Classic
    —————————
    This message is for C/AL programmers:

    The call to member Open failed.
    Microsoft OLE DB Provider for ODBC
    Drivers returned the following message:

    [Microsoft][SQL Server Native Client 10.0]
    Named Pipes Provider: Could not open a
    connection to SQL Server [53].

    —————————
    OK
    —————————

    Any suggestions what could be wrong?

  2. Dynamics NAV says:

    Ahh… mistyped the servername 😉

    By the way, this way of killing a session is really cool. User is not getting any error message (at least, when I tested this, I didn’t). And then, if user clicks anywhere on the MenuSuite, he connects back to the system automatically. Cool! 🙂

  3. Janaka Jayasekara says:

    Hi Gunnar,

    Thank You very much it’s perfect fit.

    Janaka.

  4. Divyashree says:

    Hi,

    I am new to navision. Kindly help me with this.. Im not clear.. How will the “Maximum Idle Time” field work and where should I show this code??

    1. Hi

      the “Maximum Idle Time” is the limit. When the session idle time reaches this limit it is killed. I am running this code in the job queue as a automated NAS task.

  5. Divyashree says:

    Hi Gunnar,

    I am gettin the following error if I run the report. Can u pls help me…….

    Could not create an instance of ole control or automation server identified by guid={2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection.

    1. You might need another version of the Microsoft ActiveX Data Object Library. Go into your globals and lookup the automations that are available on your computer.

  6. tester says:

    session get killed but user can continue using navision not needing to login again…was this happning in your case or am I missing anything

  7. Yes that is intended. The client reconnects if there is available license to do that. However, if you are having problems with idle clients and the concurrent user licenses then this would help you.

    1. McWolf says:

      can you please explain how to do Job Queue Batch.for this program

  8. Ktast says:

    Hi Gunnar and thank you for sharing your posts,

    Could you please tell me if this works on Dynamics NAV 2016 CU8 ?

  9. Eymundur Ingim says:

    Sæll Gunnar, ég er að reyna að gera keyrslu eftir þessu í Nav 2013 R2 en hef ekki reitinn “Idle Time” í Session töflunni? Er þetta ekki standard reitur?

    1. Ekki í 2013 og nýrri útgáfum

  10. Mihai Marian says:

    Hi,

    What is the code for Nav 2016? In Nav 2016 Session table is missing “Idle Time” field…

    Thanks, Mihai M.

    1. Hi Mihai

      There is a STOPSESSION command in C/AL and there are PowerShell options as well

  11. Mihai Marian says:

    Hi,

    Thanks for the answer. I already used STOPSESSION, but couldn’t find any PowerShell that could show Idle connections. The problem that I’m facing is: How to kill Idle for more than 4 minutes Nav 2016 sessions with exceptions: for some users should not kill sessions even if they have been idle for more than 4 minutes.

    I found 3 possible solutions:
    • Solution 1: Setup Idle Client Timeout/Keep Alive Interval/Reconnect Period on Nav Service. Still this solution is missing exceptions for some users.
    • Solution 2: Use Session Virtual table in Nav to view and kill sessions. Still this virtual table is missing “Idle Time” that was to be found on Nav 2009 R2. Is there a way, to change a virtual table, to extend this Session table in Nav 2016 to be same as in Nav 2009 R2?
    • Solution 3: Use SQL script that worked on Nav 5.0 SQL 2008 R2, that used sys.sysprocesses table to check for idle connections. This script is not showing in SQL 2014 and Nav 2016 the Role Tailored Connections, that we need to kill. Is there a different table to use?

    Thanks in Advance, Mihai M

    1. Hi Mihai

      I don’t think that there is a good solution. Perhaps the closest you could get is to build your own Idle table and watch Events to update the last action time.

  12. abeywicrema says:

    Hi
    If I use NAS service and Idle Time simultaneously in same service will it get any problem ? if the NAS service get idle , will it get disconnected ?

    Regards
    Shan

    1. My Code is using a boolean field in User Setup to make sure to ignore some users, including the NAS user(s).

Leave a Reply

%d bloggers like this: