5.0 session table

I got a hanging app just form resizing the sessions window

And got an answer: the error is known, and will be solved in the next update, scheduled for the end of august

A challenge!

4.0 SP3 SQL Syntax:

SELECT  * FROM "Navision Demo Database (4_0 SP3)"."dbo"."Session" WHERE  "Connection ID">=@P1 ORDER BY "Connection ID" OPTION (FAST 10)

5.0 SQL Syntax:

SELECT  * FROM "Dynamics NAV (5_0) Demo Database"."dbo"."Session" WHERE  "Connection ID"<@P1 ORDER BY "Connection ID" DESC

When watching the SQL Server Profiler...it hangs on the following statement:

FETCH API_CURSOR0000000000000016

Once this statement is called, Dynamics NAV hangs at around 40% utilization on my development box. After a period of time, it recovers...

What's interesting about this issue is the fact that the sort order is not by Connection ID as I'd expect. In Dynamics NAV 4.0 SP3, this issue does not occur, but in 5.0, it does...

So...what does this all mean?!

It looks to me that NAV is not getting distinct records upon the query, and for some reason, when the subquery is executed, it causes an issue in which the client locks up because it's trying to fetch a cursor position that does not exist.

How do we fix it?

Easy...set the database session table so that it is a "SELECT DISTINCT"...while there is a performance/compatibility issue with this (potentially), it seems to do the trick:

USE [Dynamics NAV (5_0) Demo Database]
GO
/****** Object:  View [dbo].[Session]    Script Date: 07/13/2007 23:49:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Session]
AS
SELECT DISTINCT 
                      CAST(SP.spid AS INTEGER) AS [Connection ID], CAST(RTRIM(SP.loginame) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [User ID], 
                      CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session], CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.login_time, 
                      108), 120) AS [Login Time], CONVERT(DATETIME, CONVERT(CHAR(10), SP.login_time, 120) + ' 00:00:00:000', 121) AS [Login Date], 
                      CAST(SD.name AS NVARCHAR(128)) COLLATE Latin1_General_CS_AS AS [Database Name], CAST(RTRIM(SP.program_name) 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Application Name], CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS [Login Type], 
                      CAST(RTRIM(SP.hostname) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Host Name], SP.cpu AS [CPU Time (ms)], 
                      CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage] * 8 END AS [Memory Usage (KB)], SP.physical_io AS [Physical I_O], 
                      CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS Blocked, CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) 
                      AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) AS [Blocking Connection ID], CAST(ISNULL(RTRIM(SPB.loginame), '') 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking User ID], CAST(ISNULL(RTRIM(SPB.hostname), '') 
                      AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Host Name], CAST('' AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS [Blocking Object], 
                      CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 ELSE 0 END AS [Idle Time]
FROM         master.dbo.sysprocesses AS SP INNER JOIN
                      master.dbo.sysdatabases AS SD ON SP.dbid = SD.dbid LEFT OUTER JOIN
                      master.dbo.sysprocesses AS SPB ON SP.blocked = SPB.spid
WHERE     (SP.ecid = 0) AND (SP.last_batch >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120))

Make sure to change the database to the correct database. YMMV with this modification, but it did the trick for me...no more lock-ups and I could change the sort orders without issues.

Enjoy!

- Scott

Scott, this does work wonderfully. However, could this have any influence on the next update? I don't mind running this on testservers, but on client databases, I am a bit more reluctant...

Thank you so much

The problem is fixed in build 24917 (KB938400)

https://premier.microsoft.com/default.a ... -us;938400

Login is required.

/Bjarne