Hi,
I'm doing a search function for recipe database and have the query:1 SELECT K.RANK, tRecipe.sHeadline, tRecipe.sIngredients, tRecipe.sImagePath
2 FROM tRecipe
3 INNER JOIN
4 FaktaRecipe ON tRecipe.iRecipeID = FaktaRecipe.iRecipe
5 INNER JOIN
6 CONTAINSTABLE(tRecipe, *, 'ISABOUT (chick* WEIGHT(0.2))') AS K
7 ON tRecipe.iRecipeID = K.[KEY]
8 WHERE (FaktaRecipe.iRecipeFakta = 5)
9 ORDER BY RANK DESC I want to return records like 'chicken pie' etc, hence using the wildcard in chick* BUT the wildcard doesn't work! It works fine if I use the whole word 'chicken' but of course a user won't always do that... I am using SQL server 2000. Any ideas? - I'm tearing my hair out! Thanks,Paul
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I am with the response time for a simple count on a fulltext search that is too slow.
Even using the most simple query on a good server (64 bit Dual Opteron 4GB Ram with high speed 16 raid disk storage)):
select count(*) from content_books where contains(searchData,'"english"') Takes 4 seconds to count the avg 500.000 resultsI have removed all the joins with real table data so that the query is only inside the fulltext engine..
I would expect this to be down to 4 milli seconds. Isn't it just getting the size of the "english" word result index?
It seems the engine is going through all the results because if a do a more complex search that returns less results the performance is better.
Any clues of how to do this faster? I never read the thousands of records BUT i need to count them...
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns. Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank. I'm not sure if this is possible. So far, I have: 1 SELECT RANK, field_1, field_2, ..., field_m FROM 2 ( 3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1, 4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable 5 WHERE KEY = T_1.field_i 6 UNION 7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2, 8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable 9 WHERE KEY = T_2.field_i 10 UNION 11 . 12 . 13 . 14 UNION 15 SELECT RANK, field_1, field_2, ... field_m FROM T_n, 16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable 17 WHERE KEY = T_n.field_i 18 ) 19 ORDER BY RANK DESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
I would like to use LINQ to generate a sql statement that does not use LIKE, but rather uses CONTAINS. Is this possible? If not, my second question is whether or not I can parameterize a SqlCommand that uses CONTAINS. For example the following statement works just fine when I pass in the parameter via SqlCommand.Parameters.AddWithValue()SELECT * FROM [event] WHERE CONTAINS(comments, @searchTerm1)However, the following results in a variable not defined error.SELECT * FROM [event] WHERE (comments LIKE @searchTerm1)Any ideas? Thanks for your help.
I'm trying to write a query to associate to an alternate key. Cansomeone provide the right syntax/keyword I need to accomplish this?Here's the line that keeps giving me the error (Operand type clash:uniqueidentifier is incompatible with int):JOIN CONTAINSTABLE(VocabularyFrench, *, 'FORMSOF(INFLECTIONAL,"remorques")') SRCH ON P.PRODUCT_ID = SRCH.[KEY]Thanks,Jeff
i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.can i do this in the same command? what is the syntax?my code :select table1.field1,a.rank,b.rank,c.rankfrom table1containstable(table1,field,'"word"') as aor containstable(table1,field,'formsof(inflectional(word)') as b)or freetexttable(table1,field,'word') as cwhere table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]the above syntax is wrong. i tried ',' instead of 'or' but the results were not right. thanks
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the following query to search all columns in the 'dashboard'table for the value 'Meets':SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]I have multiple records that contain the word 'Meets', but none areshowing up as a result of this query. Any ideas?Also, anytime I use a space in my search condition (Meets Expectationsinstead of Meets) I am getting an error:Syntax error occurred near 'Expectations'. Expected ''''' in searchcondition 'Meets Expectations'.Any ideas?Thanks in advance.
I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....
Can you not use a variable as a column list?? I have a variable for search criteria and it works fine...
Here is my syntax
containstable([tablename],@columnlist,@srch)
I have been looking online and can't seem to find anything that says I can or cannot use a variable.
Can somebody help me in identifying the difference between FREETEXTABLE and CONTAINSTABLE statements?
I have recently started experimenting with the free text search and I feel that FREETEXTTABLE can look for the search string into multiple columns by breaking it into tokens.
For example
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl ON FT_tbl.ID = key_tbl.[key]
Now in my case €œ80€? is in PAON column and €œRidge Avenue€? is in STREET column. Among the resulting rows it also displays the required row. If I try to achieve this with CONTAINSTABLE using
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN containsTABLE(temp, (saon, paon, street, postcode), '80 ridge avenue') as key_tbl ON FT_tbl.ID = key_tbl.[key]
It gives me error Syntax error near 'ridge' in the full-text search condition '80 ridge avenue'.
If I make search string as €˜€?80 ridge avenue€?€™ instead of €˜ridge avenue€™ then it doesn€™t give me error but also displays no results because 80 ridge avenue does not appear as whole in any single column. If I search by only specifying €œridge avenue€? in search string then I get a full list where street is ridge avenue.
My question is Does FREETEXTTABLE can search in all listed column by breaking the search string into tokens and CONTAINSTABLE only in one as whole?
I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.
I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY. This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML". The generated query looks a little like :-
Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK from DOCS DOC inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID
This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....
My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest. e.g. for the example for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36 for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36 for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....?? This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong? If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .
This question is about rankings returned from the ISABOUT function. In the following example, select * from documents as DOC inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key" inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key" order by doc0.rank desc
The values I get from the doc1/2/3/4.RANK columns dont seem right. In this example,
doc0.rank = 133 doc1.rank = 150 doc2.rank = 330
doc3.rank = 924
doc4.rank = 0
These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?). Once again, is there something I missed or am I understanding it wrong?
Thanks in advance for any help into understanding the whys of this...
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column. What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located. Can anybody help me achieve this? My code is:
Code Block SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK FROM Sources AS FT_TBL INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC;
I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow: =================== SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
Hello,I am using SQL 2005 and SQL Server Management Studio.How can I activate FullText in my database?Or should I do this only for the tables where I need it?How can I do this?Thank You,Miguel
I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))
I have then created a full text catalog on that table and populated it.
Then i wrote the following query "select contents from tblcatalog where contains(contents,'sample data')" It is fetching 0 records even though u have 5 records with entries "sample data"
Apologies if this is not the correct place to put this.I wrote this query to perform a fulltext search on tblPages.SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *,@searchTerm) searchTableWHERE [KEY] = tblPages.PageID ORDER BY RANK DESCWhere PageID is the primary key.The synax is correct but why does it never return any results?thanksmarc
Using CONTAINS, we can search for "never*" and we will find "nevertheless".However, I would like to do the inverse: "*less" should also find"nevertheless". Strangely, I can't find that in the doc and it does not seemto work.Am I getting something wrong? Or is this really not possible?Thanks a lot for your hints,Matthias HaldimannPS: This "*whatever" search may not look THAT useful in English, but it isvery important for searches within German nouns.
Can I get a documentation about full text capacity and limitation like memory utilization, number of catalogs per server, no of rows in each catlaog for SQL Server 2000.
Hi, I created the following table in which i created full text indexing for DocDatafld and TypeColumn was used as Type Column for DocDataFld field. I store Document name in the DocumentTitlefld and want to get the Document name and Document and want to display it in the front end application and give url for the document. Documentidfld bigint Documenttitlefld varchar MimeTypefld varchar DocDatafld varbinary(MAX) TypeColumn varchar and i am using the following query to get the contents, but not able to get the column values. select documenttitlefld,docdatafld from Documentdatatbl Freetext(DocDatafld,'C') Please help
I cna't seem to ungrey the fts option. I've checked that mssearch is running and using the stored procs to manage fts results in
Server: 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'm trying to do a fulltext search which returns the adjacent words also in the result, like u do a google search and it returns the paragraph containing the searched phrase.
Do users will still have access to the FullText catalog while the catalog is optimized (not rebuilt)?. My assumption is that they should have the access. Can anyone please confirm?.
I've got fresh Installed SQLExpress 2005 Adv. with a following: WinXP Pro, Developer PC, Admin/Full rights, Antivirus PC-Cilin was switched off for the installation.Enable User Instance - 0 (clear check box during installation)SQL Collation: Dictionary order case sensetive for1252 Char setSELECT @@language: us_englishcreate fulltext catalog testAPFullTextcatalog, Sp_help_FullText_catalogs: 5 testAPFullTextcatalog C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLFTData estAPFullTextcatalog 0 0create unique index indexName_Comment on dbo.FullTextSearchTestTable(RecordID) sp_configure: allow updates 0 1 0 0 clr enabled 0 1 0 0 cross db ownership chaining 0 1 0 0 default language 0 9999 0 0 max text repl size (B) 0 2147483647 65536 65536 nested triggers 0 1 1 1 remote access 0 1 1 1 remote admin connections 0 1 0 0 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 600 600 server trigger recursion 0 1 1 1 show advanced options 0 1 0 0 user instances enabled 0 1 0 0 user options 0 32767 0 0CREATE FULLTEXT INDEX ON dbo.FullTextSearchTestTable(Comment) KEY INDEX indexName_Comment ON testAPFullTextcatalog WITH CHANGE_TRACKING AUTO Result: Informational: No full-text supported languages found. Informational: No full-text supported languages found. Msg 7680, Level 16, State 1, Line 1Default full-text index language is not a language supported by full-text search.
I couldn't overcome the issue. Please help.
Alexei
P.S. May be Important: During installation (on Management Studio Express installation stage) a had to click twice the "Ignore" button for the following popUp message: "Could not write value to key: SoftwareClasses.xdropenWithProgIdsShared - verify, if you have sufficient access to hot key or contact your support.
We have a sql server 2000 active/passive cluster running on Windows Server 2003 and within the last 6 months have started getting the following errors which eventually cause the SQL Resource Group to failover.
An error occurred in the configuration monitor for instance <SQL Server Fulltext>: 80070005 - Access is denied.
SQL is at the latest service pack/hotfixes. (8.00.2187)
This cluster has been running fine for the past 3 years with no problems. Nothing has changed on the servers.
We've had Symantic Antivirus running on the servers for a long time but the FTDATA folder and the sql data and transaction logs are excluded and nothing has changed on the configuration.
We also run Diskeeper 2007 server on these servers but I can't see any problems with this.
Once it fails over it will run fine for several days and then the same errors will occur and it fails over again.
Any ideas on what might be causing this? I can't really find anything on the Microsoft site that matches this error.
Note: This has also just started happening on our test cluster which is an identical configuration.
I'm about ready to smash my computer. I've installed SQL Server 2005 Express Edition with Advanced Services on Win XP Pro SP2. I made sure to enable the Fulltext service at install. Installed to the default instance name, and made sure the fulltext service is running from the CM. I made sure to enable full text indexing on my database from the properties window. I can create the fulltext catalog, but not the index!
----
create fulltext index on [person.contact] (Lastname)
key index [PK_Person.Contact] on myfulltextcatalog
Informational: No full-text supported languages found.
Informational: No full-text supported languages found.
Msg 7680, Level 16, State 1, Line 1
Default full-text index language is not a language supported by full-text search.
----------------------------------------------
select * from sys.fulltext_catalogs
returns the catalog
----------------------------------------------
sp_fulltext_service 'update_languages'
Informational: No full-text supported languages found.
----------------------------------------------
select @@language
us_english
----------------------------------------------
select * from sys.fulltext_languages
Returns an empty table
----------------------------------------------
SELECT * FROM sys.configurations
ORDER BY name ;
1126 default full-text language 1033 0 2147483647 1033 default full-text language 1 1
----------------------------------------------
Does anyone have any idea what's going on? I've already removed and re-installed a couple of times to different directories, used different instance names, and still no luck. Any help would be greatly appreciated.
