none
SQL Server Alert using WMI Event

    Question

  • Hi 

    I want to execute a job when ever a file is dropped into a  particular folder.

    I found some articles where we can do it on SQL Server. (http://arookiebidev.blogspot.com/2011/07/sql-server-agent-agent-alerts.html)

    I created a alert type: " WMI Event Alert "

    For the name space its the SQL instance which comes automatically as "\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"

    On the Query section - I wrote the below query ,

    "SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘c:\\TestFolder\’ "

     bit it throws out an error msg 

     

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Cannot create new alert. (SqlManagerUI)------------------------------

    ADDITIONAL INFORMATION:

    Create failed for Alert 'AlertTest'.  (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2425.0+((KJ_PCU_Main).110406-2044+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Alert&LinkId=20476------------------------------An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------

     

    SQLServerAgent Error: WMI error: 0x80041058.  

    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. (Microsoft SQL Server, Error: 22022)

     

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2425&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

    Can some one please advice me is my Query is correct and is there any thing i need to check out something.
    Thanks a lot in advance.
    Regards
    Shan

     

    Thursday, September 22, 2011 10:15 AM

Answers

  • I just tried with your variables and it is working for me. Can you try this:

    EXEC msdb.dbo.sp_add_alert @name=N'SimpleFolderWatcher', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@category_name=N'[Uncategorized]', 
    		@wmi_namespace=N'\\.\root\cimv2', 
    		@wmi_query=N'SELECT * FROM __InstanceCreationEvent 
    WITHIN 1
    WHERE TargetInstance ISA ''CIM_DataFile'' 
    AND TargetInstance.Drive = ''C:'' 
    AND TargetInstance.Path=''\\testfolder\\'' 
    AND TargetInstance.Name LIKE ''C:\\testfolder\\%'' ', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    

     

     


    Jon
    • Marked as answer by xXShanXx Thursday, September 22, 2011 1:44 PM
    Thursday, September 22, 2011 1:09 PM

All replies

  • The namespace is incorrect.

     

    Here is a quick example:

     

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'SimpleFolderWatcher', 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@wmi_namespace=N'\\.\root\cimv2', 
    		@wmi_query=N'SELECT * FROM __InstanceCreationEvent 
    WITHIN 1
    WHERE TargetInstance ISA ''CIM_DataFile'' 
    AND TargetInstance.Drive = ''M:'' 
    AND TargetInstance.Extension=''csv'' 
    AND TargetInstance.Name LIKE ''M:\\2011%'' ', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    
    


    Jon
    Thursday, September 22, 2011 10:59 AM
  • Hi Jon 

    Thanks for your reply.

    I tried with ur namespace, I get the below error

    Msg 22022, Level 16, State 1, Line 0

    SQLServerAgent Error: WMI error: 0x80041058.

    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 even tried to use ur scripts with ur suggested namespace and with my server default instance namespace. - NO LUCK :(

     

    Thanks

    Shan

    Thursday, September 22, 2011 11:26 AM
  • I just tried with your variables and it is working for me. Can you try this:

    EXEC msdb.dbo.sp_add_alert @name=N'SimpleFolderWatcher', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@category_name=N'[Uncategorized]', 
    		@wmi_namespace=N'\\.\root\cimv2', 
    		@wmi_query=N'SELECT * FROM __InstanceCreationEvent 
    WITHIN 1
    WHERE TargetInstance ISA ''CIM_DataFile'' 
    AND TargetInstance.Drive = ''C:'' 
    AND TargetInstance.Path=''\\testfolder\\'' 
    AND TargetInstance.Name LIKE ''C:\\testfolder\\%'' ', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    

     

     


    Jon
    • Marked as answer by xXShanXx Thursday, September 22, 2011 1:44 PM
    Thursday, September 22, 2011 1:09 PM
  • Hi Jon 

    This is great

    Thanks a lot very much - it works perfect.

    Much appreciated.

    Regards

    Shan

    Thursday, September 22, 2011 1:44 PM