Solved

Change Error Message

Posted on 2006-10-19
3
2,881 Views
Last Modified: 2007-12-19
Hi All,
I would like to customize some error messages on my SQL box so that when i'm doing maintenance and i disable access the users don't panic.

one error is: (when I restrict the db)
Connection failed:
SQLState: '08004'
SQL Server Error: 4060
Server rejected the connection; Access to selected database has been denied

I used the following code:

use master
exec sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
update dbo.sysmessages
set description = 'Server is currently unavailable...don't panic!'
where error = 4060
exec sp_configure 'allow updates',0
RECONFIGURE

But I still get the same message.
Any ideas? My next guess is it can't be done.
0
Comment
Question by:nito8300
3 Comments
 
LVL 20

Accepted Solution

by:
Sirees earned 250 total points
ID: 17768881
The SQL Server error messages are stored in the sysmessages system table.
However be aware that sysmessages is stored in the master database, and
affects ALL the databases, so if you have multiple applications using
multiple databases on one server, changing sysmessages will affect ALL the
applications.


Also be aware that sysmessages contains place holders for variable
information ... things like database names, table names, etc. Unless you can
structure your customized error messages to use the place holders in the
same sequence as they appear in sysmessages, you might be out of luck.

So I think, its a good idea not to mess up with system tables.
0
 
LVL 15

Assisted Solution

by:MNelson831
MNelson831 earned 250 total points
ID: 17768982
Probably much better to handle custom errors like this:

http://www.sqlteam.com/item.asp?ItemID=2841
0
 
LVL 9

Author Comment

by:nito8300
ID: 17774310
MNelson,
Thanks for the post. Most of the user access the SQL Server thru ODBC - that's what I'm trying to customized.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 extract information from SQL Server on Database, Connection and Server properties

828 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