SQL Kill Session script on NAV 2009

bhwong

We have a SQL Job that run every min to kill idle user from NAV 4.0 running on SQL 2005.

When we transfer the same SQL Job to NAV 2009 running on SQL 2008, this SQL job no longer function. Is the Command for Transact-SQL script require any modification for the newer version?

zsp_KillIdleUser
20, 30, 'CG Live', 0,
'Connection is been blocked when inactive more than 30mins. Please try to login Navision Client again'

bhwong

The stored procedures is located in the Master DB:


USE [master]
GO
/****** Object: StoredProcedure [dbo].[zsp_KillIdleUser] Script Date: 09/11/2012 18:06:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*********
Create Store Procedure to Kill the Idle users

Built by Jacky Wai [May 2008] Modified by bhwong [11 Sep 2012]
*********/

ALTER PROCEDURE [dbo].[zsp_KillIdleUser]
@intMaxLicense as Int = NULL, -- Only Max Connection will only Run
@intMinute as Int = NULL,
@chrdbName as Char(30) = NULL,
@bitNetSend as Bit = 0, -- Execute 'Net Send' only if set to 1 --
@chrMsg as Char(255) = NULL -- Message to send user when kill the user from the system --
AS


DECLARE
@intSPID as Int,
@chrCMD as Char(255),
@chrHostname as Char(255),
@intCurrLicense as int

SELECT @intCurrLicense = COUNT(*)
FROM sysProcesses
WHERE Program_Name Like '%Navision%'

IF @intMaxLicense > @intCurrLicense
RETURN


DECLARE cCursor CURSOR FOR
SELECT TOP 3 SPID, Hostname
--, DATEDIFF(Minute, Last_Batch, GETDATE()) DifferentTime,
--CONVERT(char(8), CURRENT_TIMESTAMP, 3) CDate, CONVERT(char(8), CURRENT_TIMESTAMP, 8) CTime,
--Last_Batch as IdleTime, Login_Time, Open_Tran, Status, Loginame
FROM sysProcesses
WHERE Program_Name Like '%Navision%'
AND dbID = (SELECT dbID FROM sysDatabases WHERE NAME = @chrdbName)
AND DATEDIFF(Minute, Last_Batch, GETDATE()) > @intMinute
AND ((Loginame <> 'bhwong') AND (Loginame <> 'lcthian.adm'))
ORDER BY Last_Batch DESC

OPEN cCursor

FETCH NEXT FROM cCursor INTO @intSPID, @chrHostname


@Fetch_Status = 0 BEGIN
EXEC('KILL ' + @intSPID)

-- Execute 'Net Send' only if set to 1 --
IF @bitNetSend = 1 BEGIN
SET @chrCMD='NET SEND ' + LTRIM(RTRIM(@chrHostname)) + ' ' + @chrMsg
EXEC MASTER..xp_cmdShell @chrCMD, no_output
END

FETCH NEXT FROM cCursor INTO @intSPID, @chrHostname
END

CLOSE cCursor
DEALLOCATE cCursor
RETURN

grahamtobin22

I think the reason your new script is not working is because in 2009 teh Application name is Microsoft Dynamics NAV and not Navision. If you change the %navision% to %Dynamics NAV% then it should work.

bhwong

I have rename the program:

WHERE Program_Name Like '%Microsoft Dynamics NAV%'

However, the error message seem to target keyword 'ON'.