Matrix Report - Subtotals

Good moring,
I am working on my first matrix report and trying to add in a total for a
group and rows
Table is as follows:
Account Manager Job Number Jan Sales Feb Sales
Peter J54455 598 60
Peter J65559 500 70
David J76666 400 80
Jane J74445 700 90
I would like to have:
1) a subtotal row when the Account Manager changes.
2) a total for each row.
Example:
Account Manager Job Number Jan Sales Feb Sales Total
Peter J54455 500 60
560
Peter J65559 500 70
570
Total Peter 1000 130
1130
David J76666 400 80
480
Total David 400 80
480
Jane J74445 700 90
790
Total Jane 700 90
790
I know this is inherent in the matrix report, however I have spent hours
trying to figure out and still cannot determine how to achieve both of these
2 things (simple as they may be).
Can anyone help?
Thanks JamesAs an amendment,
I have discovered how to add row total so ignore that part.
As to the subtotal on the Account Manager I still have an issue with that.
I tried right clicking on the Account Manager name and choose subtotal.
However this puts a grand total for all account Managers, not a subtotal for
each account manager. Any ideas'?

matching saved searches to newly inserted record

Hello SQL wizards,
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job sers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job sers with matching searches
right now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job ser. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>

'master..xp_smtp_SendMail'.

Hi,
What is the SQL Server 2005 version of:
'master..xp_smtp_SendMail'.
Apparently, this sp does not exist in 2005 - and causes our job to
fail.
Thank youxp_smtp_sendmail is from a third party provider (kind of) at
http://www.sqldev.net/xp/xpsmtp.htm
So no...you won't find that extended stored procedure built
into SQL Server 2005. The closest thing in 2005 is using DB
Mail and using sp_send_dbmail.
-Sue
On 6 Sep 2006 14:19:17 -0700, tootsuite@.gmail.com wrote:
>Hi,
>What is the SQL Server 2005 version of:
>'master..xp_smtp_SendMail'.
>Apparently, this sp does not exist in 2005 - and causes our job to
>fail.
>Thank you|||<DIV><tootsuite@.gmail.com> wrote in message
news:1157577557.082293.219110@.m73g2000cwd.googlegroups.com...</DIV>> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
Database Mail
http://msdn2.microsoft.com/en-us/library/ms175887.aspx
sp_send_dbmail
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
David|||If you want to use master..xp_smtp_SendMail in SQL 2005, you'll have to
recreate it from scratch as this is a custom built sp by someone on
SQLDev.Net. You can use the 2000 .dlls (I did it myself) but you'll have to
save them in C:\Program Files\Microsoft SQL Server\90\DTS\Binn for it to work.
Big note here. If you switch from this to DBMail, read the instructions for
DBMail VERY CAREFULLY. You'll have to make changes to the variable names.
I.E., instead of @.TO, DBMail uses @.Recipients. If you don't change
everything over, your job will still fail.
Hope this helps.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"tootsuite@.gmail.com" wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>|||tootsuite@.gmail.com wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
That is a third-party utility that allows SQL 2000 to send mail through
an SMTP server. It's no longer needed in SQL 2005, as the built-in
DBMail capability can do the same thing. Be sure to read the
documentation on DBMail.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

'master..xp_smtp_SendMail'.

Hi,
What is the SQL Server 2005 version of:
'master..xp_smtp_SendMail'.
Apparently, this sp does not exist in 2005 - and causes our job to
fail.
Thank youxp_smtp_sendmail is from a third party provider (kind of) at
http://www.sqldev.net/xp/xpsmtp.htm
So no...you won't find that extended stored procedure built
into SQL Server 2005. The closest thing in 2005 is using DB
Mail and using sp_send_dbmail.
-Sue
On 6 Sep 2006 14:19:17 -0700, tootsuite@.gmail.com wrote:

