SQL Server 2005 Full-Text Thesaurus File
Aug 8, 2007
Hi! I am moving this from the Transact SQL forum as I am not getting a reply. Hope it's OK!
Hello: I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
View 1 Replies
ADVERTISEMENT
May 20, 2006
Hi,
I€™ve been trying to add keywords to the thesaurus used for SQL Server 2005 Full Text services but do not seems to get any improved results.
For example, I have a catalogue of article titles which contains titles such as €˜CRM€™ and then some others use the words €˜Customer Relationship Management€™, the ideal solution I would have thought was SQL Servers thesaurus support, I€™ve been though and added the values:
<expansion>
<sub>customer relationship management</sub>
<sub>crm</sub>
</expansion>
to the txGlobal.xml file in the SQL Servers FTData directory, I then amended by test query to use this clause:
FORMSOF(THESAURUS,"CRM")
The idea being that when I search for CRM it will bring back articles with Customer Relationship Management also. However I€™m just getting results for the same results as for the word 'CRM' and no expansion is taking place it would seem.
Would anyone be able to clarify what the best approach to making this work would be:
Do I need to restart the SQL Server FullText service after making any changes to the XML files?
(Done this, no sucess)
Do I need to restart the SQL Server service after making any changes to the XML files?
(Done this, no sucess)
Do you need to re-populate the full text catalogues after making a change the XML files?
(Done this, no sucess)
If this syntax correct when trying to utilise the thesaurus?
CONTAINSTABLE (Article_Metadata, *, €˜FORMSOF(THESAURUS,"CRM")€™ )
(It doesn;t give any errors, just no improved results over:
CONTAINSTABLE (Article_Metadata, *, €˜"CRM"€™ )
Any help would be appreciated.
Thanks
Ed
View 3 Replies
View Related
Aug 13, 2007
Hello: Please let me know if another forum is a better place to post! I have successfully enabled SQL Server 2005 Full Text (MSFTESQL) on my database, created the FT Catalog in Storage, and defined a FT Index for a table ( 1 table for testing).
I have also created an expansion and replacement entry in the Thesaurus (tsENU.xml) and removed the comments. I have restarted the server and the FTS Service, In addition, (per your comments to another user) I have used sqlcmd to confirm my default language of 1033.
I am unable to get any results from the Thesaurus file. I am able to run queries against the index using CONTAINS and FREETEXT. I get results - however I only get the same thing I would get from the CONTAINS portion of the query.
Also ..SQLSERVER2995MSFTEUser$.. has permission on the FTData folder.
// This query //
SELECT name, description FROM table
WHERE CONTAINS(description, ' FORMSOF(THESAURUS, blender) ')
// on this Thesaurus located in this C: folder MSSQL.1MSSQLFTData sENU.xml //
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<case caseflag="false"></case>
<expansion>
<sub>blender</sub>
<sub>chopper</sub>
<sub>grinder</sub>
<sub>food processor</sub>
</expansion>
</thesaurus>
</XML>
// returns this //
A search for blender returns all strings with the exact word blender in them - not even any plural forms. A search for blend or grind returns nothing. FREETEXT does a little better returning blender, blenders, blending. Still no grind,chop, or food process.
Appreciate your help, Thank you.
View 7 Replies
View Related
Aug 26, 2007
I am trying to get the full-text search thesaurus to work in Sql
Server 2005 Express edition. I live in the USA so I assume the
tx.ENU.xml is the appropriate file to modify. I used Notepad to
modify the tx.ENU.xml file as follows:<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>littre</sub>
<sub>leydig</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
</thesaurus>
</XML> I closed Management Studio and reopened to allow MSFTESQL service to restart. Then I ran these queries:SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'littre') *** returned 3 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, 'leydig') *** returned 169 rows ***SELECT FullDocNo
FROM FullDocuments
WHERE CONTAINS(SectionText, ' FORMSOF(THESAURUS, littre) ') *** returned 6 rows ***
Thus the Thesaurus does not seem to be working since it should
have returned at least 169 rows. I rebooted my entire system to
make sure Sql Server is starting fresh. Any help in sorting this out will be greatly appreciated.
View 1 Replies
View Related
Nov 13, 2007
Does anyone know where I can get a Thesaurus (or list) of People's Names and their relationships?
For Example:
Bill = Billy = Billie = Will = Willie = William
Sue = Susie = Susan = Suzanne
I can build the needed xml file to become a Thesaurus for MSSQL Full Text Searching. But I can't find a list of related people names anywhere.
Any help would be great! Thanks!
Lyam
View 3 Replies
View Related
Oct 5, 2006
Dear All,
I am not able to let thesaurus in SQL Server 2005 works.
My full indexed table has 4 records as follows:
tony
roby
mary
antony
The following is my tsENU.xml file:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics = false/>
<expansion>
<sub>Tony</sub>
<sub>Roby</sub>
<sub>Mary</sub>
</expansion>
</thesaurus>
</XML>
After I execute the following query:
SELECT * FROM Person WHERE CONTAINS(*,'FORMSOF(Thesaurus,"Tony")')
the result is only the record containing 'Tony' but I need Tony, Roby and Mary to be treated as synonimus and for that reason the result should be the 3 records containing all the 3 names.
Which is my mistake?
thanks and regards
Buzz
View 19 Replies
View Related
Jan 5, 2008
in object explorer ,do right-click on database and is selecting preoperties and is selecting "files" page "use full-text indexing" ckeck box is disable.
how can enabled this check box?
thanks , mohsen
View 1 Replies
View Related
Apr 8, 2007
Hi,
I am putting this question here but I am not limiting it to sql server 2005 express edition.
I am developing an app on a local machine (winxp with sql server 2000 personal edition) however I came to find out that full-text does not work in this setup unless I use a server type machine.
This fouls up my development somewhat and I would like to know if there is
a) a work around for my sql server 200 setup
b) does full-text serach work in sql server 2005 express edition which I have installed on my PC ?
Thanks for any input on this issue.
Tuka
View 2 Replies
View Related
Feb 8, 2007
I installed SQL Server 2005 express with advanced services which is supposed to include full-text search capability but I can't get it to work. When I try to create a full-text catalog it gives me an error because it does not think the full-text service is installed or loaded. I can't seem to find a reference to the full-text search feature to enable or install it. any ideas?
View 3 Replies
View Related
Feb 5, 2008
the sql server documentation states that the use of wildcards is allowed by placing an '*' at the end of the search term. I can get this to work OK in the SQL Server 2005 query window, heres an example
select ID, SUBSTRING(Title, 1, 100) AS Title, Implemented, Published from Table1 where contains(title,'"Therap*"') ORDER BY Title
this works OK and returns a list ot titles with the word Therapy in the title
Im trying to implelemnt this functionalty in a web app with C#. The string is passed to a stored procedure. How on earth do I pass in the quotes ??
Ive tried building the string as normal then adding single quotes on the end, so I get something like
retval = txt + "*"; //txt contains the partial word im searching for, then add the wildcard
then retval = "'" + retval + "'"; // add the single quotes
and pass txt as a string parameter to my stored procedure. It doesnt work. Can anyone tell me what im doing wrong ??
the same query works fine in the SQL query window.
View 7 Replies
View Related
Dec 16, 2005
I just installed SQL Server 2005 and need to create a full-text indexing. I looked up how to do it, but the full-text indexing option is ghosted so i don't even have the option to enable it...any ideas? I tried searching for hours with no luck.
View 1 Replies
View Related
Jan 23, 2008
Hello, I have read on the multiple places that filter for full text search of PDF files using FTS2005 is included in the Reader 8 etc. However, I have not found any document or instruction etc on adobe documents, microsoft documents or web that details on how to actually configure the filter. Please help. thanks
Kumud
View 4 Replies
View Related
May 24, 2006
Newbie questions.
1. Can SQL Server 20005 Express do full text searches?
2. If not, is there a way to use SQL Server 20005 Express to search a database column containing text data type?
Using Visual Basic 2005 Express, I would like to do a simple search with a search textbox and button that will return the entire contents of a field of database text when one or more words in the search text box are in the field of text in the database.
I have been playing in Visual Basic 2005 Express and using SQL queries (SELECT, FROM, WHERE) to output to DataGridView controls by using ID columns as filters in the query, etc. This I can do. But I have not been able to use a word or phrase in the search textbox as a filtered query to output the entire database field of text which contains the search word or phrase in the search textbox.
Thanks for any help in getting me started with this.
View 4 Replies
View Related
Sep 3, 2007
Hi guys,
I am getting a really weird error message when executing a full-text query on SQL server 2005:
-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider."
-------------------------
Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.
The query is not particular complex:
SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID
It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.
Has anybody come across this error before? Any ideas on how I could fix it?
View 14 Replies
View Related
Sep 8, 2007
Using SQL Server 2005 Express (Advanced SP2) I have created a Full-Text Search application in VB for distribution on CD for single PCs. Works fine on my local machine during development.
Although the SQL Server 2005 Express edition can be distributed freely, it does not seem to support Full-Text searches in the distributed version. Is this true? Or am I missing something with my deployment?
If I need another version of Sql Server for distribution of a Full-Text Search app, how do I go about obtaining the proper DB and permission for distribution? The DB size is about 600 MB.
View 8 Replies
View Related
Jan 21, 2007
SQL Server 2000 is pretty well documented with the limit of 256 (see http://msdn2.microsoft.com/en-us/library/aa214780(SQL.80).aspx) but I can find no documentation anywhere that discusses the limit on SQL Server 2005.
Is there one ?
Please help !
Thanks
View 3 Replies
View Related
Apr 2, 2007
Hi everyone,
Can someone help me out in providing guidelines towards how to do full text indexing using sql server 2005 express edition?
Thanks in advance
Regards,
Dave
View 1 Replies
View Related
Jan 28, 2008
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies
View Related
Dec 12, 2007
Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
Thanks in advance
View 1 Replies
View Related
Aug 1, 2007
hello.
is there a rational explanation for which after some select statements, the rank returned by the full-text search engine is 0, knowing that just after the repopulation the rank is displayed correctly?
in other words, time and usage messes up the ranking. why?
thanks!
View 4 Replies
View Related
Mar 29, 2006
What is the equivalent of the SQL Server 2000 Full-Text Search Service in SQL 2005?
I need to know cos i got a forum app implementing this in SQL 2000 but my company is using SQL 2005 Enterprise.
cos i cannot find this option in sql 2005.....
View 1 Replies
View Related
Aug 10, 2006
I have upgraded a SBS 2003 MSDE database (instance MSSQL$SHAREPOINT) to SQL Server 2005 Express Advanced Edition. This worked without a problem even when I enabled all the options for the upgrade including Full-text search.
I now want to have Full-text search on this instance of SQL 2005 with database name of STS_EVEREST_1.
I first tried to use the T-SQL command of "CREATE FULLTEXT CATALOG BBVisionCatalog AS DEFAULT;"
I now know that the original database was created under SQL 2000 and therefore I needed to use SQL 2000 commands. So I used the following script:
USE STS_EVEREST_1
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'BBVisionCatalog', 'create';
It produced the following ERROR messages:
(1 row(s) affected)
Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I checked to see if the Microsoft Search Service was running. It was running.
Anyone know how I can fix this?
Regards
Rolf Pfotenhauer
email: rolfpf@yahoo.com.au
View 9 Replies
View Related
Apr 28, 2008
I've searched the forum and saw that this question has been asked several times. However, none of the responses point to any documentation about this limit. We've got different groups bickering over this ("There is no limit!" "Yes there is!" "No there isn't!") and it would help to have a link on the MSDN or a response from Microsoft to this question to put this to rest once and for all. Also, if there is a limit, is that per Sql Server instance or per physical server?
Secondly, we currently have over 400 catalogs. We've noticed it takes 4-5 minutes for new data to show up in search results. Does the number of catalogs affect performance, or is it the amount of data, irrespective of catalog count?
Thanks
View 5 Replies
View Related
Feb 25, 2008
Has anyone created a Thesaurus file that is using it? Specifically, has anyone set up a First Name thesaurus file and is it possible to get a hold of it. Why reinvent the wheel if someone has already done this. I am creating a database that needs to return records for fuzzy matches on first names. I don't know what all forms of the names can be such as Robert, Bob, Bobby, Bert or Sue, Suzie, Susie, Susan, Suzanne, etc. and I am betting someone has created something already that can be a starting point at least.
What about the Thesaurus Word currently uses? Does anyone know what the physical file name and location is of the thesaurus file Word uses?
Any help or direction is appreciated.
Thanks
Paul
View 1 Replies
View Related
Jan 30, 2012
I remembered that the Thesaurus that comes with SQL Server is essentially empty.Is there a Thesaurus that can be downloaded for US English so I won't have to sit there with Roget's doing data entry for a few weeks?
View 9 Replies
View Related
May 11, 2006
I have several file in .doc, .xls, .pdf and would like to know if SQL Server can do a full-text indexing on these files? These are files are stored on different locations in th harddisk.
eg.
c: empmywork.doc
c: empscheduleschedule1.xls
c:showroomdatasheet1.pdf
View 5 Replies
View Related
Jul 19, 2007
I am having a problem restoring to a new or different database than the backup file being restored was created from. I understand the Move commands, and have figured out how to get this to work by mapping the data and log files from the backup file to the files defined for the restore target database.
The problem is with the full text catalog that is part of the backup.
Here's the T-SQL I'm executing, and the error I receive:
T-SQL:
RESTORE DATABASE New_DB
FROM DISK = 'C:ackupOld_DB.bak'
WITH RECOVERY, REPLACE,
MOVE 'Old_DB' TO 'C:SQLDataNew_DB.mdf',
MOVE 'Old_DB_log' TO 'C:SQLDataNew_DB_log.ldf'
Error:
Msg 1834, Level 16, State 1, Line 1
The file 'C:SQLDataFTDataftKeyWords0007' cannot be overwritten. It is being used by database 'Old_DB'.
Msg 3156, Level 16, State 4, Line 1
File 'sysft_ftKeyWords' cannot be restored to 'C:SQLDataFTDataftKeyWords0007'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Not sure what to do about this, any help would be greatly appreciated!
View 2 Replies
View Related
Mar 16, 2005
Hi,
I am having difficulty in indexing a Table when it contains a PDF file in an Image column. Basically I have a table with a unique id, some other fields, and an Image column. The Image column contains uploaded files from a web application. Full-text indexing works fine for all types of files that I have uploaded into this table which includes (.doc, .xls, .ppt) but I get the following message in my Event Viewer if one of thee files is a PDF file.
One or more documents stored in the Image columns with extension '.pdf' did not get full-text indexed because loading the filter failed with error '1x01'.
I have searched Microsoft's site and implemented their prescribed fixes but none has worked including installation of SP3 of Sql Server and/or modifying the registry key to run the Sql server indexing procedure as a single threaded process.
Has anyone experienced this problem? Please help.
Thanks
Kamal
View 1 Replies
View Related
Aug 6, 2007
Hi,
I am developing a database search using full-text search functionality of SQL server 2005.For the same I wish to permit some noise words in my search.
Can someone guide me how to allow including these noise words in search by editing noise file or by some other means.
View 3 Replies
View Related
Sep 12, 2007
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
View 5 Replies
View Related
Apr 21, 2015
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies
View Related
Jul 5, 2006
Hello everyone,
I've got a little question - wich drives me absolutely nuts by now :).
I'm trying to optimize my full-text search since a while now.
Scenario:
Full-Text Table:
id - int - primary key
srcID - varchar(30)
srchField - varchar(150) - FULL-TEXT INDEXED
src - varchar(15)
updateStamp - timestamp
Records in Table: ~ 4480000
To get my results I have to link the srcID-Field to another table.
But for performance Testing, two queries
LIKE VERSION:
SELECT srcID FROM fulltexttable WHERE srchField LIKE '%an%' GROUP BY srcID ODER BY srcID DESC
1. Running Time: 5 seconds, returns 921315 rows
2. Running Time: 5 seconds, returns 921315 rows
FT VERSION:
SELECT srcID FROM fulltexttable WHERE CONTAINS(srchField,' "an*" ') GROUP BY srcID ODER BY srcID DESC
1. Running Time: 14 seconds, returns 443676 rows
2. Running Time: 8 seconds, returns 443676 rows
If i do a search wich returns less records, full-text is a lot faster - but on big recordcounts it seems the LIKE query is faster? Is that possible???
Anyone any ideas?
Thanks in advance
View 1 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related