Note: This is an old article, citing an issue with legacy systems or providers. Some troubleshooting tips may still be applicable to newer versions. However, this article has not been tested with newer releases after 2016.
The above error message occurs in one of the following scenarios on
- During login to Navision
- Viewing current sessions in Navision
Navision 4 on SQL Server A fix for this symptom is on the Navision 4 Service Pack 3 fix list.
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”
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
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"
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
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 ELSE 0 END AS [Idle Time]
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(0 AS BIGINT) ELSE 0 END AS [Idle Time]