CODEWITHSUNDEEP
sql-serversql-server-2012
johna
johna

Reputation: 10772

RAISERROR issue since migration to SQL Server 2012

I am working on some issues with a website and database after a move of server. The database was previously SQL Server Express 2005 but is now running on SQL Server Express 2012.

The issues relate to the RAISERROR command and changes in 2012. I have looked at the documentation for the new syntax but am unsure on how I can pass both the error number and the message to the website.

Some examples of the RAISERROR commands in stored procedures and triggers are:

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 have changed some to the new syntax but am unsure if I have done this correctly or not. I understand that if I just pass the error text it passes error number 50000. But I'm not sure what to do about the other error codes.

Any advice on how to translate these commands to 2012?

Upvotes: 8

Views: 28309

Answers (2)

Victor Schiavi
Victor Schiavi

Reputation: 91

You can replace the code as follows:

SQL 2008: raiserror 55030 'text error'

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

You will not be able to set the error number, it will be 50000 by default, but you can get the same error level, red text and so on.

Hope it helps.

Upvotes: 8

Satheesh Variath
Satheesh Variath

Reputation: 680

SQL 2012 does not support the undocumented version of Raiserror The supported syntax is

RAISERROR(@Message,Serverity,state);

-- @Message could be message id , but it should 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 the other option is to use THROW

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

Upvotes: 9

Related Questions