Tuesday, 18 October 2011

RAISERROR raises error - not expected behavior on SQL 2005

Description:
We all expect that RAISERROR raises error, but not like this one:

Script:
RAISERROR ('Error', 16, 255 );

Output:
Invalid value 255 for state. Valid range is from 0 to 127

Value for state can be from 0 to 255, according to BOL:
"state
Is an integer from 0 through 255. Negative values or values larger than 255 generate an error.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors."

Solution:
- check SQL Server version (@@VERSION) and if it's 9.0.1399 (Microsoft Sql Server 2005 RTM) it's really time to apply some service pack. SQL 2005 RTM was released on November 7, 2005! ;)

Links:
- http://sqlserverbuilds.blogspot.com/ (list of SQL Server builds)
- http://www.microsoft.com/download/en/details.aspx?id=7218 (Download Microsoft SQL Server 2005 Service Pack 4 RTM)
- http://support.microsoft.com/kb/2507769/ (Cumulative update package 3 for SQL Server 2005 Service Pack 4)

Appendix:
- error in German language version:"Der Wert 255 für den Status ist ungültig. Der gültige Bereich liegt zwischen 1 und 127."

No comments:

Post a Comment