Tuesday, May 30, 2006

Errors once, warnings now

SQL 2005 brings many breaking changes that need to be adressed when preparing migrations from previous versions. Here is a brief look at some changes in error messages from SQL 2000 to SQL 2005. These aren't (or at least shouldn't be) vital to the majority of migration cases; still, this post (and the next one) provides an overview, that may prove to be useful - if only for a moment or two.

Error 1945 (SQL 2005) Severity 10Error 1945 (SQL 2000) Severity 16
Warning! The maximum key length is %d bytes. The index "%.*ls" has maximum length of %d bytes. For some combination of large values, the insert/update operation will fail.
(Same message text in SQL 2000.)

Comments: What is effectively a warning in SQL 2000 becomes a proper warning in SQL 2005. The index is created even if the maximum length is exceeded. However, the statement that violates this constraint will be aborted.

Error 2519 (SQL 2005) Severity 10Error 2519 (SQL 2000) Severity 16
Computed columns and user-defined types cannot be checked for object ID %ld (object "%.*ls") because the internal expression evaluator could not be initialized.
Different message text in SQL 2000: Unable to process table %.*ls because filegroup %.*ls is invalid.

Comments: A clear indication of the fact that error numbers are subject to change and can be reused in later versions.

Error 2594 (SQL 2005) Severity 10Error 2594 (SQL 2000) Severity 16
Cannot process index "%.*ls" of table "%.*ls" because one of its partitions exists on the filegroup "%.*ls", which was not checked.
Different message text in SQL 2000: Invalid index ID (%d) specified.

Comments: An error in SQL 2000 becomes a more verbose warning in SQL 2005.

Error 2595 (SQL 2005) Severity 10Error 2595 (SQL 2000) Severity 16
Cannot process table "%.*ls" because one of the partitions for its base index exists on the filegroup "%.*ls", which was not checked.
Different message text in SQL 2000: Database "%.*ls" must be set to single user mode before executing this statement.

Comments: Another case of "mistaken identity" -i.e. error numbers being reused.

Error 3018 (SQL 2005) Severity 10Error 3018 (SQL 2000) Severity 16
The restart-checkpoint file "%ls" was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified.
Different message text in SQL 2000: There is no interrupted backup or restore operation to restart. Reissue the statement without the RESTART clause.

Comments: When the RESTART option of the RESTORE command is specified incorrectly in SQL 2000 the restoration is interrupted, while in SQL 2005 the restoration continues and the fawlty option is ignored. Also see message 3028.

Error 3028 (SQL 2005) Severity 10Error 3028 (SQL 2000) Severity 16
The restart-checkpoint file "%ls" was corrupt and is being ignored. The RESTORE command will continue from the beginning as if RESTART had not been specified.
Different message text in SQL 2000: Operation checkpoint file is invalid. Could not restart operation. Reissue the statement without the RESTART option.

Comments: When the RESTART option of the RESTORE command is specified incorrectly in SQL 2000 the restoration is interrupted, while in SQL 2005 the restoration continues and the fawlty option is ignored. Also see message 3018.

Error 3175 (SQL 2005) Severity 10Error 3175 (SQL 2000) Severity 16
RESTORE FILEGROUP="%ls" was specified, but not all of its files are present in the backup set. File "%ls" is missing. RESTORE will continue, but if you want all files to be restored, you must restore other backup sets.
Different message text in SQL 2000: The filegroup "%ls" cannot be restored because all of the files are not present in the backup set. File "%ls" is missing.

Comments: Restoring databases in SQL 2005 becomes slightly less of a burdon on the DBA. When restoring filegroups all available files are restored, thanks to a more advanced SQL 2005 architecture.

Error 3281 (SQL 2005) Severity 10Error 3281 (SQL 2000) Severity 16
Released and initiated rewind on "%ls".
(Same message text in SQL 2000.)

Comments: Another "warning" (SQL 2000) becomes a proper warning in SQL 2005.

Error 3738 (SQL 2005) Severity 10Error 3738 (SQL 2000) Severity 16
Deleting database file "%ls".
(Same message text in SQL 2000.)

Comments: Ditto. (See previous.)

Error 5182 (SQL 2005) Severity 10Error 5182 (SQL 2000) Severity 16
New log file "%.*ls" was created.
(Same message text in SQL 2000.)

Comments: And again. (See previous.)

