An SQL Database needs to have the "SQL Server Service Broker" enabled in order for the Database Monitor Trigger to be able to execute the associated Process successfully.
The received error by the can be seen below and additionally in the attached screenshot.
ATMACDTR01 Internal error while deploying trigger Database Monitor Trigger
System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications.
at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults)
In order to resolve the issue, proceed as below. IT supervision is recommended.
- Connect to the corresponding database via the "Microsoft SQL Server Management Studio" (SSMS).
- Execute the following query by modifying the [Database_name] part accordingly.
ALTER DATABASE [Database_name] SET ENABLE_BROKER;
- If the above query takes more than a few seconds to complete, then use the following.
ALTER DATABASE [Database_name] SET ENABLE_BROKER WITH NO_WAIT;
- If an issue is still encountered, then use the below. It is important to note that the query will close all existing sessions, with rolling back pending transactions and enable Service Broker.
ALTER DATABASE [Database_name] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
- Verify that the Database Monitor Trigger works as intended.