Hi MS NAV Forum
Wrote a routine in NAV that kills idle user processors if they surpass an hour of inactivity. One admin user logs in and runs this routine under a job queue (in NAV Client not a NAS routine). This routine works great 99% of the time but every now and again the admin user gets the error...
This message is for C/AL programmers:
The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message:
User does not have permission to use the KILL statement.
The User resets the Job Queue and it works perfectly again until the above error happens again! This routine runs for weeks with no problems and then the above happens - any suggestions?
I would guess that the NAS user is not an admin or something like this? We have also implemented a session killer (the one from Alain Krikilion, mibuso, taken as template and went from there), and it works most of the time - meaning as long as the machine it's running on is in working condition. However there is an oldschool trick to work around such issues: Set up a scheduler who tries to start your NAS service every 5 minutes. Should it die unexpectedly (GUIALLOWED, a real error, something else) it will come back to life. This way at least you're collecting data in the windows event logs to track the real issue down, eventually, without disrupting the service too much.
with best regards
Thanks for your quick answer - appreciate. No not using NAS - just running the Job Queue from the Client. Yes I borrowed too the Alain Krikilion routine too and lifted the functionality for a Classic equivalent.
So would you recommend NAS - could this run the Job Queue as a Reset, so that it is always active regardless of the previous error condition?
Any ideas why this works 99% of the time but then SQL complains occasionally?
only a a suggestion.
if you have SQL Server, you can use a short scheduled stored procedure like this, lauched from sql jobs scheduling (every 5 minutes for example).
****** Object: StoredProcedure [dbo].[KillNavActiveSessioneActiveTrans] Script Date: 10/01/2013 17:12:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: <Roberto Stefanetti> — Create date: <20/02/2013> — Description: <KILL NAV Sessions Sleeping> — =============================================
CREATE PROCEDURE [dbo].[KillNavSleepingSessionsWithInactiveTrans]
AS BEGIN — Kill NAV Session (Web Services sessions excluded) sleeping without open transactions from 20 minutes
DECLARE @v_spid INT DECLARE c_Users CURSOR FAST_FORWARD FOR SELECT spid FROM master..sysprocesses (NOLOCK) WHERE spid>50 AND DATEDIFF(mi,last_batch,GETDATE())> 20 AND spid<>@@spid AND (program_name LIKE N’111%’ OR program_name LIKE N’Microsoft Dynamics NAV RTC%’ OR program_name LIKE N’Microsoft Dynamics NAV Classic client%’) AND (program_name not LIKE N’Microsoft Dynamics NAV WEB%’) AND status=’sleeping’ AND open_tran = 0
OPEN c_Users FETCH NEXT FROM c_Users INTO @v_spid WHILE (@@FETCH_STATUS=0) BEGIN PRINT ‘KILLing ‘+CONVERT(VARCHAR,@v_spid)+’…’ EXEC(‘KILL ‘+@v_spid) FETCH NEXT FROM c_Users INTO @v_spid END
CLOSE c_Users DEALLOCATE c_Users
My Blog http://rsdynamicsnavblog.wordpress.com
Hi Roberto, thanks for your suggestion.
I've similar issue and want to find out what's the best way to kill idle sessions automatically.
But I found your SQL script can not list out the sessions that I will to kill in table ..[dbo].[Active Session].
Do you think is it better if I create the script and delete record on ..[dbo].[Active Session],
Please correct me if I am wrong, thanks
Taken away the SQL link and used session.delete (True) as you recommended by Alain Krikilion. With the client as we speak!
Keep up the great work
Thanks for the SQL Code - really appreciate. Sticking with the NAV Client option for now but will convert to the SQL option if still not workin!
Keep up the good work
Try this www.mibuso.com/howtoinfo.asp - does everything for you!
Thanks for your info, but I prefer to do it at SQL Server Level, I tried Roberto's SQL but it couldn't query out the idle sessions I would like to kill (I am using NAV 2013 & SQL Server 2012).
I've setup a SQL Server Agent scheduled job and delete those session has been login over certain hours
DELETE FROM [PROD].[dbo].[Active Session] WHERE DATEDIFF(mi,[Login Datetime],GETDATE())>=960
great idea, good.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics