mibuso.com

Microsoft Business Solutions online community
It is currently Sat Jul 26, 2014 12:11 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 25 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Thu Aug 19, 2004 7:49 pm 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2043
Location: Wilrijk, Belgium
Country: Belgium (be)
Kill Idle Navision Sessions (SQL scripts)
This is a modification of the TSQL script from Steve Jones - www.dkranch.net I use since several years.
This new version kills only programs with "Navision" in their name and warn users with a "net send" message.
This archive contain a SQL Server job which run the script once per minute and kills idle sessions for more than 30 minutes.

http://www.mibuso.com/dlinfo.asp?FileID=367

Discuss this download here.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 20, 2005 12:12 pm 
Offline

Joined: Mon Dec 09, 2002 9:08 am
Posts: 4
How to implement this on Microsoft SQL 2000 Server?


Top
 Profile  
 
 Post subject: hi
PostPosted: Fri Sep 02, 2005 8:24 am 
Offline

Joined: Fri Sep 02, 2005 8:12 am
Posts: 3
Location: India
Country: India (in)
the scri[pts are there but how do we use these scriptp on microsoft sql server 2000 .. plzz reply soon


Top
 Profile E-mail  
 
 Post subject:
PostPosted: Fri Sep 16, 2005 5:52 am 
Offline

Joined: Wed Jan 19, 2005 11:55 am
Posts: 84
Location: West Port, Malaysia
Country: Malaysia (my)
Basically, the script split into 2: -

1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql)
- To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name.
Code: Select all
@database_name = N'Navision Demo',

Execute it in SQL Query Analyzer.
- According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
- The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)

2.) Create store procedure. (sp_KillIdleSpids.sql)
- This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.

I hope this helps.

_________________
Regards,
Peter Ng


Top
 Profile  
 
 Post subject: Thank you...but...
PostPosted: Tue Jul 04, 2006 6:13 am 
Offline

Joined: Fri Jun 30, 2006 9:34 am
Posts: 10
Location: seoul
Country: Korea, Republic of (kr)
Hi..

I solved this problem by above solusion

Here, compile location is important.

sp_KillIdleSpids.sql ==> master database
Job sp_KillIdleSpids ==> msdb database
8) 8) 8)


But another problem is ....

"Net Send ..." does not execute...


Why not ??? ](*,) ](*,) ](*,)


※ Win2K Server, SQL Server 2K, Navision 4.0


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 03, 2006 7:36 pm 
Offline

Joined: Tue Aug 01, 2006 5:52 pm
Posts: 129
Location: Detroit, MI
Country: United States (us)
Is there a way to identify all the sesssions that connected to a particular database and kill those?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 03, 2006 8:07 pm 
Online
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7989
Location: Howell, MI
Country: United States (us)
There's a database name field, is that what you are looking for?

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 03, 2006 8:15 pm 
Offline

Joined: Tue Aug 01, 2006 5:52 pm
Posts: 129
Location: Detroit, MI
Country: United States (us)
Yes, but that is not in sysprocesses table. But I got the answer from another place. You need to link sysprocesses with sysdatabases based on dbid and then you will get all the spid's connected to that database.

Thanks for the reply though.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 03, 2006 8:17 pm 
Online
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7989
Location: Howell, MI
Country: United States (us)
There's a view called 'Sessions' in your NAV database, and the links are in the definition. If you need to modify it, do it on a copy of the view.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 04, 2006 2:32 pm 
Offline

Joined: Tue Aug 01, 2006 5:52 pm
Posts: 129
Location: Detroit, MI
Country: United States (us)
We are using SQL server database and I was looking for a script to kill the sessions before restoring a database.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Aug 04, 2006 3:33 pm 
Offline

Joined: Tue Aug 01, 2006 5:52 pm
Posts: 129
Location: Detroit, MI
Country: United States (us)
Forget about my previous post. I thought you were refering to some navision table.

The script I got does the same thing as the 'Sessions' view. Thanks anyway.


Top
 Profile  
 
 Post subject: net send
PostPosted: Thu Dec 13, 2007 12:12 pm 
Offline

Joined: Thu May 23, 2002 7:01 am
Posts: 1
Location: Riga
Country: Latvia (lv)
"NET SEND" doesn't work, but if you use terminal server better to use MSG command, so in this case you can rewrite stored procedure like following:

set @cmd='msg ' + ltrim(rtrim(@username)) + ' /SERVER:' + ltrim(rtrim(@hostname)) + ' ' + @msg

You also need to add new variable @username and take value from nt_username like this:

......
declare u_curs scroll insensitive cursor for
select s.spid, s.hostname, s.nt_username
from master..sysprocesses s
where s.program_name LIKE '%Navision%'
and (datediff( ss, s.last_batch, getdate()) > @sec)

open u_curs

fetch next from u_curs into @spid, @hostname, @username
......


Top
 Profile  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Wed Sep 23, 2009 12:05 pm 
Offline

Joined: Mon Nov 19, 2007 3:55 pm
Posts: 10
Location: malta
Country: Malta (mt)
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


Top
 Profile  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Sun Dec 06, 2009 12:46 pm 
Offline

Joined: Mon Aug 10, 2009 12:24 pm
Posts: 144
Hi guy!

I try to implement this example on my PC ( SQL Server 2008 + Windows 7) and try with Dynamics NAV 5.0 SP1 but so far it didn´t work.

Below i describe the steps for implemeting this Scripts on SQL:



1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql) - To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name.
@database_name = N'Demo Database (5-0) -> Here i pointing this step into my Database ->Demo Database (5-0), but i dont have sure because ranian92 refers to msdb database .QUESTION: HERE TO Point THIS JOB? AND I CHANGE THE TIME TO 60 seconds
2.) Execute it in SQL Query Analyzer.
- According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
- The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)
I EXECUTED AND THE COMAND WORKS WITHOUT ERRORS
3.) Create store procedure. (sp_KillIdleSpids.sql)
- This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.
WHERE TO CREATE THE STORE PROCEDURE? ON MY DATABASE OR ON THE MASTER DATABASE like ranian92 refers?

LAST QUESTION: How to test. I go to Dynamics NAV > Database Information -> Sessions ->and i try to see if the inactive time for a session is greater than 1 minute. But the time is constantly moving and the number of licences do not decrease.


Top
 Profile E-mail  
 
 Post subject: Re: Kill Idle Navision Sessions (SQL scripts)
PostPosted: Mon Dec 14, 2009 10:47 am 
Offline

Joined: Thu Aug 20, 2009 11:17 am
Posts: 111
Country: Mongolia (mn)
nav_student wrote:
Hi guy!

I try to implement this example on my PC ( SQL Server 2008 + Windows 7) and try with Dynamics NAV 5.0 SP1 but so far it didn´t work.

Below i describe the steps for implemeting this Scripts on SQL:



1.) Add job to SQL Agent. (Job sp_KillIdleSpids.sql) - To install it, you need to start SQL Server Agent, and change the databasename in the script into your database name.
@database_name = N'Demo Database (5-0) -> Here i pointing this step into my Database ->Demo Database (5-0), but i dont have sure because ranian92 refers to msdb database .QUESTION: HERE TO Point THIS JOB? AND I CHANGE THE TIME TO 60 seconds
2.) Execute it in SQL Query Analyzer.
- According to the script, it will actually schedule to run the script to perform checking every minutes between 8am to 6pm.
- The task is stored in msdb database in tables(sysjobs, sysjobschedules, sysjobservers, and sysjobsteps)
I EXECUTED AND THE COMAND WORKS WITHOUT ERRORS
3.) Create store procedure. (sp_KillIdleSpids.sql)
- This store procedure will be trigger by the job above, to kill the user from the database and net send message to their screen.
WHERE TO CREATE THE STORE PROCEDURE? ON MY DATABASE OR ON THE MASTER DATABASE like ranian92 refers?

LAST QUESTION: How to test. I go to Dynamics NAV > Database Information -> Sessions ->and i try to see if the inactive time for a session is greater than 1 minute. But the time is constantly moving and the number of licences do not decrease.


I have same result... Can anyone tell us how we should test? or what is wrong?


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

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 8 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: