Environment

Content Manager 23.1 Patch 3 and above

SQL Server

Situation

When running the Anyword search, you encounter an error stating: "Invalid object 'STRING_SPLIT'." Please verify the database compatibility level, as the STRING_SPLIT function requires a compatibility level of 130 or higher. 

01:28:10:276           27       3920        6292   CC     CMSUPPORT1\Administrator            |search=anyWord:billy|filters=|sort=registeredOn-| SELECT uri FROM TSRECORD  WITH (NOLOCK)  WHERE ( uri IN ( SELECT URI FROM  dbo.fn_SearchTitleAndNotes ( 1, N'"BILLY"' , 0 ) ) AND rcDateRecycled IS NULL AND uri > 0 ) ORDER BY regDateTime DESC
01:28:10:276            0       3920        6292   CC                                         Error: Error executing SQL statement: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'STRING_SPLIT'.
Details: SQL state and native error code are consecutively  42S02 , 208

Cause

This is due to the database compatibility level being lower than 130.

Resolution

The STRING_SPLIT function is available in SQL Server 2016 and later, but the database must have a compatibility level of 130 or higher.

Solution:

Check and update the compatibility level of your database:

-- Check the compatibility level
SELECT name, compatibility_level 
FROM sys.databases
WHERE name = 'YourDatabaseName';

-- Update the compatibility level (if you have the necessary permissions)
ALTER DATABASE YourDatabaseName 
SET COMPATIBILITY_LEVEL = 130; -- or 160 for newer versions