Warnings once, errors today

In the previous post we took a look at some warnings in SQL 2005 that are considered as errors in SQL 2000. There are also a few opposite cases. Not serious breaking changes, but something to consider.

Error 21 (SQL 2005) Severity 20	Error 21 (SQL 2000) Severity 10
Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator. (Same message text in SQL 2000.) Comments: A sinister warning in SQL 2000 becomes a fatal error in SQL 2005. Nonetheless, this remains an error related to the current task. It remains unlikely that the database has been damaged.

Error 2547 (SQL 2005) Severity 16	Error 2547 (SQL 2000) Severity 10
Unable to process object ID %ld (object "%.*ls") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references. Different message text in SQL 2000: Performing second pass of index checks. Comments: Clearly not the same error in the 2000 and the 2005 version. Obviously, the error numbers are subject to change and can be reused.

Error 2574 (SQL 2005) Severity 16	Error 2574 (SQL 2000) Severity 10
Table error: Page %S_PGID is empty in object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls). This is not permitted at level %d of the B-tree. Different message text in SQL 2000: Object ID %d, index ID %d: Page %S_PGID is empty. This is not permitted at level %d of the B-tree. Comments: A warning of failure that obviously needs more serious attention. SQL 2000 Books Online... SQL 2005 Books Online...

Error 2759 (SQL 2005) Severity 16	Error 2759 (SQL 2000) Severity 0
CREATE SCHEMA failed due to previous errors. (Same message text in SQL 2000.) Comments: There is no CREATE SCHEMA statement in SQL 2000, but the error message suggest there is. A blast from the future? :)

Error 3266 (SQL 2005) Severity 16	Error 3266 (SQL 2000) Severity 10
The backup data at the end of "%ls" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Different message text in SQL 2000: The backup data in "%ls" is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. Comments: A warning about possible inconcistencies in the backup sets. Declared as more severe in SQL 2005 with a more verbose message.

Error 3315 (SQL 2005) Severity 21	Error 3315 (SQL 2000) Severity 10
During rollback, the following process did not hold an expected lock: process %d with mode %d at level %d for row %S_RID in database "%.*ls" under transaction %S_XID. Restore a backup of the database, or repair the database. Different message text in SQL 2000: During rollback, process %d was expected to hold mode %d lock at level %d for row %S_RID in database "%.*ls" under transaction %S_XID. Comments: A warning about inconsistencies in a transaction rollback becomes a fatal error. Indicates a problem that affects all tasks in the current database, but it's unlikely that the database itself has been damaged.

Error 3623 (SQL 2005) Severity 16	Error 3622 (SQL 2000) Severity 10
A domain error occurred. (Same message text in SQL 2000.) Different Error Number! Comments: The error number has been changed.

Error 4214 (SQL 2005) Severity 16	Error 4214 (SQL 2000) Severity 10
BACKUP LOG cannot be performed because there is no current database backup. Different message text in SQL 2000: There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. Comments:

Error 4506 (SQL 2005) Severity 16	Error 4506 (SQL 2000) Severity 10
Column names in each view or function must be unique. Column name "%.*ls" in view or function "%.*ls" is specified more than once. (Same message text in SQL 2000.) Comments: Although the severity level in SQL 2000 is declared as 10 (informational) the actual severity level displayed when the errror occurs is 16 (error that can be corrected by the user). Obviously a fix in SQL 2005.

Error 5174 (SQL 2005) Severity 16	Error 5174 (SQL 2000) Severity 10
Each file size must be greater than or equal to 512 KB. (Same message text in SQL 2000.) Comments:

Error 7908 (SQL 2005) Severity 16	Error 7908 (SQL 2000) Severity 10
Table error: The file "%.*ls" in the partition ID %I64d is not a valid FileStream file. Different message text in SQL 2000: The table "%.*ls" was created with the NO_LOG option. Comments: Another case of the error ID being reused for a different error message.

Error 8984 (SQL 2005) Severity 16	Error 8984 (SQL 2000) Severity 10
Table error: Object ID %d, index ID %d, partition ID %I64d. A row should be on partition number %d but was found in partition number %d. Possible extra or invalid keys for: Different message text in SQL 2000: Object ID %d, index ID %d. Allocations for %S_PGID. IAM %S_PGID, extents %d, used pages %d, mixed pages %d. Comments:

Error 8988 (SQL 2005) Severity 16	Error 8988 (SQL 2000) Severity 10
Row (%d:%d:%d) identified by (%ls). Different message text in SQL 2000: The schema for database "%ls" is changing. May find spurious allocation problems due to schema changes in progress. Comments: Another case of the error ID being reused for a different error message.

Error 13164 (SQL 2005) Severity 16	Error 13009 (SQL 2000) Severity 10
SEND (Same message text in SQL 2000.) Different Error Number! Comments: Different ID numbers.

Error 14106 (SQL 2005) Severity 16	Error 14106 (SQL 2000) Severity 10
Distribution retention periods must be greater than or equal to 0. Different message text in SQL 2000: Distribution retention periods must be greater than 0. Comments:

Error 15425 (SQL 2005) Severity 16	Error 15425 (SQL 2000) Severity 0
No server principal is defined for sid "%.*ls". Different message text in SQL 2000: New application role added. Comments: Another case of the error ID being reused for a different error message.

Error 15482 (SQL 2005) Severity 16	Error 15482 (SQL 2000) Severity 0
Cannot change the owner of a table that has an indexed view. Different message text in SQL 2000: Could not deny login access to "%s". Comments: Another case of the error ID being reused for a different error message.

Error 16947 (SQL 2005) Severity 16	Error 16947 (SQL 2000) Severity 10
No rows were updated or deleted. (Same message text in SQL 2000.) Comments: Another warning becomes an error. Related to updating an application to SQL Native Client from MDAC: "When SQL Native Client is connected to SQL Server 2005, server error 16947 is returned as a SQL_ERROR. This error occurs when a positioned update or delete fails to update or delete a row. With SQL Server 2000 and earlier versions, and with MDAC when connecting to any version of SQL Server, server error 16947 is returned as a warning (SQL_SUCCESS_WITH_INFO)." SQL 2005 Books Online...

Error 17809 (SQL 2005) Severity 20	Error 17809 (SQL 2000) Severity 10
Could not connect because the maximum number of "%ld" user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed.%.*ls Different message text in SQL 2000: Could not connect. The maximum number of "%1!ld!" configured user connections are already connected. The system administrator can change the maximum to a higher value using sp_configure. Comments: A more verbose error in SQL 2005 compared to the warning in SQL 2000.

Error 20053 (SQL 2005) Severity 16	Error 20053 (SQL 2000) Severity 10
An article with a different %s value already exists for object "%s". (Same message text in SQL 2000.) Comments:

Error 21410 (SQL 2005) Severity 16	Error 21410 (SQL 2000) Severity 10
Snapshot Agent startup message. (Same message text in SQL 2000.) Comments: Startup messages more severe in SQL 2005?

Error 21411 (SQL 2005) Severity 16	Error 21411 (SQL 2000) Severity 10
Distribution Agent startup message. (Same message text in SQL 2000.) Comments: Startup messages more severe in SQL 2005?

Error 21412 (SQL 2005) Severity 16	Error 21412 (SQL 2000) Severity 10
Merge Agent startup message. (Same message text in SQL 2000.) Comments: Startup messages more severe in SQL 2005?

Error 21422 (SQL 2005) Severity 16	Error 21422 (SQL 2000) Severity 10
Queue Reader Agent startup message. (Same message text in SQL 2000.) Comments: Startup messages more severe in SQL 2005?

Error 21423 (SQL 2005) Severity 16	Error 21423 (SQL 2000) Severity 10
Either the publication "%s" does not exist or you do not have sufficient permissions to access it. Ensure that the publication exists and that the account under which the Merge Agent connects to the Publisher is included in the publication access list (PAL). Different message text in SQL 2000: You do not have sufficient privileges to view the publication information. Your administrator may need to fix the PAL role on the publisher for publication "%s". Comments: Another more verbose error in SQL 2005 compared to the warning in SQL 2000.

Error 21424 (SQL 2005) Severity 16	Error 21424 (SQL 2000) Severity 10
The @publisher parameter must be NULL for SQL Server publishers. Different message text in SQL 2000: Every SQL Server Subscriber of publication "%s" must be version %s or higher in order for compensation for errors to be turned off for its subscription. Comments: Another case of the error ID being reused for a different error message.

Error 21426 (SQL 2005) Severity 16	Error 21426 (SQL 2000) Severity 10
No shared agent subscription exists for publication "%s" and the subscriber/subscriber database pair "%s"/"%s". Different message text in SQL 2000: The generation for a row that is being inserted/updated/deleted has already been localized. Please retry the merge synchronization process. Comments: Another case of the error ID being reused for a different error message.

ML

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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 3738 (SQL 2000) Severity 16
Deleting database file "%ls". (Same message text in SQL 2000.) Comments: Ditto. (See previous.)

Error 5182 (SQL 2005) Severity 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 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 10	Error 14215 (SQL 2000) Severity 16
Job Schedules: (Same message text in SQL 2000.) Comments: Job properties!? Errors!?

Error 14216 (SQL 2005) Severity 10	Error 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 10	Error 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 10	Error 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: Optimistic concurrency (with essential corrections); Snapshot transaction isolation – optimistic concurrency made easy?.

Error 18453 (SQL 2005) Severity 10	Error 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 10	Error 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 10	Error 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