Error Message on accessing Session Table in Nav 4.0

Comments

• Options

  Nobody Member Posts: 93

  2006-07-14

  Yes, It is error out on records in the sysprocesses table from other applications that have a last batch date of <= 1900. It tries to calculate the the time in seconds from the last batch date and it is overflowing the field.
  
  You can rewrite the session view to ignore those entries like with something like this. Or you can call support and get a hotfox for it that does the same thing.
  
  **************
  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Session]') and OBJECTPROPERTY(id, N'IsView') = 1)
  drop view [dbo].[Session]
  GO
  
  CREATE VIEW [dbo]."Session"
  AS
  SELECT
  CAST(SP.[spid] AS INTEGER) AS "Connection ID",
  CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "User ID",
  @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 ((DATEDIFF(YEAR, SP.[last_batch], GETDATE()))*1000) > = 68
  THEN CAST(0 as bigint)
  WHEN ((DATEDIFF(YEAR, SP.[last_batch], GETDATE()))*1000) < 68
  THEN (CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000)
  ELSE 0 END AS [Idle Time]
  FROM [master].[dbo].[sysprocesses] AS SP 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
  
  GO
  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO

  0