Problem SQL Kill Session script on NAV 2009

kum_g7

I Develope Classic Navision. from 3.7 Until > 5.1 and alwaysUsed SQL scripts (COPY From Mibuso)
for Killing Session that have more Idle Time (It' s work on every version Below 2009)
but when use this Script on NAV 2009 Classic on SQL 2008 R2, I found the important problem that
1. When Script execute , Session are killed, but when I go to NAV Window that already killed and Open some form,
Session came back.
2. then if Customer have License 10 then they can use license more than 10.

I guess that problem from NAV 2009 have the difference architecture,
so any suggestion to fixed this problem, thank in advance

prototyper

This seems very odd.
We use NAV2009 on SQL 2008 R2 and often use Kill to terminate session and have not experienced any problem like yours.
Perhaps there is something specific to your script. If you want to post it I can have a look see.

pdj

NAV2009 doesn't need traceflag 4616 - could this missing traceflag cause problems for the script?

kum_g7

thank you for all reply. This is SQL Script that I uesd. (Credit : COPY From mibuso)
I can't find anything wrong, Please Help.

USE [MACO_23052011]
GO
/****** Object: StoredProcedure [dbo].[sp_KillIdleSpids] Script Date: 07/06/2011 10:48:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER 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>]

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 '%Microsoft Dynamics NAV Classic client%'
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