master..sysmessages

Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:

>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:

> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:

> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>