A client reported an urgent support call a short time ago that they could not post any journals and were receiving this error when they tried:
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 comp
This error was produced for all users in only one of their 10+ companies; the other companies could have a journal posted by anyone with no issues. My initial question was to ask what had been changed or deployed in that database, but was told nothing. This meant some investigation.
I fairly quickly found a post from Tim Wappat covering how SQL indexed views are incompatible with Dynamics GP and will result in the exact error the client was seeing. this would make sense as the error referenced indexed views.
I used my find all SQL objects script to look for any object containing the SCHEMABINDING keyword, and found two SQL views in that one database.
After passing this information back to the client they again checked around and found a developer had deployed two SQL views for a report to the database first thing that morning; they hadn’t mentioned them as they didn’t expect them to have caused the problem. Once the views were updated to remove the index, the error was no longer produced when journals were posted.