Microsoft Dynamics GP upgrades failing on CONCAT_NULL_YIELDS_NULL error message

We've seen an recent increase in upgrades of Microsoft Dynamics GP failing on tables and/or primary keys with this type of error message:

[Microsoft][SQL Server Native Client 10.0][SQL Server]SELECT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on comp*/

Usually when we see these type of errors, it is related to DDL triggers on the SQL instance or databases triggers on SQL or GP databases. 

If you run into this type of error message when doing an upgrade of Microsoft Dynamics GP databases, you can look at the following in order to troubleshoot and find the cause of the error:
 
1. In SQL Server Management Studio where the Dynamics GP databases being upgraded are held, if you go into Server Objects > Triggers, look for any DDL triggers that are listed and if they are, look at them to see if they are referencing the table(s) in the upgrade error message? By default, Microsoft Dynamics GP won't put anything here. 
 
  2. Still in SQL Server Management Studio, expand Databases > then expand the GP database the upgrade is failing on, next expand Programmability and try to expand 'Database Triggers', is anything listed here? Again, by default, Microsoft Dynamics GP won't put anything here.
 
  3. We've also seen a case or two where, when upgrading the DYNAMICS system database we got this type of error message. The result was that there was a database trigger on the Master database. Once we removed the trigger, the DYNAMICS database upgraded fine.

      You can verify this in SQL Server Management Studio by expanding the Master database, then expanding Programmability > Database Triggers and see if any exist. Microsoft Dynamics GP does not have any database triggers under the Master database. 
 
  4. System triggers or instance level triggers on the DYNAMICS and/or company databases. We can verify these by running the following script against the GP databases each:

             SELECT * FROM SYS.TRIGGERS order by name

    Any non-default Dynamics GP triggers that we find on the databases, we can normally disable until the upgrade is completed, then enable them again. Otherwise, we can script them out, delete them for the upgrade to run, then run the script to re-create them.

More times than not, it is a third-party trigger(s) or custom trigger(s) that cause this type of error message. As mentioned, if we find anything like this, we can normally either disable the trigger until the upgrade has completed, then enable them again. Otherwise, you can script out the trigger into a file, then delete it completely from the database, then after the upgrade has completed, use the same script to re-create the trigger where it needs to go.

Hopefully this will save you some time and a support case or two if you encounter this type of error message when running an upgrade of Microsoft Dynamics GP.