1

I have a table which has various columns and 2 of them are for full-text search:

  1. file_data as varbinary(max)
  2. file_extension as nvarchar(8))

I also have a unique index on the table. I then created a catalog as follows:

CREATE FULLTEXT CATALOG mycatalog
GO

CREATE FULLTEXT INDEX ON mytable
(  
    file_data
        TYPE COLUMN file_extension
        Language 1033
)  
KEY INDEX PK_mytable ON mycatalog
WITH CHANGE_TRACKING AUTO
GO

I then proceeded by inserting PDF files as follows:

DECLARE @pdf VARBINARY(MAX)

SELECT @pdf = BulkColumn
FROM OPENROWSET(BULK N'C:\path\filename.pdf', SINGLE_BLOB) AS file_data;

INSERT INTO mytable (file_data, file_extension)
VALUES (@pdf, '.pdf')

When I do a full-text search using FREETEXT I do not get results. So I run this view:

SELECT * FROM sys.dm_fts_index_keywords(db_id('mydb'), object_id('dbo.mytable'))

And it is empty. I have also run:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus') AS [MergeStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulateCompletionAge],
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulateStatus],
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus') AS [ImportStatus]
FROM sys.fulltext_catalogs AS cat

And all values are 0 apart from PopulateCompletionAge which is a big number. So I tried to do a manual population with:

ALTER FULLTEXT INDEX ON mytable START FULL POPULATION;

And I got this message:

Warning: Request to start a full-text index population on table or indexed view 'mytable' is ignored because a population is currently active for this table or indexed view.

Am I missing a step on setting up / populating the catalog?

Ivan-Mark Debono
  • 15,500
  • 29
  • 132
  • 263
  • In your database, if you go to Storage > Full Text Catalogs and right-click the one in question and open the Properties dialog. What is the population status and item count just after inserting the PDF? – Dan Def Jan 09 '19 at 08:48
  • @DanDef It's 0 ie. idle. – Ivan-Mark Debono Jan 09 '19 at 08:49
  • If you manually rebuild the catalog, does the data appear once the rebuild/population is complete? – Dan Def Jan 09 '19 at 08:52
  • 1
    Does https://stackoverflow.com/q/25388250/11683 help? – GSerg Jan 09 '19 at 08:55
  • FYI, PopulateCompletionAge is always a big number, it is "The difference in seconds between the completion of the last full-text index population and 01/01/1990 00:00:00". See here: [Microsoft Doc](https://learn.microsoft.com/en-us/sql/t-sql/functions/fulltextcatalogproperty-transact-sql) – Matt Roy Oct 04 '21 at 19:48

0 Answers0