October 19, 2006 at 2:44 am
Hello everybody,
I'm trying out some wmi alerts in SQL2005. While creating the alerts usally works fine, some alerts never fire. For example a defined an alert for CREATE_DATABASE, but it never fires. When I define an alert for all DDL_EVENTS it works fine. Has naybody else seen this? Here's the syntax for the alert:
EXEC
msdb.dbo.sp_add_alert @name=N'New DB alert, @enabled=1,
@delay_between_responses
=0, @include_event_description_in=4,
@wmi_namespace
=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query
=N'SELECT * FROM CREATE_DATABASE'
Markus
[font="Verdana"]Markus Bohse[/font]
October 23, 2006 at 8:00 am
This was removed by the editor as SPAM
October 23, 2006 at 8:55 am
I tried to create an alert for Create Database based on this script and on BOL but it said:
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x80041013.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 300
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.
Just in case posting the script I used in case I made a typo. I believe it is same as in the original post. I did verify the event classes in BOL, looks OK.
EXEC
msdb.dbo.sp_add_alert @name=N'New DB alert', @enabled=1,
@delay_between_responses=0, @include_event_description_in=4,
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM CREATE_DATABASE'
Regards,Yelena Varsha
April 10, 2007 at 3:24 pm
I get this same error. MS has one thread in msdn forums:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=342946&SiteID=1
The MS poster says it's a bug - and also that it results from moving the msdb incorrectly - which I don't think has been done on all 4 of my servers! Stuck on this and really need it for mirroring, to activate jobs and whatnot on a state change.
May 30, 2007 at 8:50 pm
Has anyone had any luck fixing this? I also have this error and have not moved msdb.
May 30, 2007 at 11:12 pm
have you checked if applying SP2 + Cumulative update solves this ?
also, did it ever worked on your box ?
May 31, 2007 at 1:10 am
Since I stated this post some time ago I haven't spend too much time on it. Basically I use other methods to monitor these events.
But in reply to Amits question, I just tested it on a SP2 box, but the result is the same. While an Alert for all DDL_Events captures the creation of a new database, An Alert specific for "Create Database" won't fire.
Markus
[font="Verdana"]Markus Bohse[/font]
June 1, 2007 at 7:29 pm
i am able to get this to work on anything before SQL 2005 SP2, is that what others are experiencing? I have also rolled it up with the cumulative hotfixes and no luck.
June 2, 2007 at 4:46 am
JDC,
could you post your code which works on the pre-SP2 machine. Because when I originally posted this question I tested it on a SP1 machine and my code did not work.
Markus
[font="Verdana"]Markus Bohse[/font]
June 4, 2007 at 5:57 am
I have a CREATE DATABASE alert that works, with the definition below. The response to the alert is to send email to DBA (a Windows mail group). The alert definition looks just like yours, but have you defined the response to the alert?
USE
[msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Alert when database is created',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Database $(ESCAPE_NONE(WMI(DatabaseName))) has been created on $(ESCAPE_NONE(WMI(ComputerName))) by $(ESCAPE_NONE(WMI(LoginName)))',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM CREATE_DATABASE',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'dba@mydomain.com',
@category_name=N'[Uncategorized]',
@netsend_address=N'mylogin'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Alert when database is created', @operator_name=N'DBA', @notification_method = 1
GO
June 4, 2007 at 6:02 am
Sorry, I didn't read all the messages. My servers are still at SP1.
July 24, 2007 at 11:59 am
Has anyone found a solution to this problem yet? I'm having the same issue and my msdb is sitting in the default location, having never been moved to the best of my knowledge. The problem appears to either be a security issue, or a registration issue. When I checked the namespace in Scriptomatic 2.0, it says there are no dynamic classes registered on the server.
I just want to setup a simple monitor to send the IT group an email if auto failover occurs on our high-availability database so we knwo we need to check and see what happened, fix it, and fail it back.
I really don't want to have to go about writing my own monitor when MS is supposed to have this in place...
Thanks,
RB
November 15, 2007 at 7:59 am
There are two things to check here
1) @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
check that the code is being run against a default instance otherwise the MSSQLSERVER will have to be the instance
2) Check that in the SQL Server Agent Properties you have 'Replace Tokens for all job responses to alerts' checked
November 15, 2007 at 10:33 pm
944517FIX: Error message when you create a WMI event alert by using the sp_add_alert stored procedure in SQL Server 2005: "The @wmi_query could not be executed in the @wmi_namespace provided"
http://support.microsoft.com/default.aspx?scid=kb;EN-US;944517
November 19, 2007 at 1:45 pm
That was it, the severname is 15 characters exactly. Thanks for the link
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply