Solved

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

Posted on 2016-10-13
4
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:Máté Farkas
Máté Farkas earned 50 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 50

Accepted Solution

by:
Vitor Montalvão earned 450 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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