QUOTED_IDENTIFIER error

[davimack]

As of today I've been getting the following error:

Msg 1934, Level 16, State 1, Procedure sp_generate_merge, Line 764 [Batch Start Line 1] SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 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.

The error itself seems to arise from line 778 in the file here, and MIGHT be an issue with FOR XML PATH or AS XML (somebody on the internet mentioned XML as the culprit).

 BEGIN
  SET @output += 'VALUES' + CAST((SELECT @b + val FROM @tab ORDER BY ID FOR XML PATH('')) AS XML).value('.', 'NVARCHAR(MAX)');
 END

The initial problem was on SQL 2016. Order of troubleshooting:

• ran flavors of SET QUOTED_IDENTIFIER ON, against the DB and as an ALTER DATABASE statement
• dropped and re-created the database
• tried running sp_generate_merge against another database, which failed
• installed current version of sp_generate_merge
• stopped and started services, rebooted, etc.
• repaired SQL 2016
• uninstalled SQL 2016
• uninstalled SSMS
• installed SQL 2019
• installed SSMS
• verified problem still exists
• someone mentioned XML, so tried asking for text exec sp_generate_merge @table_name = 'Employees', @results_to_text = 1
• installed current version of sp_generate_mergeinto the database itself, with no luck.

At this point, I'm thinking that something in the OS must have changed, or something's left behind with the uninstall / install of 2016 / 2019. If it's a change or a patch, I don't have any idea when it happened, because I usually only run sp_generate_merge on a production server, to bring data back for test / dev purposes.

I would love it if someone told me this was my fault ... and how to fix it?

OS: Windows 10 Pro, Version 10.0.19044 Build 19044 MSSQL Versions: 2016 and 2019 SSMS Version: 15.0.18410.0

Possibly affecting this is that a junior engineer's test database is one which was scripted from their development machine and then run on their sandbox. I've listed the ALTER DATABASE statements from that script below. I've ZERO idea how this could have affected the entire server, but for completeness:

ALTER DATABASE [MyDBTest] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [MyDBTest] SET ANSI_NULLS OFF
ALTER DATABASE [MyDBTest] SET ANSI_PADDING OFF
ALTER DATABASE [MyDBTest] SET ANSI_WARNINGS OFF
ALTER DATABASE [MyDBTest] SET ARITHABORT OFF
ALTER DATABASE [MyDBTest] SET AUTO_CLOSE OFF
ALTER DATABASE [MyDBTest] SET AUTO_SHRINK OFF
ALTER DATABASE [MyDBTest] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [MyDBTest] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [MyDBTest] SET CURSOR_DEFAULT  GLOBAL
ALTER DATABASE [MyDBTest] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [MyDBTest] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [MyDBTest] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [MyDBTest] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [MyDBTest] SET DISABLE_BROKER
ALTER DATABASE [MyDBTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [MyDBTest] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [MyDBTest] SET TRUSTWORTHY OFF
ALTER DATABASE [MyDBTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [MyDBTest] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [MyDBTest] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [MyDBTest] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [MyDBTest] SET RECOVERY FULL
ALTER DATABASE [MyDBTest] SET  MULTI_USER
ALTER DATABASE [MyDBTest] SET PAGE_VERIFY CHECKSUM  
ALTER DATABASE [MyDBTest] SET DB_CHAINING OFF
ALTER DATABASE [MyDBTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
ALTER DATABASE [MyDBTest] SET TARGET_RECOVERY_TIME = 60 SECONDS
ALTER DATABASE [MyDBTest] SET DELAYED_DURABILITY = DISABLED
ALTER DATABASE [MyDBTest] SET ACCELERATED_DATABASE_RECOVERY = OFF  
EXEC sys.sp_db_vardecimal_storage_format N'MyDBTest', N'ON'
ALTER DATABASE [MyDBTest] SET QUERY_STORE = OFF

[dnlnln]

Fixed by #100