Recently we have run into upgrade issues on Microsoft Dynamics SL installations with custom triggers that do not use the SET NOCOUNT ON / OFF syntax.  The SQL below will help to troubleshoot and resolve this issue (note that you may need to make modifications to the SQL to match your installation).  The resulting problems can vary from System Message 10232 while importing or exporting customization to a timeout when running the Database Maintenance routine to upgrade the database/s to SL 2011 FP1.

You can disable the triggers and move past the errors but it is recommended that you add the “SET NOCOUNT ON” near the beginning of the SQL Trigger and “SET NOCOUNT OFF” near the end of the SQL trigger.  You can reference one of the standard triggers for FRx on the Account table in the application database via SQL Management Studio as a guide to adding the NOCOUNT.

Disabling a trigger does not delete it from the SQL database.

The following scripts will help find all triggers on your databases. It should be run against each database.

select * from sysobjects where xtype=’TR’

The following script will build a script to disable the triggers.  You will want to change your output to text, run the script and then copy and paste the results into a new query window and run them to disable the triggers.

select ‘alter table ‘ +quotename(s.name)+’.’+quotename(object_name(o.parent_obj))+ ‘ disable trigger ‘+quotename(o.name) from sys.sql_modules m join sys.sysobjects o on m.object_id=o.id join sys.schemas s on s.schema_id = o.uid where o.type=’tr’

The following script will build a script to enable the triggers.

select ‘alter table ‘+quotename(s.name)+’.’+quotename(object_name(o.parent_obj))+ ‘ enable trigger ‘+quotename(o.name) from sys.sql_modules m join sys.sysobjects o on m.object_id=o.id join sys.schemas s on s.schema_id = o.uid where o.type=’tr’

Always be sure to backup your database before making any changes.  These scripts are provided as is.  If you need assistance and would like to engage our services please email acumaticasupport@swktech.com. Service fees will apply.

 

Please follow and like us: