RAISERROR upgrade issue
I was asked to investigate an issue with the upgrade of a legacy application from SQL Server 2005 to SQL Server 2012.
After running profiler we found that it was stopping on the following statement.
if NOT EXISTS(select * from master..sysdatabases where name = 'XYZ_1234') RAISERROR 50000 'Database doesn''t exist'
The issue was with the RAISERROR statement as the syntax changed in SQL Server 2012.
The syntax now is:
RAISERROR (50001,1,1, 'Database doesn''t exist')
Note that the error number now has to be greater than 50000 and you need to add that message.
Error 50001, severity 1, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.
If we go and add that message
EXEC sp_addmessage 50001, 16,
N'Database doesn''t exist';
Then it works.
Unfortunately we couldn't change the code, there is no real fix to this issue.
All they could do was upgrade up to SQL Server 2008R2.