a message to notify user when specific is offline

when specific what is offline?

specific DB.

is the user on a webpage?  what kind of message are you talking about?

if the user is on a webpage, you could use:

try{

connect to db

}catch(exception){

print error message

}

or something of that sort, depending on what language you are using.

I want to use SQL server internal alert, not sure how to set it up?

So you want SQL Server to monitor the status of its databases and alert users when/if they do what?

Right, the purpose is to monitor the DB status. When the DB is offline for some reason I could receive an alert message.

One way would be to create a separate app that makes/closes a connection to your database every minute, and maybe run a simple query.  If it performs without errors, do nothing.  If an error is generated, create an alert message (email to a pager, email to a couple of people, etc.).

Also, place it on startup of a server to insure that it's running 24x7-ish, assuming the Server people like you and are willing to do this for you.

We did this with VB all the time to monitor various FTP apps.

SELECT DATABASEPROPERTYEX('Northwind', 'Status')


This would return a variety of possibilities:
ONLINE = database is available for query
OFFLINE = database was explicitly taken offline
RESTORING = database is being restored
RECOVERING = database is recovering and not yet ready for queries
SUSPECT = database cannot be recovered

Build your alerting around this...

I saw your other post at:  https://www.experts-exchange.com/questions/21352204/Questions-on-ALERT-on-sql-server-2000.html

if the error code isn't working, its probably because the error is not firing.  check the errorlog in the /log folder of the sql server installation.  See if an error is generated.  If an error is not generated, an alternate method would be to use an alert of type 'sql server performance condition alert'.

Set the object to 'SQLServer: General Statistics'

Set the counter to 'User Connections'

Set the alert if counter to 'Becomes equal to'

Set the value to 0.

This will only work if you regularly have people connected to the database.  if nobody is connected, something is probably wrong with your database connection, and you will get an alert fired.  Your other post said that the email is working so I won't go into that.

curlypinhead

Thanks for you guys' reply.

You are right, I try to excute 'user DB001' to tirgger the error.

I check the sql server log, I can see the error was fired.
************************
2005-04-05 12:00:00.81 spid58    Error: 942, Severity: 14, State: 4
2005-04-05 12:00:00.81 spid58    Database 'DB001' cannot be opened because it is offline..
************************

But from 'Enterprise Manager --> Management --> Alters', the alert status is 'Never Occurred', I am sure the alert was created and setup correctly (Type/Error Number/DB Name/Enable). It just confuse me, why????

My guess is that you have not started sql server agent.

right click on sql server agent and click on start.

This should fix it if it is not already started.

curlypinhead

the sql server agent is started.

i tried doing the same thing on my machine and it worked.  ARe you sure you have the right error number in the alert (the same one that is in the event log)?

Really? Let me list the detail how I setup and test the ALERT:

1. Create a new alert from 'Enterprise Manager --> Management --> Alert',

Name: Test DB Offline
Type: Sql Server Event Alert
Enable: checked
Error Number: 942
Database Name: DB001
Error Message Contain this text: offline

2. I take the DB001 offline, and excute 'use DB001'.

3. I can see the error from sql server event log.

4. But status for 'Test DB Offline' is 'Never Occurred' even after refreshing the view.

Actually I tried it on three different computers, my working station and two servers.

It didn't work on all of them.

I use 'all database' in the alert. It works!!! Thanks a lot.