Symptom #
Following an upgrade to TaskCentre v4.7, event log entries for the MS SQL Server Trigger agent appear with a message similar to the following:
Failed to create Trigger exttrigger3 on WIN-DEMO\MSSQLSERVER-DemoDatabase:dboDemoTable. Parameter or variable '@insDemoColumn' has an invalid data type. Column, parameter, or variable #7: Cannot find data type nvarcharnvarchar
Cause #
Activity within a MS SQL Server Trigger step that causes the trigger script to be generated or re-generated may cause corruption of the data types by duplicating them within the script. For example, ‘nvarcharnvarchar(50)’, ‘intint’ or ‘datedate’ etc.
This issue only affects MS SQL Server Trigger steps in TaskCentre 4.7 when used in the following configuration:
- Trigger Type: Update, Insert or Delete
- Fire Trigger: After or Instead Of
- Run task once for each row in results set in: Inserted Table or Deleted Table
- Map Columns to Task Parameters: No mappings
- Filter: A Table Filter is defined
This issue will be encountered through one of the following scenarios:
- Users who have upgraded from TaskCentre 4.6 or higher but ONLY if editing and saving an existing MS SQL Server Trigger step in 4.7. If the step remains unchanged then runtime performance is not affected
- New MS SQL Server Trigger steps using the above combination of settings
- Following the import of tasks containing MS SQL Server Trigger steps into TaskCentre 4.7
Resolution #
The underlying trigger script can be manually amended within the MS SQL Server Trigger step to correct the erroneous data types via the ‘Script’ tab in the step. However, note that this will prevent the user returning to the UI of the ‘Configure’ tab once these changes have been saved.
Alternatively, apply the associated patch which will prevent the duplication of data types when creating new triggers.
If importing tasks containing MS SQL Server Trigger steps to a TaskCentre 4.7 environment, the hotfix must be present before the import takes place. This is because the trigger scripts are created on import and require the fix to generate them correctly.
The hotfix does not amend the trigger script where the duplication is already present. In this case you will need to enter each MS SQL Server Trigger step and make a single edit to regenerate the SQL trigger script.
For example, within the ‘Trigger’ tab un-tick and then tick the ‘Filter Table’ check box.
If you then enter the ‘Script’ tab you will see that the fix has been immediately applied. Saving the task will then place the trigger on the database table.