How to Kill Sessions in NAV when the user is Idle?

chandrurec

Hi all,

The scenario is as follows.

There is one user named 'SA' who has been idle for a long time lets say 2 hours.
So ,I want to trace all the users in the database who has been idle for 2 hours and automatically kill those sessions.

Is ther any Keyword we can use in order to achieve this.

If anybody know, kindly tell me the Coding I need to do in order to kill the session of the users who is idle for 2 hours or more and where I need to write this coding.

Thanks in advance.

Regards,
Chandru.

einsTeIn.NET

Did you try to search the forum?
http://www.mibuso.com/howtoinfo.asp?FileID=18
http://www.mibuso.com/forum/search.php?st=0&sk=t&sd=d&sr=topics&keywords=session+idle

chandrurec

Hi all,

I have read that we need to make use of the query to kill sessions in NAV

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_KillIdleSpids]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_KillIdleSpids]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_KillIdleSpids
@sec int=Null,
@NetSend bit=0,
@msg char(160)=Null -- For 'Net Send' command, message string is limited to 160 char. --
as

/**************************************************************
Name : sp_KillIdleSpids
Description : Kills connections that have been
inactive for @sec seconds.

Usage : exec sp_KillIdleSpids <sec>, [<0;1>, <Message>]

Author : Steve Jones - www.dkranch.net

Modifications : Benoît Ourssaire - www.soft-business.fr


Input Params :

---

@sec : int. defaults to 'Null', number of seconds for connection to be
idle to kill it.

@NetSend : int. defaults to '0', set to '1' for send message to computer (ie. user warning).

@msg : int. defaults to 'Null', message to be displayed by the 'Net Send' command (if @NetSend enable)
Note : accents are improperly displayed by 'Net Send' command.

Output Params :

---

Return : - 0, no error.
- Raises error if no parameters sent in.

Results :

---

Locals :

---

Modifications :

---

- Add a condition in 'where' clause to select only program name with *Navision* string.
- Add a 'Net Send' to warn users.
- Change 'select @cmd=...' by 'exec(...)'
- Add two variables to manage the 'Net Send' command.
- Add code to manage the 'Net Send' command.

**************************************************************/

declare @err int,
@spid int,
@cmd char(255),
@hostname char(255)


if @sec Is Null
begin
raiserror( 'Usage : exec sp_KillIdleSpids <sec>, [<0 or 1>, <Message>]', 12, 1)
return -1
end

If @NetSend=1 and @msg is Null
begin
raiserror( 'Usage : exec sp_KillIdleSpids <sec>, [<0 or 1>, <Message>]. Please fill Message field if using ''1''', 12, 1)
return -1
end



declare u_curs scroll insensitive cursor for
select s.spid, s.hostname
from master..sysprocesses s
where s.program_name LIKE '%Navision%'
and (datediff( ss, s.last_batch, getdate()) > @sec)
-- and s.loginame LIKE ''

open u_curs

fetch next from u_curs into @spid, @hostname

@fetch_status = 0
begin
set @cmd=convert(char(4), @spid)
exec('kill ' + @cmd)

if @NetSend=1 -- Execute 'Net Send' only if set to 1 --
begin
set @cmd='net send ' + ltrim(rtrim(@hostname)) + ' ' + @msg
exec master..xp_cmdshell @cmd, no_output
end

fetch next from u_curs into @spid, @hostname
end

deallocate U_curs
return
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

can anyone explain me what are the parameters in this query which i need to be change to kill sessions automatically in my system?

Thanks in advance.

Regards,
chandru.