;WITH cte_Date ( DateCode_FK ) AS ( SELECT DATEADD( DAY, 1 - ROW_NUMBER() OVER ( ORDER BY so1.object_id ), GETDATE() ) FROM sys.objects so1 CROSS APPLY sys.objects so2 ) SELECT TOP 10 d.DateCode_FK FROM cte_Date d ORDER BY d.DateCode_FK DESC;
Nothing an overly interesting query, but i'm receiving an error message if I run it with the
ORDER BY clause:
Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused an overflow.
However, without the
ORDER BY clause, it runs just fine. In addition, if I run the query on other catalogs contained in the same instance on the same server, the query runs fine with or without the
ORDER BY clause.
I've taken a look at the configuration options and compatibility levels between the affected catalog and a catalog on which the query runs as expected, but have not found anything that might warrant the difference. Has anybody else run into a similar issue? I can work around it for now, but would ideally need to be able to fix the problem, whatever it is.
Potential hint - if you have a relatively large number of objects in a catalog ( > 5000 ), you -may- be able to reproduce the error... This is occurring on my largest catalog and it appears that if I include a TOP in the CTE, the ORDER BY issue goes away.