Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

1 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.