June 15, 2009 at 7:27 am
Hi all,
I'm a SQL Server newbie and I'm trying to set up alerting for database mirroring. I want to receive an email when the state of a database mirroring session changes.
As described in the article () I'm trying to create an alert in SQL Server Management Studio as follows :
Type : WMI event alert
Query :
EXEC msdb.dbo.sp_add_alert
@name=N'DB Mirroring: State Changes',
@category_name=N'Database Mirroring',
@wmi_namespace=
N'\\.\root\Microsoft\SqlServer\ServerEvents\TEST',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE',
@enabled=1
However I get the following error message :
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot create new alert. (SqlManagerUI)
------------------------------
ADDITIONAL INFORMATION:
Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)
------------------------------
BUTTONS:
OK
------------------------------
And when executing the query :
USE [msdb]
GO
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
I get the following error :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DATABASE_MIRRORING_STATE_CHANGE'.
Anyone an idea? Could this have something to do with the setup (SQL Server 2005 SP2)?
June 15, 2009 at 7:50 am
This is a runtime error from SQL Server management studio and nothing to do with the command you are executing.
If you don't have sql server 2005 service pack 2, install it else reinstall it. This should fix the problem.
June 22, 2009 at 2:40 am
The event you are configuring uses the WMI Provider. You cannot, unfortunately, just run the WQL statement (which is written for the WMI Namespace) directly against MSDB. The object DATABASE_MIRRORING_STATE_CHANGE simply doesn't exist in the database.
Other, similarly-named system Views do exist, such as:
SELECT * FROM msdb.sys.database_mirroring;
SELECT * FROM msdb.sys.dm_db_mirroring_connections;
SELECT * FROM msdb.sys.database_mirroring_endpoints;
SELECT * FROM msdb.sys.database_mirroring_witnesses;
Are you using the GUI of the Management Studio to create the Alert, or using a script? I suggest you use the GUI, as this leads you through what is required and will, for example, automatically complete the correct Namespace for you.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply