Kill Session not working

sanjeevasawale

Hi all,

Have made 1 report to kill ideal sessions for Nav 2009 Sp1 SQL but though Report killed session of 'x' login same was actually active on another session.

So i tried deleting session manually by 'sa' login. From my computer session got deleted but after 2 min when i open form on deleted session it actually opened without any error. Similarly same session started showing in 'sa' login. Client has 10 user license so I even logged in for all users but still it did not deleted the session.

If you have any suggestion or solution to this problem please let me know.

Rikt-It

Hallo.

I have found and modifyed this with a SQL-jobb runing once per hour an run the procedure below.
(I have not used the "netsend")

Hope it can help You
Regards
Christer

USE [YourDatabase]
GO
/****** Object:  StoredProcedure [dbo].[sp_KillIdleSpids]    Script Date: 06/27/2012 14:20:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[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>]
Example	: exec sp_KillIdleSpids 13000, 0,'You are logged out by the system'

Author	: Steve Jones - www.dkranch.net
Modifications : Benoît Ourssaire - www.soft-business.fr
Modifications : Christer Berntsson - www.softronic.se


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.
- CB 2012-06-18: Cleaned up the code
- CB 2012-06-18: Add code to manage Nav 2009 RTC, Classic and NAV 5.0 sp 1 - Classic client
- CB 2012-06-27: Exeptions: User "DUL" should not be logged out.

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

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 
		(
		rtrim(s.program_name) = 'Microsoft Dynamics NAV RTC'			-- NAV 2009 R2 RTC
		OR
		rtrim(s.program_name) = 'Microsoft Dynamics NAV Classic client'	 --NAV 2009 R2
		OR
		rtrim(s.program_name) = 'Microsoft Dynamics NAV client'			-- NAV 5.0 Sp1
		)
		and s.nt_username not like '%DUL%'								-- Exeptions: User "DUL" should not be logged out.
		and (datediff( ss, s.last_batch, getdate()) > @sec)

	open U_curs

	fetch next from U_curs into @spid, @hostname

	while @@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

sanjeevasawale

Thank you for your help. Have tried it but problem still exist. It is not deleting the session...:(

kokyas963

sanjeevasawale wrote:

Thank you for your help. Have tried it but problem still exist. It is not deleting the session...:(

Hi ,
you can use this Function to resolve your issue,
1- you should use 2 variables:
Name DataType Subtype Length
DB Record Database
Session Record Session

2- implement your function like this:


KillSession()
DB.SETRANGE("My Database",TRUE);
DB.FINDFIRST;
Session.SETRANGE("Database Name",DB."Database Name");
Session.SETRANGE("My Session",FALSE);
Session.SETFILTER("Idle Time",'>%1',1800000);
IF Session.FINDSET
THEN
REPEAT
Session.DELETE;
COMMIT;
UNTIL Session.NEXT=0;