>Hi,
>What is the SQL Server 2005 version of:
>'master..xp_smtp_SendMail'.
>Apparently, this sp does not exist in 2005 - and causes our job to
>fail.
>Thank you|||<DIV><tootsuite@.gmail.com> wrote in message
news:1157577557.082293.219110@.m73g2000cwd.googlegroups.com...</DIV>> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
Database Mail
http://msdn2.microsoft.com/en-us/library/ms175887.aspx
sp_send_dbmail
http://msdn2.microsoft.com/en-us/library/ms190307.aspx
David|||If you want to use master..xp_smtp_SendMail in SQL 2005, you'll have to
recreate it from scratch as this is a custom built sp by someone on
SQLDev.Net. You can use the 2000 .dlls (I did it myself) but you'll have to
save them in C:\Program Files\Microsoft SQL Server\90\DTS\Binn for it to wor
k.
Big note here. If you switch from this to DBMail, read the instructions for
DBMail VERY CAREFULLY. You'll have to make changes to the variable names.
I.E., instead of @.TO, DBMail uses @.Recipients. If you don't change
everything over, your job will still fail.
Hope this helps.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"tootsuite@.gmail.com" wrote:

> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>|||tootsuite@.gmail.com wrote:
> Hi,
> What is the SQL Server 2005 version of:
> 'master..xp_smtp_SendMail'.
> Apparently, this sp does not exist in 2005 - and causes our job to
> fail.
> Thank you
>
That is a third-party utility that allows SQL 2000 to send mail through
an SMTP server. It's no longer needed in SQL 2005, as the built-in
DBMail capability can do the same thing. Be sure to read the
documentation on DBMail.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Master..sysservers

Hey all,
My question is: Is it possible for the master..sysservers
table to be empty? I am running a job which requires
connection to all my other sql servers but i am unable to
because : Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'XXXX' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.
This job usto work perfectly. This server is a production
server and rebooting it might not be the best option. Any
one got any ideas?Claudy,
I don't believe the table can be empty. Though I can not find a document
about it, the table should at least contain the local server with id = 0. I
use this table for some of my tasks and have always been assuming that the
local server is there, and it has been working.
As of your problem, the error message does not say that the table is empty.
It says it could not find the remote server. If the access of the remote
server is called for by an application, you may have the linked server
removed. If the application is cycling through the table for linked
servers, the linked server may not be running or the network is not
functioning. There might be other problems, so be sure checking beyond the
sysserver table.
Quentin
"Claudy" <alphaseusa@.hotmail.com> wrote in message
news:068a01c396e8$dc2de440$a501280a@.phx.gbl...
> Hey all,
> My question is: Is it possible for the master..sysservers
> table to be empty? I am running a job which requires
> connection to all my other sql servers but i am unable to
> because : Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'XXXX' in sysservers. Execute
> sp_addlinkedserver to add the server to sysservers.
> This job usto work perfectly. This server is a production
> server and rebooting it might not be the best option. Any
> one got any ideas?

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 will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
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:

>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 will
>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 which
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 which
>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 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:

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:
>

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 will
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 will
>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 which
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:
> >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 will
> >be fired?
> >
> >What table stores this information, so that I can run the query against it?
> >
> >Thanks
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>No I do not want to create alerts, I just want to find out where and in which
>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:
>> >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 will
>> >be fired?
>> >
>> >What table stores this information, so that I can run the query against it?
>> >
>> >Thanks
>>|||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:
> >No I do not want to create alerts, I just want to find out where and in which
> >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:
> >>
> >> >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 will
> >> >be fired?
> >> >
> >> >What table stores this information, so that I can run the query against it?
> >> >
> >> >Thanks
> >>
> >>
>|||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:
>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:
>> >No I do not want to create alerts, I just want to find out where and in which
>> >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:
>> >>
>> >> >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 will
>> >> >be fired?
>> >> >
>> >> >What table stores this information, so that I can run the query against it?
>> >> >
>> >> >Thanks
>> >>
>> >>
>>