Small and medium business | Business Central, N...

Modern Search doesn't work on OnPrem BC25 environment

(7) Share

Report

Posted on by Jakob Gillinger

Hello,

In order to upgrade from BC14 to BC25 in an OnPrem environment, I had to enable the "free text search" feature in the SQL server. Checking the status of the feature with the recommended command `SELECT SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled` returns a 1, so it should be enabled. However, when using the modern search feature in the BC client, the following error is thrown:
```A CONTAINS or FREETEXT predicate can't be used on the table because full-text indexing is not enabled on that specific table.```
From what I can tell, the error is caused because SQL has to build an index catalog of the table first, but the documentation mentions nothing for setup beyond activating the feature on the SQL server. Is there some upgrade step that might have been done improperly, or is there something missing that should make BC build the indexes?

Business Central version: 25.1.25873.25900 (OnPrem)

Microsoft SQL Server version: 2022 (RTM-CU19) (KB5054531) - 16.0.4195.2 (X64)

EDIT:

I noticed that the modern search feature of a different server instance works without a problem. Compared to the first server instance this one is using

the same Business Central version
the same SQL server
a different SQL database

With this, we can confirm it's not a problem with the SQL server, but the BC server instance not creating the indexes. Is there some way I can re-synchronize the base application? Maybe this will create them from scratch.

EDIT 2:

After further testing, I can confirm that the problem is the differences in the fulltext search catalogs between the two databases. Both databases used the same BC14 databases as a starting point, but the one that does work was based on an older upgrade process that used ForceSync to bypass some errors, and thus is missing some table data. Oddly enough, the new database, which was upgraded without any ForceSync or errors, only has indexes for two of the 15 different tables that use the modern search feature. I'm unsure why only those two were covered, since almost all the tables here have table extensions with similar edits to the schema.

I was able to create a pivot table listing the differences between the two databases:

Count of IndexName	Column Labels
Row Labels	Old	New
Contact$437dbf0e-84ff-417a-965d-ed2bb9650972	16
Customer$437dbf0e-84ff-417a-965d-ed2bb9650972	21
Dimension Value$437dbf0e-84ff-417a-965d-ed2bb9650972	4
Fixed Asset$437dbf0e-84ff-417a-965d-ed2bb9650972	11
G_L Account$437dbf0e-84ff-417a-965d-ed2bb9650972	9
Item Reference$437dbf0e-84ff-417a-965d-ed2bb9650972	7	7
Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972	5
Item$437dbf0e-84ff-417a-965d-ed2bb9650972	21	21
Lot No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972	2
Nonstock Item$437dbf0e-84ff-417a-965d-ed2bb9650972	5
Post Code$437dbf0e-84ff-417a-965d-ed2bb9650972	5
Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972	2
Service Item$437dbf0e-84ff-417a-965d-ed2bb9650972	7
Stockkeeping Unit$437dbf0e-84ff-417a-965d-ed2bb9650972	5
Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972	18
Grand Total	138	28

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (11)

Answers (0)

Jun Wang 8,188 Super User 2025 Season 2 on at

Like (3)

Report

Modern Search doesn't work on OnPrem BC25 environment

for reference

https://github.com/microsoft/AL/issues/7870

Was this reply helpful? Yes No
Jakob Gillinger 58 on at

Like (3)

Report

Modern Search doesn't work on OnPrem BC25 environment

@Jun Wang The error code in that issue seems to be different - I do have the full text search enabled, but it still doesn't work.

Was this reply helpful? Yes No
Suggested answer

Jeffrey Bulanadi 8,451 on at

Like (2)

Report
Modern Search doesn't work on OnPrem BC25 environment

Hi Jakob,

You're absolutely right to suspect that the issue stems from missing full-text indexing on specific tables, even though the SQL Server feature itself is enabled. The command SELECT SERVERPROPERTY('IsFullTextInstalled') returning 1 confirms that the feature is available — but BC still needs to build full-text indexes on the relevant tables to support modern search.

Let’s break it down:

1. Why the error occurs despite full-text being installed
The error message
A CONTAINS or FREETEXT predicate can't be used on the table because full-text indexing is not enabled on that specific table.
means that while SQL Server supports full-text search, the target table lacks a full-text index.

BC does not automatically create full-text indexes during upgrade or installation

You must manually enable full-text indexing on the tables used by modern search (e.g., Customer, Vendor, Item)

2. How to enable full-text indexing on required tables
Here’s what you need to do:

Open SQL Server Management Studio (SSMS)

Identify the tables used by modern search (e.g., CRONUS$Customer, CRONUS$Item)

Right-click the table → Full-Text Index → Define Full-Text Index

Choose the appropriate columns (e.g., Name, Description)

Create or assign a Full-Text Catalog

Once the index is built, modern search should start working without errors.

3. Additional upgrade considerations
If you upgraded from BC14 to BC25:

Ensure that the modern search feature is enabled in BC (check Feature Management)

Confirm that permissions are correctly set for the SQL Server service account

Restart the BC service tier after enabling full-text indexing to refresh metadata

Helpful references:

Enable full-text search in SQL Server – Microsoft Learn
BC25 OnPrem full-text search error – Mibuso Forum

If you find this helpful, feel free to mark this as the suggested or verified answer.

Cheers
Jeffrey

Was this reply helpful? Yes No
Gerardo Rentería Ga... 23,665 Most Valuable Professional on at

Like (2)

Report

Modern Search doesn't work on OnPrem BC25 environment

Hi, good day
I hope this can help you, and give you some hints.

Tip #1270: Table or view is not full-text indexed

Best

GR

Was this reply helpful? Yes No
Jakob Gillinger 58 on at

Like (3)

Report

Modern Search doesn't work on OnPrem BC25 environment

@Jeffrey Bulanadi Thanks for the advice, I'm just not sure why these indexes wouldn't have been built by BC during the upgrade.

At no point does the documentation mention anything about manually creating indexes and I couldn't find anyone saying that's what they had to do to fix this issue, so I'm certain manually creating the indexes is something I should be doing. Even the Mibuso post you linked says restarting the SQL server fixed the issue, but that didn't work in my case either.

Is there some schema and naming convention for the indexes? I'm worried that when creating them manually, they'd be set up incorrectly and the feature would just run into a different error.

Also, if you're going to post answers from ChatGPT, please mark them as such. I find that they aren't very valuable for niche topics like this.

Was this reply helpful? Yes No
Suggested answer

Kamal Khakhkhar 1,202 on at

Like (3)

Report

Modern Search doesn't work on OnPrem BC25 environment

Hii There ,

for your reference i had one reference link you can check that. You can do this as per on new upgrade Some of changes were done for the fulltextsearch .

https://youtu.be/e4GrhSZi3y0?si=GuQvwP8_aqsXFxTF

If You Found answer mark it answered.

Thank You.

Kamal Khakhkhar

Was this reply helpful? Yes No
Jakob Gillinger 58 on at

Like (2)

Report

Modern Search doesn't work on OnPrem BC25 environment

@Kamal Khakhkhar Again, the problem isn't that the full text search feature isn't installed, the problem is that BC hasn't created the indexes for the tables.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 93,514 Super User 2025 Season 2 on at

Like (1)

Report

Modern Search doesn't work on OnPrem BC25 environment

If you install the CRONUS (Demo) database, does this problem occur? If not, it means that the problem is still with your database upgrade.

Hope this can give you some hints.

Thanks.

ZHU

Was this reply helpful? Yes No
Jakob Gillinger 58 on at

Like (2)

Report

Modern Search doesn't work on OnPrem BC25 environment

@YUN ZHU It definitely is a problem with the database - I tried using the database with the other server instance which did work, and I got the same error. The question now is why there's this discrepancy, and how I can fix it. Interestingly enough, the database that does allow for modern search is based on an older, incomplete upgrade that used ForceSync for most of the applications, resulting in missing table data. Why the indexes weren't created in the clean upgrade is beyond me - I'm already following Microsoft's upgrade guide.

Was this reply helpful? Yes No
Suggested answer

Jakob Gillinger 58 on at

Like (1)

Report

Modern Search doesn't work on OnPrem BC25 environment

I still don't know what caused the issue, but for the time being I've made a temporary fix using a SQL script:

-- Create temp table to hold index creation info

IF OBJECT_ID('tempdb..#FullTextIndexes') IS NOT NULL DROP TABLE #FullTextIndexes;

CREATE TABLE #FullTextIndexes (

TableName NVARCHAR(256),

Columns NVARCHAR(MAX),

KeyIndex NVARCHAR(256)

);

-- Insert all index definitions

INSERT INTO #FullTextIndexes (TableName, Columns, KeyIndex) VALUES

('[dbo].[Company$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Name] LANGUAGE [Neutral], [Name 2] LANGUAGE [Neutral], [Address] LANGUAGE [Neutral], [Address 2] LANGUAGE [Neutral], [City] LANGUAGE [Neutral], [Contact] LANGUAGE [Neutral], [Phone No_] LANGUAGE [Neutral], [Telex No_] LANGUAGE [Neutral], [Our Account No_] LANGUAGE [Neutral], [Fax No_] LANGUAGE [Neutral], [Telex Answer Back] LANGUAGE [Neutral], [VAT Registration No_] LANGUAGE [Neutral], [County] LANGUAGE [Neutral], [E-Mail] LANGUAGE [Neutral], [Home Page] LANGUAGE [Neutral], [Mobile Phone No_] LANGUAGE [Neutral], [Contact Graph Id] LANGUAGE [Neutral], [EORI Number] LANGUAGE [Neutral], [Registration Number] LANGUAGE [Neutral], [Format Region] LANGUAGE [Neutral]', '[Company$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Item$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral], [Description 2] LANGUAGE [Neutral], [Vendor Item No_] LANGUAGE [Neutral], [Block Reason] LANGUAGE [Neutral]', '[Company$Item$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Name] LANGUAGE [Neutral], [Name 2] LANGUAGE [Neutral], [Address] LANGUAGE [Neutral], [Address 2] LANGUAGE [Neutral], [City] LANGUAGE [Neutral], [Contact] LANGUAGE [Neutral], [Phone No_] LANGUAGE [Neutral], [Telex No_] LANGUAGE [Neutral], [Our Account No_] LANGUAGE [Neutral], [Fax No_] LANGUAGE [Neutral], [Telex Answer Back] LANGUAGE [Neutral], [VAT Registration No_] LANGUAGE [Neutral], [County] LANGUAGE [Neutral], [E-Mail] LANGUAGE [Neutral], [Home Page] LANGUAGE [Neutral], [Registration No_] LANGUAGE [Neutral], [EORI Number] LANGUAGE [Neutral], [Registration Number] LANGUAGE [Neutral], [Format Region] LANGUAGE [Neutral], [Mobile Phone No_] LANGUAGE [Neutral]', '[Company$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Contact$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Name] LANGUAGE [Neutral], [Name 2] LANGUAGE [Neutral], [Address] LANGUAGE [Neutral], [Address 2] LANGUAGE [Neutral], [City] LANGUAGE [Neutral], [Phone No_] LANGUAGE [Neutral], [Telex No_] LANGUAGE [Neutral], [Fax No_] LANGUAGE [Neutral], [Telex Answer Back] LANGUAGE [Neutral], [VAT Registration No_] LANGUAGE [Neutral], [County] LANGUAGE [Neutral], [E-Mail] LANGUAGE [Neutral], [Home Page] LANGUAGE [Neutral], [Company Name] LANGUAGE [Neutral], [First Name] LANGUAGE [Neutral], [Middle Name] LANGUAGE [Neutral], [Surname] LANGUAGE [Neutral], [Job Title] LANGUAGE [Neutral], [Initials] LANGUAGE [Neutral], [Extension No_] LANGUAGE [Neutral], [Mobile Phone No_] LANGUAGE [Neutral], [Pager] LANGUAGE [Neutral], [E-Mail 2] LANGUAGE [Neutral], [Registration Number] LANGUAGE [Neutral], [Format Region] LANGUAGE [Neutral], [Business Relation] LANGUAGE [Neutral]', '[Company$Contact$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Item Reference$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Item No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral], [Description 2] LANGUAGE [Neutral]', '[Company$Item Reference$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Item No_] LANGUAGE [Neutral], [Serial No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral]', '[Company$Serial No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Lot No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Item No_] LANGUAGE [Neutral], [Lot No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral]', '[Company$Lot No_ Information$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Service Item$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral], [Description 2] LANGUAGE [Neutral], [Location of Service Item] LANGUAGE [Neutral], [Vendor Item Name] LANGUAGE [Neutral], [Sales_Serv_ Shpt_ Document No_] LANGUAGE [Neutral]', '[Company$Service Item$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Code] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral], [Description 2] LANGUAGE [Neutral]', '[Company$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Fixed Asset$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral], [Description 2] LANGUAGE [Neutral], [Serial No_] LANGUAGE [Neutral]', '[Company$Fixed Asset$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Stockkeeping Unit$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Item No_] LANGUAGE [Neutral], [Vendor Item No_] LANGUAGE [Neutral]', '[Company$Stockkeeping Unit$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Nonstock Item$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Entry No_] LANGUAGE [Neutral], [Vendor No_] LANGUAGE [Neutral], [Description] LANGUAGE [Neutral]', '[Company$Nonstock Item$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Post Code$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Code] LANGUAGE [Neutral], [City] LANGUAGE [Neutral], [County] LANGUAGE [Neutral]', '[Company$Post Code$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$G_L Account$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[No_] LANGUAGE [Neutral], [Name] LANGUAGE [Neutral]', '[Company$G_L Account$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]'),

('[dbo].[Company$Dimension Value$437dbf0e-84ff-417a-965d-ed2bb9650972]', '[Dimension Code] LANGUAGE [Neutral], [Name] LANGUAGE [Neutral], [Totaling] LANGUAGE [Neutral]', '[Company$Dimension Value$437dbf0e-84ff-417a-965d-ed2bb9650972$$systemId]')

DECLARE @TableName NVARCHAR(256), @Columns NVARCHAR(MAX), @KeyIndex NVARCHAR(256), @Sql NVARCHAR(MAX);

DECLARE IndexCursor CURSOR FOR

SELECT TableName, Columns, KeyIndex FROM #FullTextIndexes;

OPEN IndexCursor;

FETCH NEXT FROM IndexCursor INTO @TableName, @Columns, @KeyIndex;

WHILE @@FETCH_STATUS = 0

BEGIN

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(@TableName))

BEGIN

SET @Sql = N'CREATE FULLTEXT INDEX ON ' + @TableName + ' (' + @Columns + ') KEY INDEX ' + @KeyIndex + ' ON [bc_ft_catalog];';

EXEC sp_executesql @Sql;

PRINT 'Created fulltext index on ' + @TableName;

END

ELSE

PRINT 'Fulltext index already exists on ' + @TableName;

FETCH NEXT FROM IndexCursor INTO @TableName, @Columns, @KeyIndex;

END

CLOSE IndexCursor;

DEALLOCATE IndexCursor;

DROP TABLE #FullTextIndexes;

This will work on a default BC25 database without any additional fields added to the catalog. Just remember to replace the instances of "Company" with your own company, and each company will need their own set of indexes.

Was this reply helpful? Yes No