NAV Kill Process Issues

(0) Share

Report

Posted on by Community Member

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?

With thanks

David

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Roberto Stefanetti 12,998 on at

Like (0)

Report

RE: NAV Kill Process Issues

hi,

great idea, good.

Was this reply helpful? Yes No
David Lo 570 on at

Like (1)

Report

RE: NAV Kill Process Issues

Hi David

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

David Lo

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: NAV Kill Process Issues

Hi David

Try this www.mibuso.com/howtoinfo.asp - does everything for you!

Good luck

David

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: NAV Kill Process Issues

Hi Roberto

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

David

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: NAV Kill Process Issues

Hi Jen

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

David

Was this reply helpful? Yes No
David Lo 570 on at

Like (0)

Report

RE: NAV Kill Process Issues

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

Was this reply helpful? Yes No
Roberto Stefanetti 12,998 on at

Like (0)

Report

RE: NAV Kill Process Issues

hi,

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).

USE [DB]

GO

****** 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

END

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: NAV Kill Process Issues

Hi Jens

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?

Thanks Jen

David

Was this reply helpful? Yes No
Jens Glathe 6,092 on at

Like (0)

Report

RE: NAV Kill Process Issues

Hi David,

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

Jens

Was this reply helpful? Yes No