4

I'm unable to modify a Notify Operator task in my new maintenance plan. SQL Server Management Studio displays this error after double clicking on the task:

Property IsMailHost is not available for database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access right.

The other tasks in the maintenance plan are working successfully. I'm using SQL Server 2016 Standard (13.0.1601.5) and SQL Sever Management Studio 16.5. The database mail is also configured with a public profile.

  • Did you resolve this problem? I'm facing the same issue. We've just come from SQL 2008 and it seems it's the SSMS tool that causes the issue, since we can't edit Notify Operator Tasks on SQL 2008 from SSMS 13.x, but we can from SSMS 10.x!! – Matty Brown Feb 20 '17 at 21:32
1

From the message you posted I can see MSDB is not set for Mail Host Status.

Database.IsMailHost Property -- Gets a value that specifies whether the database is configured as a mail host.

To find whether the Database is a Mail Host or not, use the below PowerShell command. I tested it on SQL Server 2014.

Use the below code in Powershell ISE to check: (For Default Instance Only)

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("MSDB") 
Write-Host "Database is a mail host :" $db.IsMailHost 

The MSDB database is the Database Mail host database. This database contains the stored procedures and messaging objects for Database Mail.

By default, Database Mail is not active.

You must explicitly enable Database Mail by using either

  1. The Database Mail Configuration Wizard

or

  1. The Database Mail XPs Option of the sp_configure system stored procedure.

The following example enables the Database Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
  • 1
    I'm facing the same issue. I wrote a very similar Powershell script to you and found that the IsMailHost property is True. Seems to be an issue with the SQL Server Management Suite software? – Matty Brown Feb 20 '17 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.