开发者

SQL Server Alert using WMI Event ERROR

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

I found some articles that showed me how I can do it on SQL Server.

I created a alert type: WMI Event Alert

For the name space its the SQL instance which comes automatically as \\.\开发者_StackOverflow中文版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\’ `

The error message returned is:


Cannot create new alert. 

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

Please may you advise if my Query is correct and if there is anything else I need to check?

Thanks a lot in advance.


You are using the wrong namespace, the CIM_DataFile WMI class is part of the \root\CIMV2 namespace and not of \root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER


In this case, the answer from RRUZ is correct. However there are other possible causes of this error message:

The @wmi_query could not be executed in the @wmi_namespace provided.

One possible reason is the account that runs the Windows service "Windows Management Instrumentation" is disabled as a SQL login. (If you are running SQL 2012+, look for the login 'NT SERVICE\winmgmt'). (Source: Blog by 'rahmanagoro' )

Edit 2020-05-29: I've made a more comprehensive answer to this question on the DBA forum. Another possible fix is to restart the "Windows Management Instrumentation" service. No idea what leads to the problem, but restarting the service fixes it. I've seen this twice, both times on Windows Server 2008 R2 Standard Edition x64.

Edit 2020-05-29: I've made a more comprehensive answer to this question on the DBA forum.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