If you upgrade to SQL 2005 by Backup/Restore, it is possible that you will receive the following error when you issue a select statement against a table with a Datetime column in it:
An error occurred 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.
This error is not caught by a standard DBCC CHECKDB. The books online for DBCC CHECKDB says:
Under some circumstances, values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server 2005 cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error.
Once you follow these directions, your should no longer encounter this problem, since databases in SQL 2005 have data validation checks in place.
For more information see:
No comments:
Post a Comment