Problem SQL Kill Session script on NAV 2009

kum_g7kum_g7 Member Posts: 12
edited 2011-07-06 in SQL General
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

Comments

  • prototyperprototyper Member Posts: 70
    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.
    Sleep on it... The best solutions come at 2am
  • pdjpdj Member Posts: 643
    NAV2009 doesn't need traceflag 4616 - could this missing traceflag cause problems for the script?
    Regards
    Peter
  • kum_g7kum_g7 Member Posts: 12
    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
Sign In or Register to comment.