SQL Kill Session script on NAV 2009

bhwong
Member Posts: 13
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'
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'
0
Comments
-
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
RETURN0 -
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 321 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions