SQL Server 2000 FullText Search Service:How to set up and Query with Indexing Service

SQL Server 2000 FullText Search Service:How to set up and Query with Indexing Service
=====================================================================================
http://www.eggheadcafe.com/articles/20010422.asp

My Notes:
========
1):Create Database 'HelpCenter' with these tables:
---------------------------------------------------
USE [HelpCenter]
GO
/****** Object: Table [dbo].[faq_node] Script Date: 12/12/2008 15:34:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[faq_node](
[id] [int] IDENTITY(1,1) NOT NULL,
[parent_id] [int] NOT NULL,
[ref_id] [int] NULL,
[position] [int] NULL,
[type] [varchar](10) NOT NULL,
[name] [varchar](50) NOT NULL,
[description] [varchar](200) NULL,
[path] [varchar](100) NOT NULL,
[language] [varchar](10) NOT NULL,
[keyword] [varchar](50) NULL,
CONSTRAINT [PK_faq_node] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[faq_node] WITH NOCHECK ADD CONSTRAINT [FK_faq_node_faq_node] FOREIGN KEY([parent_id])
REFERENCES [dbo].[faq_node] ([id])
GO
ALTER TABLE [dbo].[faq_node] CHECK CONSTRAINT [FK_faq_node_faq_node]
---------------------------------------------------------------------
USE [HelpCenter]
GO
/****** Object: Table [dbo].[faq_q_and_a] Script Date: 12/12/2008 15:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[faq_q_and_a](
[qa_id] [int] IDENTITY(1,1) NOT NULL,
[top] [int] NOT NULL,
[hot] [bit] NOT NULL,
[question] [text] NULL,
[answer] [text] NULL,
[hits] [int] NOT NULL CONSTRAINT [DF_faq_q_and_a_hits] DEFAULT (0),
CONSTRAINT [PK_faq_q_and_a] PRIMARY KEY CLUSTERED
(
[qa_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
---------------------------------------------------------------------------
2): From Storage create this Catalogs:
=====================================
USE [HelpCenter]
GO
/****** Object: FullTextCatalog [faq_q_and_a] Script Date: 12/12/2008 15:36:31 ******/
CREATE FULLTEXT CATALOG [faq_q_and_a]
IN PATH N'd:\mssql\MSSQL\FTData'

----------------------------------
3): Add these Stored proedures:
===============================
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_hot_lst_xml] Script Date: 12/12/2008 15:37:57 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_hot_lst_xml]
(
@language VARCHAR(10)
)
AS



SELECT TOP 1 *
FROM faq_q_and_a q_and_a INNER JOIN
faq_node n ON q_and_a.qa_id = n.ref_id
WHERE (n.[language] = @language)
AND hot = 1
ORDER BY [top] ASC
FOR XML AUTO
----------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_node_del] Script Date: 12/12/2008 15:38:27 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_node_del]
(
@node_id INT
)
AS
DELETE FROM faq_q_and_a
WHERE qa_id IN
(
SELECT ref_id
FROM faq_node
WHERE [id] = @node_id
)
DELETE FROM faq_node WHERE [id]= @node_id
-----------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_node_flat_tree] Script Date: 12/12/2008 15:38:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_node_flat_tree]
(
@language VARCHAR(10) = NULL
)
AS
SELECT 1 AS Tag, NULL AS Parent,
node.[id] AS [node!1!id],
node.parent_id AS [node!1!parent_id],
node.[position] AS [node!1!position],
node.type AS [node!1!type],
node.language AS [node!1!language],
node.[name] AS [node!1!name],
node.[description] AS [node!1!description],
node.[path] AS [node!1!path]
FROM faq_node node LEFT OUTER JOIN
faq_q_and_a q_and_a ON node.ref_id = q_and_a.qa_id
WHERE (@language IS NULL OR language = @language)

--and id < 45
ORDER BY [node!1!parent_id], [node!1!id]
FOR XML EXPLICIT
-------------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_node_ins] Script Date: 12/12/2008 15:39:01 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_node_ins]
(
@node_id INT OUTPUT,
@parent_id INT,
@type VARCHAR(10)
)
AS
DECLARE @top INT
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(100)
DECLARE @language VARCHAR(10)




