Hi All,
Scenario:
Would like to check the number of sessions currently active in MS SQL Server, by going to File -> Database -> Information. Under Sessions tab, select the Lookup arrow and the error prompts this message
The following SQL Server error(s) occure while accessing the Session table:
535,"22003",[Microsoft][ODBC SQL Server Driver][SQL Server]Difference of two datetime columns caused overflow at runtime.
Have anyone encounter the same problem before and what's the cause and solution towards this. Thanks in advance
Kind Regds,
NN
0
Comments
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
thanks for the prompt reply. as this problem happens when the idle time has grown too big. So i assume to counter this problem, i have to set an idle time in MS SQL to constraint it. But if i start afresh by killing every session and then re-log in again, and when i check the active sessions i presume the error message would not appear as the error only occurs if the inactivity of the session is greater then the idle time being set. am i right on this matter?just trying to understand how the idle session works as i'm new to this matter cause i did not do any changes or mods to the SQL idle setting or let alone understand how it worked in the first place. thanks in advance.
How did you constraint it? Did you changed the definition of the Session view on the server?
You are not able to kill all sessions manually. Some sessions (processes) are system processes and you are not able to kill them. You have two ways:
1) restart MS SQL server - all processes will be restarted
2) exclude system processes from Session view... for example setting the view to list only processes with PID >=50
The session list work in this way:
In your DB on MS SQL server is defined one view with name Session. This view read data from many sources and create virtual table with all data which Navision show.
For example Idle time field is defined as: which means - if status of the process is AWAITING COMMAND (doing nothing) than calc time difference between time of last command and NOW (*1000 to have milliseconds) else return 0... You can change this definition to return maximal value which Navision can handle (Duration type which Navision is using for this field is 64bit integer).
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
Sorry for the misspelling earlier on. Ok i get an idea already on your explanation. So in this matter, i'm unable to kill the session manually because i can't check it in the first place, so does it mean when user logs off from Navision does it still registers it as an active session?
the problem occured due to client saying that they had not reached the number of concurrent users allowed as they have head counted who is logged in, but when i tried checking it, they can't proceed coz they have reach their user sessions limits. I'm afraid because of this Session issue that has actually caused Navision to register one or two active sessions after all users have been logged off.
thanks in advance
If you are not able to open the session list from Navision, you can still open it through MS SQL Enterprise Manager or through MS SQL Query analyzer...
for example through command:
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
i solved this by modifying the Session view this way:
and (SP.[program_name] like '%Navision%')
In this case, if the session is idle more than -30 days (it is just example, it is some limit, which prevent to have overflow), the idle time will be 999999999 ms.
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.