Killing an Idle session in Native Navision Database

niimodin

Hello Experts,
Can someone help me on how to automatically log out an idle session in my navision database server.

Thank

kriki

This is one way : http://www.mibuso.com/howtoinfo.asp?FileID=18.
if you search the forum, probably there are others.

rocatis

Your code seems to basically delete records in the virtual Session table. This used to work (v4 and up) but it doesn't seem to work with v6.01. You can delete the session, but it is reestablished automatically.

An interesting side effect to this is that it's no longer possible to delete/disconnect sessions from File, Database, Information, Sessions (using F4). Well, you can delete it but it just pops up again without the deleted user being affected at all, prompting the question of why the delete functionality has not been removed. My guess is that it's supposed to work, Microsoft just forgot to test it.

I've created a support request with Microsoft.

Disclaimer: I've only looked at your code, I haven't tested it.

kriki

Well, I just tried it and it seems to work.
Maybe the record you just deleted gets back because of the buffers that are not always updated correctly.

You can easily check this by opening 2 sessions and with one session you delete the other. Then you go to the other session and do something that requires reading on the DB and you will get an error that you lost connection.

rocatis

kriki wrote:

Well, I just tried it and it seems to work.

What did you try? Your coded solution or manually deleting users with F4?

Because I created a support request for Microsoft regarding the F4 issue, and they are able to reproduce it...

You can easily check this by opening 2 sessions and with one session you delete the other. Then you go to the other session and do something that requires reading on the DB and you will get an error that you lost connection.

This is what I did. The "deleted" user continued to function flawlessly, and indeed he popped up in the session table again without me doing anything.

nilesh20

Hello All,

I have done this job when NAV 6.00 is released.

Deleting Session by codding is seems possible only when User is Database user. but for Windows user it does not work.

kriki

I tried both possibilities (manually and programmaticaly).
I didn't try it with the RTC though.

BTW: the user that deletes sessions probably needs to be "sysadmin" on the server.

rocatis

As I mentioned, I created a support request with Microsoft. As it turns out, the client will attempt to reconnect whenever the connection to the server (Native or SQL) is lost. They consider this to be a feature, not a bug.

As to why anybody would delete a user only to have it reappear automatically, this is supposedly to resolve a block or kill a time consuming process.

Bottom line: you can't delete/disconnect users from within NAV anymore.

GoMaD

Is there really no way around this?

Is the only real solution to restart the native service?

Savatage

how about a 3rd party app like http://www.autocloseidle.com which closed the session from the client side. It also has remote control from the administrators pc to close down a specific pc as well as a timer.

has a 14day free trial

sfriedman

I thought I would weight in, as we have struggled with--and resolved--automating the disconnection of idle users.

I must mention that our solution requires you to use some programming environment that allows you to interact directly with SQL. I realize that this will make my solution completely worthless to some. I am writing because I have seen stored-procedure solutions that I find confusing because of my lack of knowledge in the stored-procedure realm. My goal is to give those who may be profecient in other programming languages the ability to implement an auto-kick solution:

First, our setup:
NAV 6.0
SQL 2005
A scripting or programming language of your choosing
A SQL user with the ability to KILL connections, and the ability to SELECT in the Navision DB

1) Use a SQL command to find idle sessions, and then kill them with the KILL command and the Connection ID.

For instance, if you wanted to automatically remove users who were idle longer than 20 minutes, you could create a .NET Console application or a PHP CLI script to do the following:

a. Find idle sessions

SELECT * from [your_sql_database_name].[dbo].[Session]
WHERE [Idle Time] > 1200000 -- 20 minutes = 1200000 milliseconds
AND [Database Name] = 'your_sql_database_name'
AND [Application Name] = 'Microsoft Dynamics NAV Classic client';

b. Iterate through your sessions results and kill the connections with:

KILL [connection id]

c. Set a scheduled task on any computer with your access to your SQL server to perform this operation at an interval of your choosing.

2) @rocatis: Yes, sessions are re-established automatically. When you 'kick' a user by deleting their database session, the user does not receive any error or notification. In our situation, this is ideal. We constantly kick users idle longer than 20 minutes. When a user returns to his/her desk, the NAV client reconnects. He/she begins using NAV again seamlessly--no hiccups/dialogs/warnings.

3) The "Sessions" table in Navision is not a real table. It is a "View" in SQL. It is a dynamically-established table. Flow Filters work in a similar fashion-- they are not actual SQL fields; rather, they are "Views" in SQL.

4) Users in the "Sessions" view are drawn from another "view": In the MASTER db, there is a view called "sysprocesses". You could take a peak at this by using the following SQL:

SELECT *
FROM [master].[dbo].[sysprocesses]

I think that's all I know.

If anyone is familiar with PHP or ASP.NET console programming, I would be glad to discuss how you might implement such a solution using your existing programming knowledge.

Cheers!
Sam

ssingla

I will give it a try using SQL stored procedure and the scheduling it. Will it work?

SonGoten13

hi all

i am using the solution from this thread http://www.mibuso.com/dlinfo.asp?FileID=367 to delete sessions via T-SQL.
It works often, but also several times the User-Session stays open. Not always like it is said here but often. The Session disappears from the table till the User makes a move again.
Same with the F4 in the NAV-Client Session Table.
Sucks when the User is longer idle, because you think you disconnected him, but in fact he still blocks a licence and you don't see him anymore ...
Someone knows why this happens?

i am using SQL Server 2008, NAV 2009 R2 Classic Client, Windows Logins

Similar discussion here: viewtopic.php?f=23&t=47553&hilit=kill+session+does+not+work