|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to create a single reusable alert in SQL Server Agent?I have numerous SQL scripts that run as jobs under SQL Server Agent. I've been playing with the alert system and got it working using a test script. However, it seems that it's not possible to create a single alert and associate it with every script. When right-clicking on a job under SQL Server Agent in SSMS and selecting the 'Alerts' tab and the 'Add...' option, there is no way to select an alert that I have already created. So, this forces me to create a new alert, which basically does exactly the same thing (raises an alert when the job message contains the word 'error') as one that I created previously, meaning the only difference will be the alert name. The problem is that I have over a two dozen scripts that I want to set an alert on. Am I missing something or are multiple duplicate scripts the right way to go? Thanks, Frank. I might be missing something here, but IMO you seem to be looking at this the wrong way...
When an alert fires, you can specify to execute a job. This has been the case since 6.0. IMO, the alert folder for the job is a bit confusing, because it implies that you associate an alert with the job. Where it actually is the other way around, you associate a job with the alert (fire this job when the alert happens). So, what you want to do, being able to re-use the alert from several jobs, would actually mean that you want to fire several jobs when the alert fires. And, to the best of my knowledge, this isn't possible. At least not directly. I guess you could fire a job in which you cal sp_start_job several time so that this job in turn fires other jobs... -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <francis.mo***@gmail.com> wrote in message news:801cbe16-c882-4f96-bfb6-7dec556ddd50@j44g2000hsj.googlegroups.com... > Hi, > > I have numerous SQL scripts that run as jobs under SQL Server Agent. > I've been playing with the alert system and got it working using a > test script. > However, it seems that it's not possible to create a single alert and > associate it with every script. > When right-clicking on a job under SQL Server Agent in SSMS and > selecting the 'Alerts' tab and the 'Add...' option, there is no way > to > select an alert that I have already created. So, this forces me to > create a new alert, which basically does exactly the same thing > (raises an alert when the job message contains the word 'error') as > one that I created previously, meaning the only difference will be > the > alert name. > The problem is that I have over a two dozen scripts that I want to > set > an alert on. > Am I missing something or are multiple duplicate scripts the right > way > to go? > > Thanks, > Frank. You can also write a VBScript that sends email alerts using SMTP. This way
you can pass parameters like probably the names of the jobs and call this script inside your SQL job. This is what I did for log shipping alerts for a clustered SQL Server 2000 since IMAP is not supported in this scenario <francis.mo***@gmail.com> wrote in message Show quote news:801cbe16-c882-4f96-bfb6-7dec556ddd50@j44g2000hsj.googlegroups.com... > Hi, > > I have numerous SQL scripts that run as jobs under SQL Server Agent. > I've been playing with the alert system and got it working using a > test script. > However, it seems that it's not possible to create a single alert and > associate it with every script. > When right-clicking on a job under SQL Server Agent in SSMS and > selecting the 'Alerts' tab and the 'Add...' option, there is no way > to > select an alert that I have already created. So, this forces me to > create a new alert, which basically does exactly the same thing > (raises an alert when the job message contains the word 'error') as > one that I created previously, meaning the only difference will be > the > alert name. > The problem is that I have over a two dozen scripts that I want to > set > an alert on. > Am I missing something or are multiple duplicate scripts the right > way > to go? > > Thanks, > Frank. Tibor,
Thanks for the response. I'm still confused, although I can see what you are saying. > When an alert fires, you can specify to execute a job. I can see that now, but what I want to do is execute a job using SQLServer Agent and then if that job fails, I want it to trigger an alert that emails me the fact that the job has failed. I've had a few occasions recently where batch jobs have failed and it hasn't been noticed until a day or so later. In SSMS, when selecting a job under SQL Server Agent | Jobs and right- clicking, you can get to a Properties dialog for that job. On that dialog there are some tabs, General, Steps, Schedules, Alerts, Notifications and Targets. Selecting Alerts give you a page with a button titled 'Add...' at the bottom of it. Clicking 'Add...' brings up a 'New Alert' dialog. I am expecting to enter details in there to create an alert for that job. It allows me to do this once, but when I go to the next job, to create a similar alert for that job, I can neither pick the alert that I just created, nor can I create another alert similar to the pervious one as it says that one like this already exists. What I would really like to do is create one alert (i.e. when message contains text like 'error') and then select that to be the alert that I can use for that job and possibly others as well. However, it doesn't seem to work like that. So, I'm still confused. So, is it possible to use alerts as I want to in SQL Server 2005? And if not, how would you let an operator know that a batch job has failed (preferably by email)? Many thanks for your time, Frank. > In SSMS, when selecting a job under SQL Server Agent | Jobs and right- No, don't select Alerts. Select the "Notifications" page. This is what you want to do (notify an > clicking, you can get to a Properties dialog for that job. > On that dialog there are some tabs, General, Steps, Schedules, Alerts, > Notifications and Targets. > Selecting Alerts give you a page with a button titled 'Add...' at the > bottom of it. operator after job executed). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <francis.mo***@gmail.com> wrote in message news:504870de-6e30-4424-ad6c-f532a484a5fe@t47g2000hsc.googlegroups.com... > Tibor, > > Thanks for the response. > > I'm still confused, although I can see what you are saying. > >> When an alert fires, you can specify to execute a job. > > I can see that now, but what I want to do is execute a job using SQL > Server Agent and then if that job fails, I want it to trigger an alert > that emails me the fact that the job has failed. I've had a few > occasions recently where batch jobs have failed and it hasn't been > noticed until a day or so later. > > In SSMS, when selecting a job under SQL Server Agent | Jobs and right- > clicking, you can get to a Properties dialog for that job. > On that dialog there are some tabs, General, Steps, Schedules, Alerts, > Notifications and Targets. > Selecting Alerts give you a page with a button titled 'Add...' at the > bottom of it. > Clicking 'Add...' brings up a 'New Alert' dialog. > I am expecting to enter details in there to create an alert for that > job. > It allows me to do this once, but when I go to the next job, to create > a similar alert for that job, I can neither pick the alert that I just > created, nor can I create another alert similar to the pervious one as > it says that one like this already exists. > > What I would really like to do is create one alert (i.e. when message > contains text like 'error') and then select that to be the alert that > I can use for that job and possibly others as well. However, it > doesn't seem to work like that. So, I'm still confused. > > So, is it possible to use alerts as I want to in SQL Server 2005? > And if not, how would you let an operator know that a batch job has > failed (preferably by email)? > > Many thanks for your time, > Frank. Tibor,
Once someone points it out to you it's becomes so blatantly obvious :-) You're a star! Many thanks, Frank. I'm glad you "see the light", Frank.
Actually, I've always been confused what the "Alert" tab does for a job. I do know what alerts are (EventLog, Perfmon and now WMI), but I just failed to understand why we would have an alert tab for the job. It just felt ... backwards. You configure and alert, and then what job to fire. So your post made me investigate what this alert tab (for a job) does and indeed it doesn't bring any new functionality, it just allow us to configure that this job is to be fired for an alert - in a pretty backwards kind of way (and indeed the same config as you do for the alert). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <francis.mo***@gmail.com> wrote in message news:d59e3dd6-8aa5-4bea-bc03-515257f5a3b3@a35g2000prf.googlegroups.com... > Tibor, > > Once someone points it out to you it's becomes so blatantly > obvious :-) > You're a star! > > Many thanks, > Frank. On Nov 27, 12:40 pm, "Tibor Karaszi"
<tibor_please.no.email_kara***@hotmail.nomail.com> wrote: Show quote > I'm glad you "see the light", Frank. Frank - you should check out the xSQL Software's RSS Reporter for SQL> > Actually, I've always been confused what the "Alert" tab does for a job. I do know what alerts are > (EventLog, Perfmon and now WMI), but I just failed to understand why we would have an alert tab for > the job. It just felt ... backwards. You configure and alert, and then what job to fire. So your > post made me investigate what this alert tab (for a job) does and indeed it doesn't bring any new > functionality, it just allow us to configure that this job is to be fired for an alert - in a pretty > backwards kind of way (and indeed the same config as you do for the alert). > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <francis.mo***@gmail.com> wrote in message > > news:d59e3dd6-8aa5-4bea-bc03-515257f5a3b3@a35g2000prf.googlegroups.com... > > > > > Tibor, > > > Once someone points it out to you it's becomes so blatantly > > obvious :-) > > You're a star! > > > Many thanks, > > Frank.- Hide quoted text - > > - Show quoted text - Server (http://www.xsqlsoftware.com/Product/ Sql_Server_Rss_Reporter.aspx) - it is a great way to notify someone on the status of SQL Server Jobs. It automatically generates RSS feeds that aggregate job status information from multiple servers - it avoids the hassles associated with the email notifications, it allows you to drill down on a job to see what step of it actually failed etc. JC  |
|||||||||||||||||||||||
Other interesting topics