STRING_AGG' is not a recognized built-in function name

Symptom : The error message 'STRING_AGG' is not a recognized built-in function name during database upgrade after software update OR during operation after an aparent sucessful database upgrade.

Cause : The STRING_AGG function was introduced in SQL Server 2017, and the earlier SQL Server environment being used is throwing that error during the database upgrade. 

Solution : To get past this error and successfully upgrade the system will require an upgrade to SQL Server 2017 or higher. Alternatively, it is possible to change the upgrade scripts to account for the different supported method.

Do not attempt to just uninstall the new version and reinstall your to back out. Because the database was touched by the upgrade scripts, a simple downgrade will leave you with an "incompatible database version" error. If you decide to roll back instead of upgrading SQL Server right now, your best route is to restore the database and file storage from a pre-upgrade backup.

Because STRING_AGG is only used once within the specific database scripts (inside

 the DeleteProjectByTenant procedure), we can manually replace it with the standard pre-2017 T-SQL equivalent. This 

will allow you to successfully bypass the error without needing to upgrade your entire SQL Server 2016 environment right now.

Depending on how you decide to proceed with the database, the location of the script you need to modify will change slightly. I have outlined all three possible scenarios below.

The Code Replacement

In whichever location applies to your scenario (see below), find the DeleteProjectByTenant procedure and locate this exact snippet:

SELECT @ProjectIdsOut = STRING_AGG(CAST(ProjectId AS NVARCHAR), ',')

FROM @DeletedProjects;

Replace that snippet entirely with the following code block:

SELECT @ProjectIdsOut = STUFF((

    SELECT ',' + CAST(ProjectId AS NVARCHAR(MAX))

    FROM @DeletedProjects

    FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

Scenario A: Upgrading the Database (most likeley path)

If you are continuing with the upgrade of your existing test database:

1. On your FlexiCapture Application Server, open this file in a text editor:
2. 
   
3.  C:\inetpub\wwwroot\FlexiCapture12\Server\Upgrade146to147.sql
4. 
   
5. Replace the code as shown above and save the file.
6. Run the database upgrade process from the Administration and Monitoring Console.

Scenario B: Creating a New Database

If you decide to abandon the upgrade and start fresh with a newly created database:

1. Open this file in a text editor: C:\inetpub\wwwroot\FlexiCapture12\Server\Description.sql
2. 
   
3. Replace the code as shown above and save the file.
4. Proceed with creating the new database in the console.

Scenario C: Fixing an Existing Database

If the database was already created or partially upgraded and the error is occurring during runtime operations:

1. Open SQL Server Management Studio (SSMS).
2. Navigate to your FlexiCapture Database -> Programmability -> Stored Procedures.
3. 
   
4. Locate the DeleteProjectByTenant stored procedure, right-click, and select "Modify".
5. 
   
6. Replace the code block and execute the script to alter the procedure.