I've written many and many .NET applications that interacts with Dynamics NAV (expecially .NET Web Services) but (as usual) misterious problems are always under the hood... 
I have a custom .NET Web Service used for interaction between Dynamics NAV and a Pocket PC application. This service has always worked good but today, on a customer's site (Dynamics NAV 4.02 was used), here's the problem: after a data transfer, it's impossible for new users to login with Dynamics NAV due to an error in the Session table in NAV. The error says something like "Difference of two datetime columns caused overflow at runtime".
Why this?
I've spent some hours of in-depth investigation on SQL Server and I've discovered that the error is due to a particular line inside the Session view. The line that causes the error is this:
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)
* 1000 ELSE 0 END AS [Idle Time]
Under particular circumstances, when there's an external connection to the Dynamics NAV database (for example with .NET SQL Client), the sysprocesses table contains a wrong date (01-01-1900) on the [last_batch] field.
This is what I can retrieve if I execute this query:
select cmd,login_time, last_batch, program_name from sysprocesses
This particular date causes the overflow error.
The first thing I've tryed was to correct the Session view code manually as follow:
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN
CASE WHEN SP.[last_batch] = '1900-01-01 00:00:00' THEN 0 ELSE CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 END ELSE 0 END AS [Idle Time]
This works good inside SQL Server, but not inside the NAV client. Seems that the NAV Client sends a built-in SQL string to check the Session view. This not occours with NAV 4.03 however.
The curious thing that seems to solve this problem is to upload the NAV license at the server level and not at the database level. 
I usually work without problems with licenses uploaded at the server level, but this customer had the per-database license.
By uploading the license on the server, all was good... seems that the Dynamics NAV client makes a different check of the Session table. A nice mistery...
Technorati Tag:
Dynamics NAV