Raising an error with a system message_id in SQLCLR

Question

If call CREATE DATABASE foo twice I get the error message:

Msg 1801, Level 16, State 3, Line 1
Database 'dropme' already exists. Choose a different database name.

The error message is listed in sys.messages:

SELECT * FROM sys.messages WHERE message_id = 1801 AND language_id = 1033

I have a SQLCLR procedure that basically does a CREATE DATABASE FOR ATTACH. It knows the name of the created database at the beginning, but does a lot of work before attaching it. Therefore, I'd like to make sure the database doesn't exist before proceeding and give that error if it does.

If I try to use RAISERROR(1801, 16, 1) on that message it will not let me:

Msg 2732, Level 16, State 1, Line 1
Error number 1801 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.

Do I have any option other than raising the error and having the msg_id being 50000?

Answer 1

Do I have any option other than raising the error and having the msg_id being 50000?

You can define custom messages that have an id > 50000, or you can choose to not define a message and use id = 50000. Internal error numbers can't be raised by users because allowing that would open up all sorts of avenues of abuse...

How you implement this really depends on your environment, so I'm not sure we can give a recommendation for what to do in this case.

One alternative to the built-in methods (I hate that custom messages are global within an instance) which is a bit out-of-the-box, and may or may not be appropriate, is to raise your errors with the default message id of 50000, and return a custom error code in the message itself (with or without an actual error message). This will let you localize the message outside the context of SQL Server, while still retaining the ability to capture specific types of messages. (Note: best done if you have some kind of client-side framework that handles the errors.) The disadvantage is that this kind of behaviour isn't usually expected by callers.