What is the proper way to create and maintain custom error messages in SQL 2008r2?

Experts,

SQL 2008r2, I am trying to use best practice; using the sql error system and adding my custom errors.

Where is sys.messages table, containing all the error messages in the SSMS directory tree?  Or is an sproc the only way to see them; SELECT * FROM sys.messages WHERE language_id = 1033

RAISERROR (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms178592.aspx
see B. Creating an ad hoc message in sys.messages

I do not think I should create and drop messages in every SPROC as the ms example above suggests.  I think I should add them to sys.message with @msgnum 50001 or higher, in one place, let them persist, and then use them over and over again.

The only way I see to do this; is an SPROC with each custom message added, sp_addmessage... if it does not exisit.  Run this sproc every time the sqlserver is run or when a new message is added.  Unless the messages already persist then only when a new message is added?

I think my ideas are wrong but I did not know how to ask, hopefully this describes my question.

What is the proper way to create and maintain custom error messages in SQL 2008r2?

Kind Regards
Sam
SamCashAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
I do not think I should create and drop messages in every SPROC as the ms example above suggests
Not really a suggestion but an example how to do it if you're looking for an ad hoc solution.

I think I should add them to sys.message with @msgnum 50001 or higher, in one place, let them persist, and then use them over and over again.
That's usually the most used solution.

The only way I see to do this; is an SPROC with each custom message added, sp_addmessage... if it does not exisit.  Run this sproc every time the sqlserver is run or when a new message is added.  Unless the messages already persist then only when a new message is added?
It can be but why to check every time if error exists? My suggestion is to build a script where you add all necessary user messages and have it running once to store the custom error messages and then you won't need to think more about it. If in the future you need to create a new database you'll just need to remember to run the same script against the new database to have all custom messages available.
0
 
Máté FarkasConnect With a Mentor Database Developer and AdministratorCommented:
Yes, you can register a new error message into sys.messages table with sp_addmessage.
Then you can call the message number when you want to raise the error.
0
 
SamCashAuthor Commented:
EE,

Thanks much!  I feel a little foolish now that I see this simple solution!  Thanks again.

I am implementing but I can not find where the usermessages are storred.  https://msdn.microsoft.com/en-us/library/ms179503.aspx says 'sys.sysusermsg' but it is not under master.tables.system tables or master.views.system views (where I find sys.sysmessages) hmm?  I know it is saved somewhere because I sp_addmessage @msgnum = 60000, ... and when I RAISEERROR(6000... it returns the correct values.  I have tried SELECT * FROM [master].[sys].[sysusermsg] which returns *Invalid object name 'master.sys.sysusermsg'.*

So how do I get the list of 'user messages'?  Or view them in SSMS?

Best Regards
Sam
0
 
SamCashAuthor Commented:
EE,

I found it.  They are added to sys.sysmessages.  my SELECT was for WHERE error = 6,000 in stead of 60,000.  Now I am off to the races!

Many Thanks
Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.