• Adam Thurgar

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

USE master;

GO

EXEC sp_addmessage 50001, 16,

N'Database doesn''t exist';

GO

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.


15 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t