Solved

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

Posted on 2016-10-13
4
43 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 11

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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now