Hi,
When we go to File → Database → Information and try to drill down on the sessions table we get the following error:
The following SQL Server error(s) occurrec while accessing the Session table:
535,“22003”,[Microsoft][ODBC SQL Server Driver][SQL Server]Difference of two datetime columnscaused overflow at runtime.
We are using V4 SP1 executables, V2.6 Database objects, on SQL Server 2000 Enterprise.
We can run the sessions table OK from Query Analyser.
Many thanks
Justin
Superfran
Hi!
This my second post here and my first in English (sorry for my bad English ).
I have a problem with MBS 4 and SQL Server 2000. The database is running on SQL Server. When I connect with it with Navision, and then press on File → Database → Information → Sessions and finally on “Current Sessions” (in order to view what users are connected), i got this error (in Spanish):
Ha ocurrido el siguiente error SQL Server mientras se accedía la tabla Sesión:
535,“22003”,[Microsoft][ODBC SQL Server Driver][SQL Server]Difference of two database columns caused overflow at runtime.
In English, it shoulb be something like this:
SQL Server error when trying to access to Session table:
535,“22003”,[Microsoft][ODBC SQL Server Driver][SQL Server]Difference of two database columns caused overflow at runtime.
Anyone has idea of the reason of this problem and how to solve it?
Thank you very much in advance. I expect to learn and to help in this forum from now on.
Thomas Brodkorb Advanced Member
Posted - 2006 Jan 27 : 11:16:06
Try to create a new form using the form wizard based on the SESSION table.
If that works: Navision has a problem !!!
Thomas Brodkorb
Senior Product Manager for Smart RetailSuite
For budget reasons, the light at the end of the tunnel had to be switched off.
Fritz Brande
Member
The Session ‘table’ is actually a SQL Server View that the runtime creates in each Navision database to provide the info for the Session virtual table. Beginning with v4.0, one of the columns of this view calculates the “Idle Time” for each session by subtracting two Datetime values. The mibuso post displays a snip from the view that shows this field is calculated as follows:
When a session has been idle for a very long time, it appears that this calculation produces an overflow – and you get the error message.
SQL Books Online has the following note in the docs for DATEDIFF:
quote:
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
Hmm – a session must be idle for 68 years…???
Perhaps the problem is not an idle session, but rather a session with a very small (or null) datetime in the [last_batch] column of the sysprocs table…
Workaround(s):
?) Use Enterprise Manager or some such non-Navision tool to kill the idle session(s)…
?) Shutdown and restart SQL Server…
Superfran
Starting Member Posted - 2006 Jan 30 : 11:15:20
Thank you very much for the info! I’ll do some tests .
Thomas Brodkorb
Advanced Member
Posted - 2006 Jan 30 : 11:36:24
As DATEDIFF produces a MILLISEC diff. it is sufficient to have an idle session for more than 24 days.
Thomas Brodkorb
Senior Product Manager for Smart RetailSuite
For budget reasons, the light at the end of the tunnel had to be switched off.
Superfran Posted - 2006 Jan 31 : 13:07:59
Hi!
I modified the Sessions view in SQL Server, and now it only shows registers where spid > 50. It works fine!
Thank you very much!
Craig Webber
Starting Member
Everyone,
I’m really new to Navision 4.0 and have hit this same overflow condition in the database.sessions view. I was examing the code and I’m not great a coding in SQL. Would I make the change in the WHERE clause of the sessions view of somewhere else:
Current: Where SP.[ecid] = 0
FIX?: Where SP.[ecid] > 50
Can anyone help by showing the code change in the Sessions view?
Thanks in advance for your help!
Joerg Stryk
Advanced Member
Posted - 2006 Apr 21 : 09:50:05
What you also could do is this:
Create a new database on the SQL Server with the 4.00 client - e.g. name it “Reference”.
Go to the “Views” of this new database, right-click on “Session” and select “All Tasks” - “Gererate SQL Script”. Here, on page “Formatting” select “DROP” and “CREATE”, then click “OK” to create the script.
(Alternatively, you can use the “Preview” feature and copy & paste the TSQL to the “Query Analyzer”).
Now you can delete the “Reference” db.
Run this script in “Query Analyzer” on the problematic database, this would replace the “Session” view, the problem should be fixed …
Joerg A. Stryk
(MCP, MBSCP)
Pearle Europe Coöperatief U.A.
CompetenceCenter
Excellent, thanks for that.
I had the same problem and modified the Session view as follows:
Existing Code extraction:
CASE WHEN SP.[cmd] = ‘AWAITING COMMAND’ THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)
Changed to:
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]
Hope this helps.
Have you tried to synchronize the users? Sometimes it helps.
good luck.