Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change Error Message

Posted on 2006-10-19
3
Medium Priority
?
2,891 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 750 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 750 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

916 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