Full text enabling error Nopcommerce 4.20

'PK_LocalizedProperty' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.


when want to enable Full text search.

To manually correct the inclusion of full-text search, run the following script:

ALTER PROCEDURE [FullText_Enable]
AS
BEGIN
  --create catalog
  EXEC('
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''nopCommerceFullTextCatalog'')
    CREATE FULLTEXT CATALOG [nopCommerceFullTextCatalog] AS DEFAULT')

  DECLARE @SQL nvarchar(500);
  DECLARE @index_name nvarchar(1000)
  DECLARE @ParmDefinition nvarchar(500);

  SELECT @SQL = N'SELECT @index_name_out = i.name FROM sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id) WHERE (i.is_unique=1 and i.is_disabled=0) and (tbl.name=@table_name)'
  SELECT @ParmDefinition = N'@table_name varchar(100), @index_name_out nvarchar(1000) OUTPUT'

  EXEC sp_executesql @SQL, @ParmDefinition, @table_name = 'Product', @index_name_out=@index_name OUTPUT
  
  --create indexes
  DECLARE @create_index_text nvarchar(4000)
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription])
    KEY INDEX [' + @index_name +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)

  EXEC sp_executesql @SQL, @ParmDefinition, @table_name = 'LocalizedProperty', @index_name_out=@index_name OUTPUT
  
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    CREATE FULLTEXT INDEX ON [LocalizedProperty]([LocaleValue])
    KEY INDEX [' + @index_name +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)

  EXEC sp_executesql @SQL, @ParmDefinition, @table_name = 'ProductTag', @index_name_out=@index_name OUTPUT

  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    CREATE FULLTEXT INDEX ON [ProductTag]([Name])
    KEY INDEX [' + @index_name +  '] ON [nopCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
END

Thanks, it worked for me

Hello,

I've also just enabled Full-Text Search on v4.20, and while I'm not seeing any errors, I'm not seeing any changes to search. Currently, only the Advanced Search will return any results.

Under General Settings> Full Text> I'm seeing "Full-Text is supported by your database", and I've selected Using CONTAINS and OR with prefix_term.

Under All settings (advanced)> commonsettings.fulltextsearchmode is set to "True". Is this correct?

Do I need to run the above script on the database to get Full-Text Search to work properly without selecting "Advanced Search"?

Thanks

Your setting is correct and it works with advance search only.

However, MSSQL doesn't have a very powerful full-text search features so while enabling this feature will improve search results.. it will not like a true full text search engine.

You may need additional search plugin to have a full-text search experience on the store.

Regards

nopAccelerate.com wrote:

I was already looking at this plugin. Will probably purchase it later today.

I appreciate your input and advice! :-)

WebMagi wrote:

So glad it's helpful. :)