Hello Community, hope you are doing well in the new year!
We can see this type of error in a couple of places with Dynamics GP. Creating a company or setup of a new user and also during an upgrade with Utilities.
When you see this type of erroneous blog of an error message, you start to think how can I ever figure this out?!
No matter where you get the error, the first most common item to check is custom triggers within SQL, as noted in this older blog post, but through the years, we always gain more knowledge and tricks and yes, you can teach this old dog (me) some new tricks too 😊
Microsoft Dynamics GP upgrades failing on CONCAT_NULL_YIELDS_NULL error message
Below is where we can start to look for triggers to solve the issue.
1. Database > DYNAMICS > Programmability > Triggers
2. Database > Master > Programmability > Triggers
3. Server Objects > Triggers
If there is anything in the Triggers folder, right-click on them and select Disable.
Then test the issue again, do you still see the errors?
If you don’t see any triggers, then you can also try running the following script to check for any custom system-level triggers, the MASTER database holds all login information for Dynamics GP users.
USE MASTER
SELECT * FROM SYS.TRIGGERS
You could use a script like this to run against the GP system database to look at what triggers exist in the database, to see if there are any custom triggers present:
Select * from sysobjects where xtype = 'TR' order by name
Running this script against a GP system database at the 18.5 version or higher, which has all modules installed, there are only 16 total triggers:
ADHWF_ADH00100UpdateTrigger
eConnectOutSetupDelete
eConnectOutSetupEnabled
mfg_delete_orphaned_MO_locks
orgEntity_SETPInsertTrigger
orgLevel_SETPDeleteTrigger
orgRelation_MSTRDeleteTrigger
orgRelation_MSTRInsertTrigger
orgRelation_MSTRUpdateTrigger
syClassMainSETPInsertTrigger
syClassMainSETPUpdateTrigger
syODataSourceDeleteTrigger
syUserMSTRInsertTrigger
syUserMSTRUpdateTrigger
vatDaybookCompanyDeleteTrigger
zDT_MC40200U
By default, Dynamics GP does not use any database or DDL triggers, so if any are found, we’d want to look at removing them and then verify that the sample company database can be created successfully or an upgrade, and users can login to it without issues. Once that is verified, any triggers can be added back at that time, if needed.
If you have verified the trigger piece above and still have issues, I have broken this into two sections with different items to verify based on when you are getting the error message.
Creating a Company with Utilities or creating a new user or changing a user password in Dynamics GP:
- Verify in SQL Server Management Studio on the properties page of the Dynamics and each company database that DYNSA is the database owner.
- Run the GRANT script against the Dynamics and each company database. The grant script is typically located in C:\Program Files (x86)\Microsoft Dynamics\GP\SQL\Util
- If you found a trigger above, it is best to run the CLEAR company script to remove the company from the database, then log into Utilities with user SA and try again.
- IT may have created a trigger at the server level where an email is sent if a new database is created and that will cause this above error message.
- Remove any 3rd party products from the set file to test.
Disable third-party products or temporarily disable additional products in the Dynamics.set file in Microsoft Dynamics GP - Dynamics GP | Microsoft Learn
- Verify the database compatibility for System database.
During an upgrade:
Usually, it is around this procedure.
The following SQL statement produced an error:
EXEC wfDeployClrAssemblies
ERROR [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, ANSI_PADDING'.
Items you should rule out to resolve the error message.
- Restart the SQL Server and the machine you are doing the upgrade on.
- Validate the correct ODBC settings (see below)
- Try the update from a fresh install of Dynamics GP code
- Verify the version of SQL that you are on and make sure it is supported for the Dynamics GP version.
- This error will happen if you deployed a 32 bit SQL, install a 64 bit version of SQL instance and the update will go through.
- If you have change tracking turn on in SQL it too will cause this error and also not allow you to create a table in the database.
- If you have any 3rd party auditing solutions that may use instance-level triggers to capture data, this too will cause the issue.
- We have seen issues with older .NET in general and recommend to be on 4.7.2 or 4.8 if you can.
Determine which .NET Framework versions are installed - .NET Framework | Microsoft Learn
Less common causes of this type of error are:
SQL Server Mgmt Studio
1. In SQL Server Management Studio, for the SQL instance that holds the GP databases, right-click on the SQL Server name and click on Properties.
2. In the Server Properties window, click on the 'Connections' page.
3. On this page, in the list of 'Default connection options', by default, everything should be un-marked, including the 'CONCAT_NULL_YIELDS_NULL', 'ANSI WARNING' and 'ANSI PADDING'. If you find these marked, un-mark them and then click OK to save changes.
4. If any changes are made, you may need to stop and restart the SQL Server service for changes to be pushed through, then login to Dynamics GP again and attempt to change a user's password to see if the same error, different error, or issue is resolved.
ODBC DSN
1. Open the 32-bit ODBC Data Source Administrator window, select the 'System DSN' tab, click on the DSN you're using to connect to Dynamics GP Utilities & click the 'Configure' button.
2. Confirm the name and SQL server name and click Next.
3. Make sure 'SQL Server Authentication' option is marked, as well as the 'sa' login ID and password, click Next.
4. Make sure all boxes are un-checked on the next window, including the 'Use ANSI quoted identifiers' and 'Use ANSI nulls, paddings and warnings'. Click Next.
5. Make sure all options are un-marked on the next window as well, click Finish.
6. Click the 'Test Data Source...' button to make sure you see a 'TESTS COMPLETED SUCCESSFULLY!' result and click Ok and then Ok and OK to close all the DSN windows.
7. Launch Dynamics GP again, selecting this DSN in the login window, and verify whether this resolves the failed sample company database errors.
Enjoy!
Thanks
Terry Heley
Microsoft
