Select query produces SqlDateTime overflow on valid dates

Question

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.

"error always occurs on the same line" what line? SELECT * FROM table is not your original query. Show that. — Tim Schmelter
– Tim Schmelter, Commented May 9, 2016 at 11:49
When error occurs always the same first ten rows are returned. — Marko Kastelec
– Marko Kastelec, Commented May 9, 2016 at 11:53
what is your real query since it's not SELECT * FROM table. Do you use a filter? — Tim Schmelter
– Tim Schmelter, Commented May 9, 2016 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
– Allan S. Hansen, Commented May 9, 2016 at 11:57
@TimSchmelter: table is meant as table name. It also could be 'SELECT * FROM persons' or something like that. — Marko Kastelec
– Marko Kastelec, Commented May 9, 2016 at 11:57

Community · Accepted Answer · 2017-04-13 12:13:44Z

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.

edited Apr 13, 2017 at 12:13

CommunityBot

11 silver badge

answered May 20, 2016 at 11:27

Marko Kastelec

117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select query produces SqlDateTime overflow on valid dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related