July 18, 2008 at 6:53 am
Okay, here's a head scratcher;:blink: when I create a new operator and attempt to assign new alerts and nothing shows up in the display. I've tried jobs as well and get the same thing. Since I did not receive e-mails or pages, I am assuming that unless I mark something, I will get nothing.
Is there some way to force the alerts? I've looked at sp_add_operator but it does appear to have any where to add the alerts or type of notification.
July 18, 2008 at 7:28 am
On the alerts themselves, you will add the operator(s) to be alerted in the "Response" tab. On the jobs, you'll go to the "Notifications" tab.
So, add the operator and its contact information first, then go to the alerts/jobs you want to have that operator notified of. When you go back to the properties of the operator, you'll see things listed.
Kyle
July 18, 2008 at 8:06 am
But, I just want to be notified of the standard alerts. For those, all I see is 'Enable All' and 'Disable All' and New Alerts, (that one has 'response'). When I look at the Operator Properties there is nothing showing on the Notifcation page. Shouldn't all of the standard alerts and my jobs be listed there?
July 18, 2008 at 8:12 am
Are you using SQL 2000 or SQL 2005?
July 18, 2008 at 8:17 am
SQL 2005
July 18, 2008 at 8:24 am
I've been proven wrong before and I'll likely be proven wrong again, but I've never seen any default alerts on 2005 that weren't created in respect to Replication.
All the alerts I've ever used had to be created.
What alerts are you expecting to see?
July 18, 2008 at 8:33 am
Type 001, Type 007, Type 008, Type 009...Type 025.
July 18, 2008 at 8:41 am
Run this to add the alert, changing "001" for each of your severities...
EXEC msdb.dbo.sp_add_alert
@name=N'Severity 001',
@severity=001,
@message_id=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
It should then show up in your list for the operator. SQL 2000 included some of these (which is why I asked earlier), but I've never seen them included by default in SQL 2005.
July 18, 2008 at 10:14 am
I tried the code change 'Severity' in the name to 'Type' and received errors that they all existed. I still don't see anything in the notification page.:crying:
July 18, 2008 at 10:27 am
Do they show up when you open up the "Alerts" folder in Management Studio?
What shows up when you run this query?
select id, name, severity, enabled from msdb.dbo.sysalerts
July 18, 2008 at 10:30 am
No, they didn't show up in the list. The code shows nothing. sp_help_alert shows that they exist.
July 18, 2008 at 10:48 am
Interesting...
I did an sp_helptext on that procedure and it uses msdb.dbo.sysalerts as the basis for the query.
At this point, it may be a stupid question, but can you verify that SELECT @@SERVERNAME has the same results in the context of the code above and the sp_helptext? Possibly, run them together?
select id, name, severity, enabled from msdb.dbo.sysalerts;
EXEC sp_help_alert
What is the result of SELECT @@VERSION? I'm trying this on SP2 only.
July 18, 2008 at 11:00 am
Every thing is the same. When I run:
sp_help_alert @alert_name='Type 025'
I get: "The specified @alert_name ('Type 025') does not exist"
When I run sp_add_alert I get: "The specified @name ('Type 025') already exists"
I know that MS some times has trouble making up their mind, but which is correct, does it exist or not? :crazy:
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply