Hi! This my second post here and my first in English (sorry for my bad English [:I]). 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. [:)]
Try to create a new form using the form wizard based on the SESSION table. If that works: Navision has a problem !!!
This problem has been kicked around a bit over on mibuso. For example, see http://www.mibuso.com/forum/viewtopic.php?t=6962 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:CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST( DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) * 1000 ELSE 0 END AS [Idle Time]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…
Thank you very much for the info! I’ll do some tests [:)] .
As DATEDIFF produces a MILLISEC diff. it is sufficient to have an idle session for more than 24 days.
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! [:)]
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!
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 …