user session

ningnongjin · 2005-07-20

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

kine · 2005-07-20

Yes, this is problem of the new field "Idle time" - if there is some session idle for long time, the field overflow... (and it can be session which is not Navision session...)

kine · 2005-07-20

Workaround - you can alter view Session on the MS SQL server to not allow Idle Time to be bigger than some max limit...

ningnongjin · 2005-07-21

thanks for the reply..so what you're saying is that it could be another session opened for other application that sits on the same server as the Navision application?i would try this method out then...thanks once again

ningnongjin · 2005-07-21

so this 'Idle time' field where it is evident in Navision?

kine · 2005-07-21

You can see it (in normal conditions) in the table sessions (sessions list) - the form, you are not able to open because error... the time is too big for Navision to show it... - it exist only in 4.00...

ningnongjin · 2005-07-21

hi kinke,

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.

kine · 2005-07-21

ningnongjin wrote:

So i assume to counter this problem, i have to set an idle time in MS SQL to constraint it

How did you constraint it? Did you changed the definition of the Session view on the server?

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.

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:

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

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).

ningnongjin · 2005-07-21

Hi Kine,

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

kine · 2005-07-21

ningnongjin wrote:

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?

No, after logout, the session (MS SQL process) is terminated. Of course, if you are using for example terminal server for connecting to Navision DB, you can have some TS sessions which was disconnected but are still runing Navision. Check your Terminal server for disconnected sessions.

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:

select * from <DatabaseName>.Session

ningnongjin · 2005-07-21

thanks kine for your replies. your help is greatly appreciated

ara3n · 2006-03-14

Anybody fixed the view, and would mind posting the code for this issue? Thanks

deblocker · 2006-05-18

Hi,
i solved this by modifying the Session view this way:
and (SP.[program_name] like '%Navision%')

kine · 2006-05-18

Another solution than Deblocker's is to change definition of [idle time] field to something like:

CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN (CASE WHEN SP.[last_batch] < DATEADD(day, -30, GETDATE()) 
                      THEN 999999999 ELSE CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 END) ELSE 0 END AS [Idle Time]

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.

user session

Comments

Categories