A couple of weeks ago, we had an unusual support case: upgrading two of our products, Visual Studio Integration Toolkit and GP Power Tools, was failing with a SQL Server error I had never seen before.
The errors only occurred on the customer’s live system, but the install worked fine on their development system. Eventually the cause for the issue was identified.
Today, my good friend Beat Bucher, had the same error and I was able to tell him the cause straight away. Read on for a full explanation.
The products were trying to update their tables from the old structures to the new structures for the newly installed builds but kept reporting that the tables were missing or had the incorrect structure.
The Problem
Here is a screenshot of the error message from SQL Server. Depending on the process, turning on SQL Logging in the Dex.ini file to capture a DEXSQL.LOG might be needed to see the error.
The full error message is as follows:
An error occurred executing SQL statements.
GPS Error: 58
SQL Error: 1934 [Microsoft][SQL Server Native Client 11.0][SQL Server]INSERT 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 computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
ODBC Error: 37000
Because the tables had failed to update, the following dialog is displayed to identify which tables had issues:
At the end of the install process, as the tables were not the correct format, the following error message was displayed asking you to check the user has permissions and that you have the correct version installed.
This last error message mentions common reasons for the install to fail but it is not helpful in this case. The product features are also disabled because the tables are not yet usable.
The Cause
For the initial support case, after checking many settings in SQL Server and comparing between the Live and Development environments we were still unable to locate the cause of the issue. Then I heard back from the customer that they had worked it out.
Their live system had DDL (Data Definition Language) Triggers added to log any changes to the database objects (Tables, Views, Stored Procedures, etc.). In theory this sounds like a great idea for a database administrator to monitor for unexpected changes in their system. However, in practice there can be issues.
Note: When there are triggers of any sort used in SQL Server, they are executed as a single transaction with the commands that fired the trigger. If the trigger code fails for any reason, the entire transaction including the original command fails. This is why it is so important for triggers to be written correctly. Finding the cause of this sort of issue where the original code is fine, but a faulty trigger makes it fail is very hard. That is why the SQL Trigger Control feature of GP Power Tools exists.
When Microsoft Dynamics GP had support for SQL Server added originally in version 3.0, the implementation was designed to mimic the behavior of the previous ISAM platforms (Ctree and Btrieve/Pervasive SQL).
For example: GP does not understand NULL which is why all table columns are defined as NOT NULL. It also does not want thousand separators or currency symbols returned in numeric data.
To help with that GP turns off ANSI_WARNINGS, ANSI_NULLS, ANSI_PADDINGS and QUOTED_IDENTIFIERS. This is why those settings should not be checked in the ODBC setup.
The DDL triggers that were added to the system must have been using commands that need those options turned on. So, changes to the database schema made from SQL Server Management Studio will work and have the triggers log the changes. But changes attempted from scripts running inside GP will have the settings turned off and due to the trigger errors, they fail.
The Solution
Disabling the DDL Triggers temporarily allows the installs to complete without any further issues.
Note: If you want to use the SQL Maintenance window in Microsoft Dynamics GP to drop and recreate objects, you will also need to disable the triggers. FYI: If you have GP Power Tools with Database Tools module registered, it will offer to preserve data when dropping and recreating tables.
A possible solution to this issue would be to modify the trigger code. You would need to create a stored procedure with the parameters needed for the insert statement and move the insert statement into the stored procedure. Then the trigger could check the settings and change them if needed, call the stored procedure (which now runs with the changed settings), and then restore the settings if they were changed.
More Information
The following links explain more about DDL Triggers and how they were used for logging schema changes:
- SQL Server DDL Triggers to Track All Database Changes
- Using the EventData() Function with DDL triggers in SQL Server 2005
- Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers
- Microsoft Learn: DDL Triggers (old article)
- Microsoft Learn: DDL Triggers (new article)
- Microsoft Learn: Auditing in SQL Server 2008
- Microsoft Dynamics GP Support: Microsoft Dynamics GP CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS error message, what could this mean?
- Microsoft Dynamics GP Support: Microsoft Dynamics GP upgrades failing on CONCAT_NULL_YIELDS_NULL error message
Thanks goes to Beat for the screenshots and for locating the articles talking about the DDL Triggers which caused the issue.
Hope this is helpful.
David
10-Feb-2024: Updated with related blog articles from Microsoft Dynamics GP Support Team found by Beat.
This article was originally posted on http://www.winthropdc.com/blog.
Winthrop Development Consultants Blog 