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."

Tuesday 4 October 2011

Kids don't delete LDF files!

If you have, and no backup etc, read following articles:

try with "CREATE DATABASE name ON fileMDB FOR ATTACH_REBUILD_LOG"
http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/d48bb79b-0403-46d8-abb9-11fff40e7e72/

in case of opened transactions (error:" The protocol can not be rebuilt because there were open on shutdown of database transactions / users that no probe was used for the database or the database was write protected. This error can occur if the transaction log file was manually deleted or lost due to hardware failure or external failure.")

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114812
http://stackoverflow.com/questions/773059/how-to-recover-database-from-mdf-in-sql-server-2005

Uncommited transacions will appear in recovered databases.