Follow

Error: “Difference of two datetime columns caused overflow at runtime” in Navision

Symptoms

The above error message occurs in one of the following scenarios on
  1. During login to Navision
  2. Viewing current sessions in Navision

Navision versions

Navision 4 on SQL Server A fix for this symptom is on the Navision 4 Service Pack 3 fix list.

Cause

Navision creates a view in the SQL Server database called Session which relies on the Last Batch column of the table Sysprocesses to calculate an idle time for sessions. This time difference calculation causes an overflow when there is no Last Batch information as described below. When a non-Navision client such as timeXtender or Microsoft Office is connected to the Navision SQL Server database, there is no information in the Last Batch column. Because clients like these doesn’t issue a execute command or remote stored procedure call during connect, the Last Batch column has the default value of “1900-01-01”

Possible work-arounds

There are a number of different modifications which can be applied to the Session view

1. Use current date/time if last batch is 1900-01-01

Replace
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time" 

With

CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, (CASE WHEN SP.[last_batch] = '1900-01-01' THEN getdate() ELSE SP.[last_batch] END), GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"

2. Filter on the application name to ignore specific clients or to only show Navision clients

If you add the following to the where clause, timeXtender is ignored in the sessions list as timeXtender is seen from SQL Server as a .Net client SP.
[program_name] <> '.Net SqlClient Data Provider'

3. A common suggestion in Navision forums is to use the following

Replace
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 ELSE 0 END AS [Idle Time] 
With
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(0 AS BIGINT) ELSE 0 END AS [Idle Time]

Disclaimer

This document is prepared with the knowledge available to timeXtender at the time of preparation, but we cannot guarantee that all information is correct at the time it is used. All information is provided “as-is” for informational purposes and timeXtender makes no warranties, express or implied in this document. Åbyhøj, February 2009
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.