Use SQLServer to query uploaded file content _ MySQL

Use SQLServer to query the uploaded file content bitsCN.com

As work requires, you need to query the content of the uploaded file. By using MSDN, we learned that the Windows index service can retrieve the full text of a file and query it through SQL Server. This article combines the two to achieve full-text retrieval of uploaded files.

Solution description:

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

Go to the Computer Management program (right-click 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 (create a New Catalog)

Set the newly added Catalog

(1) enter the name of the catalog (this name needs to be associated with the database. pay attention to the name. Here we assume that

(2) click Browse to go to the selection index service running directory form.

(3) select the Index run directory created earlier (any place)

(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 (New 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:

Execute the following stored procedure in the 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 the 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.

Call of ms SQL 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.

Other solutions:

In the SQL Server 2000 database, the full-text search function stores the file content with the image field, and adds a field to store the file type, then, when you create a full-text index in the Enterprise Manager, match the two fields

BitsCN.com