?
Solved

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

Posted on 2016-10-13
4
Medium Priority
?
112 Views
Last Modified: 2016-10-14
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
0
Comment
Question by:SamCash
  • 2
4 Comments
 
LVL 14

Assisted Solution

by:Máté Farkas
Máté Farkas earned 200 total points
ID: 41843203
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
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 1800 total points
ID: 41843473
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
 

Author Comment

by:SamCash
ID: 41844124
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
 

Author Comment

by:SamCash
ID: 41844175
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question