RAISERROR issue since migrating to SQL Server 2012

Question

I am working on some problems with website and database after moving server. The database was previously SQL Server Express 2005, but now runs on SQL Server Express 2012.

Problems relate to the RAISERROR team and the changes in 2012. I looked through the documentation for the new syntax, but I don’t know how I can transfer both the error number and the message to the website.

Some examples of RAISERROR commands in stored procedures and triggers:

RAISERROR 50000 'Member with same Email address already exists.'

RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblBrand''.'

RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblFragranceHouse''.'

I changed some to the new syntax, but not sure if I did it right or not. I understand that if I just pass the error text, it will skip the error number 50000. But I'm not sure what to do with the other error codes.

Any tips on how to translate these teams for 2012?

Answer 1

SQL 2012 does not support an undocumented version of Raiserror. Supported syntax

RAISERROR(@Message,Serverity,state); 

- @Message may be a message identifier, but it must exist in sysmessages, so if you want to send custom messages, I think you should add them sysmessages

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Or another option is to use THROW

http://technet.microsoft.com/en-us/library/ee677615.aspx

Answer 2

You can replace the code as follows:

SQL 2008: raiserror 55030 'text error'

SQL 2012: raiserror ('text error', 16, 1)

You cannot set the error number, by default it will be 50,000, but you can get the same error level, red text, etc.

Hope this helps.