IF @type = 'q_and_a'
BEGIN
SET @top = (
SELECT ISNULL(MAX([position]), 0) + 1 AS position
FROM faq_node
WHERE (faq_node.parent_id =@parent_id) AND (type = 'q_and_a')
)
SET @name ='Question ' + CAST(@top AS VARCHAR(10))
END
ELSE
BEGIN
SET @top = (
SELECT ISNULL(COUNT(id), 0) + 1 AS position
FROM faq_node
WHERE (parent_id =@parent_id) AND (type = 'folder')
)
SET @name ='Category ' + CAST(@top AS VARCHAR(10))
END
INSERT INTO faq_node
(
parent_id, [position], type, [name], path, language
)
VALUES
(
@parent_id, @top, @type, @name,'',''
)
SET @node_id = @@IDENTITY

IF(@parent_id = 0)
BEGIN
SET @path = '/' + LTRIM(CAST(@node_id AS VARCHAR(10)))
END
ELSE
BEGIN
SET @path = (SELECT path FROM faq_node WHERE [id] = @parent_id)
SET @path = RTRIM(@path) + '/' + LTRIM(CAST(@node_id AS VARCHAR(10)))
END

SET @language = (SELECT language FROM faq_node WHERE [id] = @parent_id)


UPDATE faq_node SET path = @path, language = @language WHERE [id] = @node_id
------------------------------------------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_node_QuestionKeywordDetails] Script Date: 12/12/2008 15:39:19 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_node_QuestionKeywordDetails]
@Language varchar(5),
@questionKeyword varchar(50),
@path varchar(50) output,
@parent_id int output,
@ref_id int output
AS


SELECT top 1 @parent_id=parent_id, @path=path, @ref_id=ref_id from faq_node where language=@Language and keyword=@questionKeyword

------------------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_node_upd] Script Date: 12/12/2008 15:39:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_node_upd]
(
@node_id INT,
@ref_id INT = NULL,
@position INT = NULL,
@parent_id INT,
@name VARCHAR(50),
@description VARCHAR(200) = NULL,
@language VARCHAR(10) = NULL,
@keyword VARCHAR(50) = NULL
)
AS
UPDATE faq_node SET [position] = @position, parent_id = @parent_id, [name] = @name, [description] = @description, ref_id = @ref_id , keyword = @keyword
WHERE id = @node_id

IF (@language IS NOT NULL)
BEGIN
UPDATE faq_node SET language= @language
WHERE id = @node_id
END
-----------------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_get] Script Date: 12/12/2008 15:39:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_get]
(
@node_id INT
)
AS

SELECT id, parent_id, qa_id, position, name, description, question, answer, path, language, hot, [top], keyword
FROM faq_q_and_a q_and_a RIGHT OUTER JOIN faq_node node ON q_and_a.qa_id = node.ref_id
WHERE (node.id = @node_id)
--------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_get_xml] Script Date: 12/12/2008 15:40:03 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_get_xml]
(
@qa_id INT
)
AS
--SELECT *
--FROM faq_q_and_a q_and_a
--FOR XML AUTO
SELECT 1 AS Tag, NULL AS Parent,
q_and_a.qa_id AS [q_and_a!1!qa_id],
q_and_a.question AS [q_and_a!1!question!element],
q_and_a.answer AS [q_and_a!1!answer!element]
FROM faq_q_and_a q_and_a
WHERE (qa_id = @qa_id)
FOR XML EXPLICIT



UPDATE faq_q_and_a
SET Hits = Hits + 1
WHERE (qa_id = @qa_id)

------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_ins] Script Date: 12/12/2008 15:40:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_ins]
(
@qa_id INT OUTPUT,
@question TEXT,
@answer TEXT
)
AS
INSERT INTO faq_q_and_a (hot, [top], question, answer) VALUES (0, 0, @question, @answer)
SET @qa_id = @@IDENTITY
-----------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_lst] Script Date: 12/12/2008 15:40:28 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_lst]
(
@parent_id INT

)
AS
SELECT qa_id, parent_id, question
FROM faq_node node INNER JOIN
faq_q_and_a q_and_a ON node.ref_id = q_and_a.qa_id
WHERE (node.parent_id = @parent_id)
ORDER BY node.[position]

