[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2016-10-13
4
Medium Priority
?
105 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 13

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 52

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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