Error 7924 (SQL 2005) Severity 10Error 7924 (SQL 2000) Severity 16
Index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls). FirstIAM %S_PGID. Root %S_PGID. Dpages %I64d.
Different message text in SQL 2000: Index ID %ld. FirstIAM %S_PGID. Root %S_PGID. Dpages %ld.

Comments: Another error in SQL 2000 becomes a more verbose warning in SQL 2005. But does that make it easier to understand?

Error 7925 (SQL 2005) Severity 10Error 7925 (SQL 2000) Severity 16
Index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls). %I64d pages used in %I64d dedicated extents.
Different message text in SQL 2000: Index ID %d. %ld pages used in %ld dedicated extents.

Comments: See previous.

Error 7927 (SQL 2005) Severity 10Error 7927 (SQL 2000) Severity 16
Total number of extents is %I64d.
Different message text in SQL 2000: Total number of extents is %ld.

Comments:

Error 7962 (SQL 2005) Severity 10Error 7962 (SQL 2000) Severity 16
Invalid BATCHID %d specified.
Different message text in SQL 2000: Upgrade requires SQL Server to be started in single user mode. Restart SQL Server with the -m flag.

Comments: Another error number being reused.

Error 8508 (SQL 2005) Severity 10Error 8508 (SQL 2000) Severity 20
QueryInterface failed for "%ls": %ls.
Different message text in SQL 2000: QueryInterface failed for "%hs": %hs.

Comments: Even fatal errors in SQL 2000 can be treated as warnings in SQL 2005.

Error 8625 (SQL 2005) Severity 10Error 8625 (SQL 2000) Severity 16
Warning: The join order has been enforced because a local join hint is used.
(Same message text in SQL 2000.)

Comments: Another "warning" (SQL 2000) becomes a proper warning in SQL 2005.

Error 8975 (SQL 2005) Severity 10Error 8975 (SQL 2000) Severity 16
Indexed view check failed for indexed view %.*ls (object ID %d) due to internal query error %d, severity %d, state %d. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database. However, this view will not be processed further.
Different message text in SQL 2000: Table error: Object ID %d, index ID %d. The child page pointer %S_PGID on PageId %S_PGID, slot %d is not a valid page for this database.

Comments: Another error number being reused.

Error 14213 (SQL 2005) Severity 10Error 14213 (SQL 2000) Severity 16
Core Job Details:
(Same message text in SQL 2000.)

Comments: Job properties used to be errors?

Error 14214 (SQL 2005) Severity 10Error 14214 (SQL 2000) Severity 16
Job Steps:
(Same message text in SQL 2000.)

Comments: Job properties used to be errors!?

Error 14215 (SQL 2005) Severity 10Error 14215 (SQL 2000) Severity 16
Job Schedules:
(Same message text in SQL 2000.)

Comments: Job properties!? Errors!?

Error 14216 (SQL 2005) Severity 10Error 14216 (SQL 2000) Severity 16
Job Target Servers:
(Same message text in SQL 2000.)

Comments: Job properties used to be errors? Really?

Error 14217 (SQL 2005) Severity 10Error 14217 (SQL 2000) Severity 16
SQL Server Warning: "%s" has performed a forced defection of TSX server "%s". Run sp_delete_targetserver at the MSX in order to complete the defection.
(Same message text in SQL 2000.)

Comments:

Error 16934 (SQL 2005) Severity 10Error 16934 (SQL 2000) Severity 16
Optimistic concurrency check failed. The row was modified outside of this cursor.
(Same message text in SQL 2000.)

Comments: More on the subject is available here:

Error 18453 (SQL 2005) Severity 10Error 18453 (SQL 2000) Severity 14
Login succeeded for user "%.*ls". Connection: trusted.%.*ls
Different message text in SQL 2000: Login succeeded for user "%ls". Connection: Trusted.

Comments: Some security-level errors (SQL 2000) are treated as informational messages. As they should have been all along.

Error 18454 (SQL 2005) Severity 10Error 18454 (SQL 2000) Severity 14
Login succeeded for user "%.*ls". Connection: non-trusted.%.*ls
Different message text in SQL 2000: Login succeeded for user "%ls". Connection: Non-Trusted.

Comments: Some security-level errors (SQL 2000) are treated as informational messages. As they should have been all along.

Error 18455 (SQL 2005) Severity 10Error 18455 (SQL 2000) Severity 14
Login succeeded for user "%.*ls".%.*ls
Different message text in SQL 2000: Login succeeded for user "%ls".

Comments: Some security-level errors (SQL 2000) are treated as informational messages. As they should have been all along.

ML

No comments: