Kill Session not working

Comments

• Rikt-It Member Posts: 37

  2015-07-20

  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
  
  

  Regards
  Christer in Stockholm, Sweden

  0
• sanjeevasawale Member Posts: 63

  2015-07-21

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

  0
• kokyas963 Member Posts: 16

  2015-07-28

  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;

  0