mibuso.com

Microsoft Business Solutions online community
It is currently Thu Apr 17, 2014 8:51 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 25 posts ]  Go to page Previous  1, 2
Author Message
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Tue Dec 15, 2009 9:29 am 
Offline

Joined: Thu Aug 20, 2009 11:17 am
Posts: 111
Country: Mongolia (mn)
I have run it!!! \:D/

as said the scripts should be executed as these way:
Job sp_KillIdleSpids.sql -> msdb
sp_KillIdleSpids.sql -> master

and then in sp_KillIdleSpids.sql in these part you need to change the value %Navision% to something %nav% cause the program_name is running under "Microsoft Dynamics NAV client" something like that so it will select nothing.

select s.spid, s.hostname
from master..sysprocesses s
where s.program_name LIKE '%Navision%'

After this you need to enable xp_cmdshell. In order to do that you need to go to SQL Surface Area Configuration and enable it.

To enable the netsend you need to start the service.

thats all folks


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Feb 11, 2010 8:05 am 
Offline

Joined: Thu May 01, 2008 1:57 pm
Posts: 379
Location: Ahmedabad, India
Country: India (in)
Hello All,

Specified SQL Scripts are running successfully in my SQL Server without any error.
I have modified Program Name filter to '%Dynamics%'.

But, My NAV Classic and RTC are not going to close on end user PC.
What can be the issue?

Please, help me.
Thanks.

_________________
Ravi_Thakkar
Ahmedabad, Gujarat, India
E Mail : ravi.thakkar@hotmail.com


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Feb 25, 2010 11:40 am 
Offline

Joined: Thu Feb 25, 2010 11:37 am
Posts: 1
Country: Italy (it)
I have sql 2008 and the job retourn me this error:

Don't exist a cursor named 'U_curs'. [SQLSTATE 34000] (Error 16916). The step failed.

Someone can help me?


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Fri Mar 12, 2010 11:24 am 
Offline

Joined: Fri Dec 30, 2005 11:20 am
Posts: 126
Location: Pretoria
Country: South Africa (za)
Hi

I wrote something in NAV Codeunit 1 that kills the sessions. Except that it only works on a native database.
In SQL it deletes the entry from the Session table, and you can see it is gone, but as soon as the user opens a form the session is back again.

So I downloaded this, implemented it and the same result!
Session entry is removed, but when you click on a menu item or navigate through records a new session entry is created with a new session ID. :?:

Regards,


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Sep 16, 2010 8:14 am 
Offline

Joined: Thu Sep 16, 2010 7:41 am
Posts: 2
Country: Bulgaria (bg)
Hello,

I have sql 2005 and I have the same problem like sunil_knd, the job return me this error:

A cursor with the name 'U_curs' doesn't exist. [SQLSTATE 34000] (Error 16916). The step failed.

What could be the cause for such a problem?

Regards.


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Sep 16, 2010 9:45 am 
Offline

Joined: Thu Sep 16, 2010 7:41 am
Posts: 2
Country: Bulgaria (bg)
Hi ...

I fixed the problem. Actually it was something simple but ... Anyway, the problem was that in fact a cursor with the name 'U_curs' doesn't exist!!! It exists but not with capital letter. I changed the capital 'U_curs' to 'u_curs' in the file sp_KillIdleSpids.sql at line ... deallocate U_curs and this error message doesn't appear any more.

Regards.


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Jun 23, 2011 7:06 am 
Offline

Joined: Thu Jun 23, 2011 6:54 am
Posts: 4
Country: Thailand (th)
dareme76 wrote:
Dear All,

I have set the scripts onto an environment having SQL 2005 & Nav2009

The issue is that when the sql scripts run, when one views file > database > information > sessions the idle users are correctly removed from the list.

BUT

i) The user does not receive a message indicating that he has been killed
ii) The user can continue using navision not needing to login again.

kindly advise

D


Dear All,
I Have same problem too. But SQL 2008 & Nav2009 R2
i) The user does not receive a message indicating that he has been killed
ii) The user can continue using navision not needing to login again.
iii) New user can login If Old Users continue using navision then overflow the licence

How to fix this issue pls kindly advise


Attachments:
Capture.JPG
Capture.JPG [ 22.17 KiB | Viewed 3221 times ]
Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Fri Jun 08, 2012 9:52 am 
Offline

Joined: Tue Aug 05, 2008 2:19 pm
Posts: 84
Location: Delhi
Country: India (in)
was this issue solved? even the same is happening in my case

"I Have same problem too. But SQL 2008 & Nav2009 R2
i) The user does not receive a message indicating that he has been killed
ii) The user can continue using navision not needing to login again.
iii) New user can login If Old Users continue using navision then overflow the licence"

sebius wrote:
dareme76 wrote:
Dear All,

I have set the scripts onto an environment having SQL 2005 & Nav2009

The issue is that when the sql scripts run, when one views file > database > information > sessions the idle users are correctly removed from the list.

BUT

i) The user does not receive a message indicating that he has been killed
ii) The user can continue using navision not needing to login again.

kindly advise

D


Dear All,
I Have same problem too. But SQL 2008 & Nav2009 R2
i) The user does not receive a message indicating that he has been killed
ii) The user can continue using navision not needing to login again.
iii) New user can login If Old Users continue using navision then overflow the licence

How to fix this issue pls kindly advise


Top
 Profile  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Mon Jan 21, 2013 4:13 pm 
Offline

Joined: Mon Jan 21, 2013 2:23 am
Posts: 2
Country: United States (us)
I implemented this solution in my environment and executed and it worked perfectly from my view. Since it worked great, I created a job in SQL to run by COB. It run for 2 days until I started to hear from users that they were loosing their settings.

I have disabled this until I figure out what exactly is going on.

Has anyone experience this as well?


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Fri Jun 28, 2013 5:04 am 
Offline

Joined: Wed Feb 16, 2011 11:42 am
Posts: 3
Country: India (in)
Script for kill SQL session in Navision DB..


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_KillIdleSpids]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_KillIdleSpids]
go
create procedure [dbo].[sp_KillIdleSpids]
as
declare
--@sec int=null,
--@diff int,
@spid int,
@cmd char(255),
@loopcount int

set @loopcount=( select count(s.spid) as count1 from master..sysprocesses s
where program_name LIKE '%NAV%'
and ((select DATEDIFF(SS,s.last_batch,GETDATE()))>180) ) --180 stands for 3 minutes

while @loopcount!=0
begin
set @spid=(select top 1 s.spid
from master..sysprocesses s where program_name LIKE '%NAV%'
and ((select DATEDIFF(SS,s.last_batch,GETDATE()))>1))
set @cmd=convert(char(4), @spid)
exec( 'kill ' +@cmd);
SET @loopcount =@loopcount-1
end


Create the SQL JOB

define in step
exec sp_KillIdleSpids


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 25 posts ]  Go to page Previous  1, 2

All times are UTC + 1 hour [ DST ]


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: