0

I have a problem: on a simple select like SELECT * FROM table SqlDateTime overflow error is randomly returned (few times it works OK after that error is returned; after that it again works few times and after that error is returned again) - error occurs on the same row (while using the same connection) - if I open and close MGMT Studio, error occurs on different row.

Exact error message is:

An error occured while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Table has 3 DateTime columns:

  • DTcolumn1 - can be null, without default value
  • DTcolumn2 - must not be null, default value ('1800-01-01')
  • DTcolumn3 - can be null, without default value

Values in all 3 DateTime columns look fine (null or inside of the allowed interval).

Table also has some other columns of varchar and other types. It is more likely that select query will fail more often if I add order by one of those 3 DateTime columns (empiricaly tested).

Collation of the database is Slovenian_CI_AI.

What is causing this error (as I said - DateTime values seem to be OK)?

Thank you in advance!

EDIT 1 (2016-05-09): I forgot to mention it previously: error happens in SQL MGMT Studio and from code (using LINQ to SQL).

EDIT 2 (2016-05-10): It seems there is different problem - on every table with more than let's say 10000 records it throws some silly error. On some other table it throws:

An error occurred while executing batch. Error message is: Internal connection fatal error.`

It also disconnects me from database (in the bottom status row it says disconnected). SQL server is installed on remote server inside of local network.

Marko Kastelec
  • 11
  • 7
  • _"error always occurs on the same line"_ what line? `SELECT * FROM table` is not your original query. Show that. – Tim Schmelter May 09 '16 at 11:49
  • When error occurs always the same first ten rows are returned. – Marko Kastelec May 09 '16 at 11:53
  • what is your real query since it's not `SELECT * FROM table`. Do you use a filter? – Tim Schmelter May 09 '16 at 11:54
  • When it overflows like that it usually indicates that the datetime somewhere is handled as a string. Triggers? Views? etc? – Allan S. Hansen May 09 '16 at 11:57
  • @TimSchmelter: table is meant as table name. It also could be 'SELECT * FROM persons' or something like that. – Marko Kastelec May 09 '16 at 11:57
  • @AllanS.Hansen: no, it's plain select from table without triggers appended. – Marko Kastelec May 09 '16 at 11:58
  • Does it happen from management studio as well as code? Or only in one or the other? – Allan S. Hansen May 09 '16 at 12:01
  • @AllanS.Hansen: Sorry, I forgot to mention that in my post - it happens from MGMT Studio and from code. – Marko Kastelec May 09 '16 at 12:04
  • So, what is the date and time in the row which doesn't work? Try to select the lines with a cast to nvarchar... so you see the record which fails. My first guess would be a problem in the collation. Maybe - for one reason or another - a value like "31/12/2000" is returned instead of the reuired "12/31/2000" (dummy values only as example). – Tyron78 May 09 '16 at 12:12
  • @Rene: I meslead you. If I close and reopen MGMT Studio error happens on different row (but until I close and reopen it again on the same row). Dates in the database look like that: `1800-01-01 00:00:00.000`. If I cast it to nvarchar(max) it looks like `Jan 1 1800 12:00AM`. It is really strange because it happens really randomly and the data looks fine... – Marko Kastelec May 09 '16 at 12:21
  • Oh, OK. Could you please evaluate, if the milliseconds (.000) are different? Maybe the ones with .000 work fine while others don't? I had this problem once when loading datetime from SQL to XLS... but again - only a guess by me. – Tyron78 May 09 '16 at 12:43
  • @Rene: Sometimes all rows are returned without a problem. After lets say 5 successful select query executions SqlDateTime overflows happens (on data that was successfully returned 5 times before that?!). Milliseconds are different (I only gave an example of one of the values). I can append list of all distinct values (if it helps?) - it's 50 different DateTime values. – Marko Kastelec May 09 '16 at 13:02
  • Which edition and version of SQL Server are you using? Is the SQL Server on your local machine or on another computer? – RichardCL May 09 '16 at 14:24
  • How many rows are in the table? Can you post some sample data, enough to reproduce the problem. – RichardCL May 09 '16 at 14:25
  • It would help to see the structure of the table. – RichardCL May 09 '16 at 14:30
  • Hi everyone! Firstly - tnx for your help. I posted additional findings in my original post (check EDIT 2). – Marko Kastelec May 10 '16 at 10:38

1 Answers1

0

Our admin found out, that the problem is with DNE LightWeight Filter. If this monster is disabled everything works as it is supposed to (no random disconnects with strange errors).

You can disable it if you go to Control Panel / Network and Sharing center / Change addapter settings. Right click on your network device and select Properties. Deselect DNE LightWeight Filter.

Link to Server Fault, where I posted the question when we started to believe that this is network related problem.

Community
  • 1
  • 1
Marko Kastelec
  • 11
  • 7