----------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_lst_all_descendents] Script Date: 12/12/2008 15:40:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[faq_q_and_a_lst_all_descendents]
(
@language VARCHAR(10),
@keyword VARCHAR(50)

)
AS



DECLARE @path VARCHAR(100)

SET @PATH = (SELECT TOP 1 path FROM faq_node WHERE language = @language AND keyword = @keyword)

SELECT TOP 1 qa_id, parent_id, question, path
FROM faq_node node INNER JOIN
faq_q_and_a q_and_a ON node.ref_id = q_and_a.qa_id
WHERE (node.path LIKE @path + '%')
ORDER BY NewID()
------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_lst_xml] Script Date: 12/12/2008 15:40:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_lst_xml]
(
@parent_id INT

)
AS
SELECT q_and_a.qa_id, node.[position], q_and_a.question, q_and_a.answer
FROM faq_node node INNER JOIN
faq_q_and_a q_and_a ON node.ref_id = q_and_a.qa_id
WHERE (node.parent_id = @parent_id)
ORDER BY node.[position]
FOR XML AUTO

------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_search] Script Date: 12/12/2008 15:41:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_search]
(
@search VARCHAR(500),
@language VARCHAR(500)
)
AS

SELECT q_and_a.qa_id, q_and_a.question, parent.name

FROM faq_q_and_a q_and_a INNER JOIN
CONTAINSTABLE(faq_q_and_a, *, @search, 100) KEY_TBL ON q_and_a.qa_id = KEY_TBL.[KEY] INNER JOIN
faq_node node ON q_and_a.qa_id = node.ref_id INNER JOIN
faq_node parent ON node.parent_id = parent.id
WHERE (node.[language] = @language)

ORDER BY rank DESC
FOR XML AUTO
--------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_q_and_a_upd] Script Date: 12/12/2008 15:41:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[faq_q_and_a_upd]
(
@qa_id INT OUTPUT,
@top INT= 0,
@hot INT= 0,
@question TEXT,
@answer TEXT
)
AS

IF(@hot > 0)
BEGIN
DECLARE @language VARCHAR(10)

SET @language = (
SELECT TOP 1 n.[language]
FROM faq_q_and_a qa INNER JOIN faq_node n ON qa.qa_id = n.ref_id
WHERE (qa.qa_id = @qa_id)
)

UPDATE faq_q_and_a
SET hot = 0
FROM faq_q_and_a qa INNER JOIN
faq_node n ON qa.qa_id = n.ref_id
WHERE (n.[language] = @language)
END

UPDATE faq_q_and_a SET [top] = @top, hot = @hot, question = @question, answer = @answer
WHERE qa_id = @qa_id
--------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_top_10_del] Script Date: 12/12/2008 15:41:34 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_top_10_del]
(
@language VARCHAR(10)
)
AS

UPDATE faq_q_and_a
SET [top] = 0
FROM faq_q_and_a qa INNER JOIN
faq_node n ON qa.qa_id = n.ref_id
WHERE (n.[language] = @language)
----------------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_top_10_ins] Script Date: 12/12/2008 15:41:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[faq_top_10_ins]
(
@qa_id INT,
@top INT
)
AS
UPDATE faq_q_and_a
SET [top]=@top
WHERE qa_id = @qa_id


----------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_top_10_lst] Script Date: 12/12/2008 15:41:58 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[faq_top_10_lst]
(
@language VARCHAR(10)
)
AS


SELECT TOP 10 [top], qa_id, question
FROM faq_q_and_a qa INNER JOIN
faq_node n ON qa.qa_id = n.ref_id
WHERE (n.[language] = @language)
AND [top] > 0

ORDER BY [top] ASC
-------------------------------------
USE [HelpCenter]
GO
/****** Object: StoredProcedure [dbo].[faq_top_10_lst_xml] Script Date: 12/12/2008 15:42:09 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[faq_top_10_lst_xml]
(
@language VARCHAR(10)
)
AS

SELECT TOP 10 *
FROM faq_q_and_a q_and_a INNER JOIN
faq_node n ON q_and_a.qa_id = n.ref_id
WHERE (n.[language] = @language)
AND [top] > 0
ORDER BY [top] ASC
FOR XML AUTO