Prev: How to move all tables to a new filegroup in sql 2005
Next: Rebuilding the master database on a 2005 Cluster
From: Med Bouchenafa on 3 Dec 2007 09:22
Thank you so much Jason
You point me in the right direction
If somebody from the SQL Server Documentation Team is reading this, It would be more than nice adding, to SQL Server Books online, a WMI example for events raised by the operating system
All current examples are related to SQL Server events

Thanks again
Med

"jason" <jason-r3move(a)statisticsio.com> wrote in message news:0E4764DB-3D42-4F1F-BDFC-B549535F7A3C(a)microsoft.com...
Actually, it does fire.
"jason" <jason-r3move(a)statisticsio.com> wrote in message news:CB05198F-C9DC-40A4-A0D5-D5CC28C3EFC4(a)microsoft.com...
Ahh, it looks likes it has to be an event query.

It lets me create an alert with an event query but I cannot get it to fire.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert

@name =N'Test',

@enabled =1,

@delay_between_responses =0,

@include_event_description_in =0,

@wmi_namespace =N'\\.\ROOT\CIMV2',

@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 10000000'

GO

"Med Bouchenafa" <com.hotmail(a)bouchenafa> wrote in message news:uIvq2oTNIHA.4272(a)TK2MSFTNGP06.phx.gbl...
Jason,
Thank you for your input
In this web link Microsoft clearly said that

SQL Server Agent responds to SQL Server WMI events, as well as to WMI events raised by the operating system and other WMI providers

SQL Server Agent is here only a WMI consumer.
Regarding SQL Server WMI Events, I guess that any application and not only SQL Server Agent can access them using the WMI Provider .

The example I provided is so simple and obvious
I can not understand why such simple task is not documented.

Thanks again
Med


"jason" <jason-r3move(a)statisticsio.com> wrote in message news:B0A71EE8-EB53-41C9-BB3F-C1508F13D8D3(a)microsoft.com...
I do not think you can access OS WMI classes this way. Just the SQL Server provider. I was never able to get something like this to work and like you said there are no samples\examples. However, I may be wrong.

You can work around this several ways with xp_cmdshell to powershell, CLR or the SSIS WMI tasks.

I have a blog post with a CLR example.

http://statisticsio.com/Home/tabid/36/EntryID/23/Default.aspx


--
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx


"Med Bouchenafa" <com.hotmail(a)bouchenafa> wrote in message news:e4cTUFSNIHA.4752(a)TK2MSFTNGP05.phx.gbl...
Did somebody ever manage to setup a WMI event alert through SQL Server Agent?
I could not find any example that works.
Can you please, try this and see if it works for you

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert
@name =N'Test',
@enabled =1,
@delay_between_responses =0,
@include_event_description_in =0,
@wmi_namespace =N'\\.\ROOT\CIMV2',
@wmi_query =N'Select from Win32_LogicalDisk Where FreeSpace < 1000'
GO

What I want to achieve is very simple.
Set up an alert that raises when disk space goes below a threshold.

Whatever method I used (GUI or sp_add_Alert), I always got the same error message
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x80041059.
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.

I'm aware of this KB article http://support.microsoft.com/kb/944517
My machine name is less 15 characters
and I'm running SQL Server 2005 - 9.00.3152.00

Any help is more than appreciated

Thanks
Med

First  |  Prev  | 
Pages: 1 2
Prev: How to move all tables to a new filegroup in sql 2005
Next: Rebuilding the master database on a 2005 Cluster