mibuso.com

Microsoft Business Solutions online community
It is currently Fri Oct 31, 2014 6:32 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Problem SQL Kill Session script on NAV 2009
PostPosted: Tue Jul 05, 2011 10:17 am 
Offline

Joined: Fri Apr 24, 2009 7:52 am
Posts: 8
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


Attachments:
Session.png
Session.png [ 38.6 KiB | Viewed 2381 times ]
Top
 Profile E-mail  
 
 Post subject: Re: Problem SQL Kill Session script on NAV 2009
PostPosted: Tue Jul 05, 2011 11:36 am 
Offline

Joined: Thu Oct 30, 2008 1:29 am
Posts: 57
Country: Australia (au)
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


Top
 Profile E-mail  
 
 Post subject: Re: Problem SQL Kill Session script on NAV 2009
PostPosted: Tue Jul 05, 2011 3:11 pm 
Offline

Joined: Wed Nov 24, 1999 7:01 am
Posts: 581
Location: Aalborg,Denmark
Country: Denmark (dk)
NAV2009 doesn't need traceflag 4616 - could this missing traceflag cause problems for the script?

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: Problem SQL Kill Session script on NAV 2009
PostPosted: Wed Jul 06, 2011 4:53 am 
Offline

Joined: Fri Apr 24, 2009 7:52 am
Posts: 8
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


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


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: