neo6053
  •  neo6053
  • 53% (Neutral)
  • YAF Forumling Topic Starter
2010-10-25T10:55:05Z
I was migrating my db to other server. Somehow, i was unable to migrate the full-text search. Can anyone tell me how to recreate it.

Btw, i've disable the function of full-text search. What is the benefits of having it? I still can search for words inside the thread. What is the difference?
Sponsor
centur
2010-10-25T11:09:25Z
if you have source а yaf - there is a file
<somewhereonyourdrive>\yafsrc\YetAnotherForum.NET\install\mssql\fulltext.sql

If you dont - here the entire file content :


-- Enables FULLTEXT support for YAF
-- Must be MANUALLY run against the YAF DB

if (select fulltextserviceproperty('IsFulltextInstalled'))=1 and (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
	exec sp_fulltext_database N'enable' 
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) = 1
BEGIN
	if not exists (select * from dbo.sysfulltextcatalogs where name = N'YafSearch')
	BEGIN
		EXEC sp_fulltext_catalog N'YafSearch', N'create'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'create', N'YafSearch', N'PK_yaf_Message'	
		EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Message]', N'Message', N'add'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'activate' 
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_change_tracking'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Message]', N'Start_background_updateindex'

		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'create', N'YafSearch', N'PK_yaf_Topic'
		EXEC sp_fulltext_column N'[{databaseOwner}].[{objectQualifier}Topic]', N'Topic', N'add'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'activate' 
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_change_tracking'
		EXEC sp_fulltext_table N'[{databaseOwner}].[{objectQualifier}Topic]', N'Start_background_updateindex'
		
		-- enable in yaf_Registry as a default
		IF EXISTS ( SELECT 1 FROM [{databaseOwner}].[{objectQualifier}Registry] where [Name] = N'usefulltextsearch' )
			UPDATE [{databaseOwner}].[{objectQualifier}Registry] SET [Value] = '1' WHERE [Name] = N'usefulltextsearch'
		ELSE
			INSERT INTO [{databaseOwner}].[{objectQualifier}Registry] ([Name],[Value],[BoardID]) VALUES (N'usefulltextsearch','1',NULL);
	END
END
GO


I think its pretty self-explained.

You can read about benefits here http://msdn.microsoft.com/en-us/library/ms345119 (SQL.90).aspx
Here the short quote from link above:
Quote:


Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a SQL Server database. Unlike the LIKE predicate, which only works on character patterns, full-text queries perform a linguistic search against this data, operating on words and phrases based on rules of a particular language.

The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query (for example, '%cencini%') against millions of rows of text data can take minutes to return; whereas a full-text query (for 'cencini') can take only seconds or less against the same data, depending on the number of rows that are returned.


neo6053
  •  neo6053
  • 53% (Neutral)
  • YAF Forumling Topic Starter
2010-10-25T11:17:43Z
thank you very much. i have actually run that sql already. but it gives me error

Msg 15009, Level 16, State 1, Procedure sp_fulltext_table, Line 39
The object '[{databaseOwner}].[{objectQualifier}Message]' does not exist in database 'Forum_DB' or is invalid for this operation.
Msg 15009, Level 16, State 1, Procedure sp_fulltext_table, Line 39
The object '[{databaseOwner}].[{objectQualifier}Topic]' does not exist in database 'Forum_DB' or is invalid for this operation.


Thank you for the infor of full-text search too.
centur
2010-10-25T11:55:11Z
aww, I didnt noticed that...
Replace {databaseOwner} token on your Config.DatabaseOwner ("YAF.DatabaseOwner" from config or use "dbo" if not specified) and {objectQualifier} token on Config.DatabaseObjectQualifier ("YAF.DatabaseObjectQualifier" or use "yaf_")
manually. And run script again 🙂

About Us

The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

Powered by Resharper Donate with PayPal button

Project Twitter Updates

Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved