Today, I've lost the power outage of "100 years" and read a day. In the evening, I made a full-text index. I decided to put my experience. I tried to write more, everyone learned together, welcome to correct!
1. Start the Microsoft Search service start menu -> SQL program group -> service manager -> drop basket -> Microsoft Search Service -> Start it
2, ../microsoft SQL Server / MSSQL / FTDATA / SQLSERVER / CONFIG / Directory built a non-empty NOISE.chs file non-empty NOISE.chs file, and some people say that it is empty noise.chs file, but I have every time Write a few letters that are useless.
3. Establish an environment to open the query analyzer -> Execute the following scripts: ---------------------------------- -------- Create Database Test --- Create Test Database Use Test --- Select Test Database Create Table Dali (ID INT Not Null Primary Key, MyImage Image, Filetype Varchar (255), FileNMAE VARCHAR (255 ) --- Create a Dali Table - Dali Table ID, MyImage, FileType three columns is necessary, because you want to have an image column index, you must have a primary key column, an image column, a column that stores file types - - We know that the file type in the Windows system is distinguished by the expansion, so the filetype column is also the extension of the file -------------------- ----------------------
Sp_fulltext_database 'enable' - Enable database sp_fulltext_catalog 'my_fulldir', 'create' - created a full-text directory called MY_FULLDIF
DECLARE @key sysname; select @ key = c.name from syscolumns a, sysconstraints b, sysobjects c where a.id = Object_id ('dali') And a.name = 'id' and a.id = B.ID and B .constid = C.ID and c.Name Like 'pk%' exec sp_ffulltext_table 'dali', 'create', 'my_fulldir', @ key ---- These two sentences are for full-text indexes, tag table
Sp_fulltext_column 'Dali', 'MyImage', 'Add', 0x0804, 'FileType' - This sentence is specified as a full-text search, FileType is a type column -------------- -------------------------------- 4, put a Word file that extends the DOC under the C drive, one Extended an Excel file called XLS, an extension of the web file named HTM, a total of 4 pictures called BMP, you can put it according to the actual situation!
5, insert the data to establish the following stored procedures --------------------------------------- --------- Create Procedure SP_TextCopy @srvname Varchar (30), @login varchar (30), @Password varchar (30), @dbname varchar (30), @tbname varchar (30), @colname varchar (30), @filename varchar (30), @whereclause var (40), @direction char (1) AS / * This is to insert files into the database using the TextCopy Tool, if there is a front tool to use the front desk development tool to file files Insert, here for demonstration * / declare @exec_str varchar (255) select @ exec_str = 'textcopy / s' @ srvname ' / u ' @ login ' / p ' @ password ' / d ' @ dbname ' / t ' @ TBNAME ' / C ' @ colname ' / w "' @ whereclause '" / f "' @ filename '" / ' @ DirectionExec master..xp_cmdshell @EXEC_STR --------- ------------------------------------------ Insert Dali Values (1,0X , 'DOC', 'strong DOC') --- The second column is 0x it is a 16-en-number corresponding image column, it is necessary, don't write null, the third column is the file type, extension
SP_TEXTCOPY 'Your server name', 'sa', 'your password', 'test', 'dali', 'myimage', 'c: / vigor Doc.doc', 'where id = 1', 'i '------- The parameters are: instance name, user name, password, database name, table name, image name, path, and file name, condition (you must guarantee only one line), i --- -------------------------------------------------- -------------------------------------------------- -------------- INSERT DALI VALUES (2,0x, 'bmp', 'picture') sp_textcopy 'Your server name', 'sa', 'your password', 'test ',' dali ',' myimage ',' c: / picture .bmp ',' where id = 2 ',' I '- Note condition is ID = 2
INSERT DALI VALUES (3,0x, 'xls',' Excel file ') sp_textcopy' Your server name ',' sa ',' your password ',' test ',' dali ',' myimage ',' C: / Excel file. Xls', 'where id = 3', 'i' - Note Conditions is ID = 3insert Dali Values (4,0x, 'htm', 'page') sp_textcopy 'Your server name,' SA ',' Your password ',' Test ',' Dali ',' MyImage ',' C: / Web .htm ',' Where ID = 4 ',' I '- Note Condition is ID = 4
---------- The above statement, to ensure the same type, the path is correct, the condition is only correct, it should be
6, fill full-text index
Sp_fulltext_table 'dali', 'start_full' --- The first parameter is a table name, the second parameter is completely filled in the full-text index of the start table
7, you can start your experiment
Select * from Dali Where Contains (MyImage, 'J Teacher ")
Select * from Dali Where Contains (MyImage, Teacher ')
------ End ------------ Debugging environment: SQLServer2000 Enterprise Edition, Windows2000 Advanced Server
