Skip to content
+44 (0) 330 99 88 700
Sign In Register
Ready to Talk?

Unable to run an SQL Server trigger: Database is modified but Task fails to trigger

Table of Contents

Symptom #

An SQL Server trigger has been created on a database table but when the table is modified the task is not triggered to run. The Windows Event Viewer log on the agent side will display the following error code stating that the task failed to queue:

Error: 70000 Severity: 16 State: 1 Trigger on[Table Name]failed to Queue [Task ID] (-2147024891)

Capture4

Cause #

DCOM permissions have not been configured. The account that the SQLServer instance runs under has not been given Local Launch, Local Activation and Local Access to the TaskCentre Microsoft SQL Server Trigger Agent component.

The TaskCentre configurator will display a red cross against the pane Agent Permissions located: Event Agents -> Microsoft SQL Server Trigger Agent -> Configuration -> Prerequisites

Capture1

Resolution #

Configure the DCOM permissions and give the account that the SQL Server instance runs under; Local Launch, Local Activation and Local Access to the Microsoft SQL Server Trigger Agent component.

It is recommended to enable this setting via the TaskCentre configurator with the aim being to obtain a green tick against the setting once the operation is successful.

TaskCentre Configurator #

  1. Under Agent Permissions click Modify
  2. Select Yes when prompted to restart the Microsoft SQL Server Trigger Agent Service. Once complete a green tick will appear in the pane indicating that the permissions are active.Capture2

Below outlines the procedure for setting the DCOM permissions via the Component Services #

  1. Start -> Run and type DCOMCNFG then press Enter to open Component Services.
  2. Expand the branches: Component Services -> Computers -> My Computer -> DCOM Config
  3. Locate the TaskCentre SQL connector class component. For versions prior to 4.6 the component name is ‘IWSQLEA’, for versions 4.6 onwards the component name is suffixed by the instant name that is being monitored by the agent, for example ‘IWSQLEA$MSSQLSERVER’.Capture6
  4. Give the component Local Launch, Local Activation and Local Access permissions to the account that the SQL server service runs under.
    Capture3
    Normally SQL server 2005/2008/2010 runs under the NETWORK SERVICE account. However this can be set differently so you should check which account the SQL server is running under so that you are applying the DCOM permissions to the correct account.
Was this article useful?