SQL Server full-text search and query

You need to query the content of the uploaded file as required by the project. By using MSDN, we learned that the Windows Index Service can retrieve the full text of a file and query it through SQL Server. The project combines the two to achieve full-text retrieval of uploaded files.

Solution Overview:

1. Change the file name during file storage

2. Configure the Indexing Server and associate the Indexing Server with the ms SQL Server.

3. Modify the SQL statement and add the content of the full-text query statement to the query condition.

File storage method:

To facilitate storage and indexing, we store the uploaded files in a directory. To ensure that the uploaded files are not duplicated, we use GUID as the file name, and The GUID is associated with the database records. At the same time, the file suffix also retains the original file suffix, so that the Indexing Service can recognize this document.

Configure index service

Right-click the Computer Management Program "my Computer"-> "manage") and find the Index Service)

Configure the Indexing Service Function

1) Right-click the Index Service to bring up a menu

2) Select New> Catalog to create a New Catalog)

Set the newly added catalog

1) enter the name of the catalog. The name must be associated with the database.

2) Click Browse to go to the Selection Index Service Running directory form.

3) select any place in the index running directory created earlier)

4) Click OK to complete this operation.

After creating a directory number, you must add a directory to the directory.

1) Right-click the created catalog

2) Select New-> Directory to create a Directory)

Create INDEX DIRECTORY

1) Click Browse to go to the directory selection page.

2) Select the directory for full-text search

3) confirm to complete this operation

Start Index Service after creation

1) Start the Index Service

2) After the Index Service is started successfully, the index information indicates that the service has been started successfully.

Associate the Index Service with the SQL database:

Run the following stored procedure in SQL Query Analyer.

EXEC sp_addlinkedserver Dcs, -- Name of the connection server, which will be used for later Query

'Index Server ',

'Msidxs ',

'Dcci' -- Name of the newly created catalog in the Index Service

Method 1

1) Right-click and choose Index Service

2) select All Tasks-> Tune Perrformance

3) Select User-Defined

4) Click Customize to enter the performance adjustment form.

5) Move the attributes of Index and Querying to the rightmost.

6) Click OK to complete the operation.

Performance adjustment method 2

If the server does not need to index the entire System file, you can stop or delete the System catalog.

MSSQL call to Index Server

1. query the content of the connection server through SQL statements

Select q .*

FROM OpenQuery (dcs, -- connection server name

'Select FileName, Size, DocAuthor, path -- SQL statement in the connection Server

From scope ()

Where contains (''' and abc '')'

) AS Q

2. Because SQL statements do not support dynamic input strings, query strings must be spliced in C # code.

S. p.

You can perform full-text search in the database without saving the file outside the database. The specific solution is to use the full-text security search function in SQL Server 2000 to store the file content with the image field and add a new field to store the file type, then, match the two fields when you create a full-text index in the Enterprise Manager. However, from the perspective of the database/rate and the efficiency of database backup and recovery in the future, the design of storing files in the database is not very good.

1. Dangerous judgment on Microsoft SQLServer password management
2. How to Use SQLServer database to query the cumulative value
3. Analysis of Oracle and SqlServer Stored Procedure debugging and error handling
4. SQL Server statements and stored procedures
5. Microsoft updates the JDBC driver to support SQLServer 2005