I have a front-end search set up that takes the users input and runs a
stored procedure that uses FREETEXTTABLE to get the results. I want it
so that when a user types in a search that has words grouped together
inside quotes (i.e. "sql server") it does an AND instead of an OR. I
know that CONTAINSTABLE can have this kind of logic but I was wondering
if there is a way to handle this without having my script (ASP)
building the search phrase by parsing the user input.
Any ideas? Or has someone done this and have some advice.
Thanks.
But if it does an AND it will not be the same as "sql server" which searches
for the two words occurring adjacent to each other.
Here is a proc which you can set whether you want the entire phase, an OR,
or an AND.
CREATE PROCEDURE SearchSQL (@.stringin varchar(200), @.BooleanType int= NULL)
AS
-- a @.boolean type of null means a phrase based search
-- a @.boolean type of 0 means an OR type search
-- a @.boolean type of 1 means an AND type search
DECLARE @.holdingString VARCHAR(2000)
DECLARE @.whitespace INT
DECLARE @.boolean VARCHAR(10)
--returning a syntax message if no search phrase is passed
IF LEN(@.stringin)=0
BEGIN
PRINT 'usage is SimpleSQLFTSSearch ''Your Search Phrase goes here'''
RETURN -1
END
SET @.boolean=case WHEN @.booleantype=1 THEN char(34)+' OR ' + char(34)
WHEN @.booleantype=2 THEN char(34)+' AND ' + char(34)
ELSE ' ' END
DECLARE @.counter INT
DECLARE @.posold int
DECLARE @.posnew int
SET @.holdingstring='SELECT * FROM authors AS a JOIN
CONTAINSTABLE(authors,*,'''+char(34)
SELECT @.whitespace=LEN(@.stringin) - LEN(replace(@.stringin,' ',''))
SELECT @.posold=0
SELECT @.posnew=Charindex(' ',@.stringin)
WHILE @.whitespace >=0
BEGIN
IF @.whitespace=0
BEGIN
SELECT
@.holdingString=@.holdingString+SUBSTRING(@.stringin, @.posold+1,LEN(@.stringin)-@.
posold+1)+char(34)+char(39)+',200) AS t ON '
END
ELSE
BEGIN
SELECT @.holdingString = CASE WHEN LEN(SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1))>0 THEN @.holdingString+SUBSTRING(@.stringin,@.posold+1,
@.posnew-@.posold-1)+@.boolean ELSE @.holdingstring END
SELECT @.posold=@.posnew, @.posnew=Charindex(' ',@.stringin, @.posold+1)
END
SELECT @.whitespace=@.whitespace-1
END
SELECT @.holdingString = @.holdingString + 't.[KEY]=a.au_id ORDER BY RANK
DESC'
--PRINT @.holdingstring
EXEC(@.holdingstring)
RETURN @.@.rowcount
Usage is:
DECLARE @.returncode int
EXEC @.returncode=SearchSQL 'this is a test'
PRINT @.returncode
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JT" <jtreuting@.gmail.com> wrote in message
news:1112316885.228212.171380@.f14g2000cwb.googlegr oups.com...
> I have a front-end search set up that takes the users input and runs a
> stored procedure that uses FREETEXTTABLE to get the results. I want it
> so that when a user types in a search that has words grouped together
> inside quotes (i.e. "sql server") it does an AND instead of an OR. I
> know that CONTAINSTABLE can have this kind of logic but I was wondering
> if there is a way to handle this without having my script (ASP)
> building the search phrase by parsing the user input.
> Any ideas? Or has someone done this and have some advice.
> Thanks.
>
Showing posts with label freetexttable. Show all posts
Showing posts with label freetexttable. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
FreeTextTable Search limitations
Hi,
I'm currently writing a search system in ASP with Javascript connected in to
SQL to search a product database. I'm using the FreeTextTable command to
search a number of fields and bring back results in rank order which is
working fine. However when the user submits a word in the noise list or
leaves the search blank and submits SQL returns a nice friendly ODBC
'80040e14' error.
I've read in FAQs and other posts etc to clear the noise filter and just
leave a space but if the user enters a space then hits search we have the
same issue. I'm not happy with that being the resolution and client side
code to correct this would have to cover the blank, space or multiple space
querystring. Instead of this is there not a way in the stored procedure to
throw something else back before returning what ever it does to bring up the
ODBC error? I'm new to stored procedures so not sure if there would be a way
to do this breaking out from a statement if it is yeilding a nasty ODBC
error. If anyone else has any suggestions or examples as to how they use the
FreeTextTable predicate to implement a search system through an ODBC driver
please let me know.
Regards
Nick Scott
MCSE 2003
Do a replace, replacing these illegal characters. Then check to see if the
resulting string is =0 characters, if so exit immediately with a return code
that will be interpreted by the calling application as an invalid search
string.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm currently writing a search system in ASP with Javascript connected in
to
> SQL to search a product database. I'm using the FreeTextTable command to
> search a number of fields and bring back results in rank order which is
> working fine. However when the user submits a word in the noise list or
> leaves the search blank and submits SQL returns a nice friendly ODBC
> '80040e14' error.
> I've read in FAQs and other posts etc to clear the noise filter and just
> leave a space but if the user enters a space then hits search we have the
> same issue. I'm not happy with that being the resolution and client side
> code to correct this would have to cover the blank, space or multiple
space
> querystring. Instead of this is there not a way in the stored procedure to
> throw something else back before returning what ever it does to bring up
the
> ODBC error? I'm new to stored procedures so not sure if there would be a
way
> to do this breaking out from a statement if it is yeilding a nasty ODBC
> error. If anyone else has any suggestions or examples as to how they use
the
> FreeTextTable predicate to implement a search system through an ODBC
driver
> please let me know.
> Regards
> Nick Scott
> MCSE 2003
>
|||set a filter to stop certain charactors such as space.
ok?
"Hilary Cotter" <hilary.cotter@.gmail.com> д?
news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Do a replace, replacing these illegal characters. Then check to see if the
> resulting string is =0 characters, if so exit immediately with a return
> code
> that will be interpreted by the calling application as an invalid search
> string.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
> news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> to
> space
> the
> way
> the
> driver
>
|||This would work, as long as the filter would permit these characters when
there was something in addition to them. So the phrase "this is a test"
would pass even though it contains the space characters. Whereas " " would
be filtered out as it only contains the space character.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zgw" <gwdotnet@.hotmail.com> wrote in message
news:u2KqA8MpFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> set a filter to stop certain charactors such as space.
> ok?
> "Hilary Cotter" <hilary.cotter@.gmail.com> д?
> news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
just[vbcol=seagreen]
the[vbcol=seagreen]
side[vbcol=seagreen]
up[vbcol=seagreen]
a[vbcol=seagreen]
use
>
I'm currently writing a search system in ASP with Javascript connected in to
SQL to search a product database. I'm using the FreeTextTable command to
search a number of fields and bring back results in rank order which is
working fine. However when the user submits a word in the noise list or
leaves the search blank and submits SQL returns a nice friendly ODBC
'80040e14' error.
I've read in FAQs and other posts etc to clear the noise filter and just
leave a space but if the user enters a space then hits search we have the
same issue. I'm not happy with that being the resolution and client side
code to correct this would have to cover the blank, space or multiple space
querystring. Instead of this is there not a way in the stored procedure to
throw something else back before returning what ever it does to bring up the
ODBC error? I'm new to stored procedures so not sure if there would be a way
to do this breaking out from a statement if it is yeilding a nasty ODBC
error. If anyone else has any suggestions or examples as to how they use the
FreeTextTable predicate to implement a search system through an ODBC driver
please let me know.
Regards
Nick Scott
MCSE 2003
Do a replace, replacing these illegal characters. Then check to see if the
resulting string is =0 characters, if so exit immediately with a return code
that will be interpreted by the calling application as an invalid search
string.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm currently writing a search system in ASP with Javascript connected in
to
> SQL to search a product database. I'm using the FreeTextTable command to
> search a number of fields and bring back results in rank order which is
> working fine. However when the user submits a word in the noise list or
> leaves the search blank and submits SQL returns a nice friendly ODBC
> '80040e14' error.
> I've read in FAQs and other posts etc to clear the noise filter and just
> leave a space but if the user enters a space then hits search we have the
> same issue. I'm not happy with that being the resolution and client side
> code to correct this would have to cover the blank, space or multiple
space
> querystring. Instead of this is there not a way in the stored procedure to
> throw something else back before returning what ever it does to bring up
the
> ODBC error? I'm new to stored procedures so not sure if there would be a
way
> to do this breaking out from a statement if it is yeilding a nasty ODBC
> error. If anyone else has any suggestions or examples as to how they use
the
> FreeTextTable predicate to implement a search system through an ODBC
driver
> please let me know.
> Regards
> Nick Scott
> MCSE 2003
>
|||set a filter to stop certain charactors such as space.
ok?
"Hilary Cotter" <hilary.cotter@.gmail.com> д?
news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Do a replace, replacing these illegal characters. Then check to see if the
> resulting string is =0 characters, if so exit immediately with a return
> code
> that will be interpreted by the calling application as an invalid search
> string.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
> news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> to
> space
> the
> way
> the
> driver
>
|||This would work, as long as the filter would permit these characters when
there was something in addition to them. So the phrase "this is a test"
would pass even though it contains the space characters. Whereas " " would
be filtered out as it only contains the space character.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zgw" <gwdotnet@.hotmail.com> wrote in message
news:u2KqA8MpFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> set a filter to stop certain charactors such as space.
> ok?
> "Hilary Cotter" <hilary.cotter@.gmail.com> д?
> news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
just[vbcol=seagreen]
the[vbcol=seagreen]
side[vbcol=seagreen]
up[vbcol=seagreen]
a[vbcol=seagreen]
use
>
FREETEXTTABLE returns no result for a complex freetext only on JDB
This problem sounds like a problem of fulltext module, but it's not.
I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
through MS SQL Server JDBC Driver SP3
from J2EE 1.4.2_08 on Windows 2k.
I've indexed and am searching tons of Japanese news articles
to test fulltext features of 2005.
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は阪神大震災で倒 した従X員向け福利厚生施Xの跡地 特別養X老人3施Xを建Xした。')
ON(ID=[KEY])
(This Japanese sentense is a little complex, saying
'The Kansai-Electric has constructed three elder care facilities at a
vacant lot where their employee welfare facilities which had given way
because of the Great Hanshin Earthquake had existed.')
This query returns no result if it was passed through JDBC,
but if I pass it to SQLServer Management Studio directly,
it returns tons of result with very good ranking.
(btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
But, as a strange thing, if I simplify the freetext like
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は福利厚生施Xの 地に施Xを建Xした。')
ON(ID=[KEY])
(It says 'The Kansai-Electric has constructed facilities at a
vacant lot where their employee welfare facilities had existed.')
, it returns good result even through JDBC!
Our company provides a product on Java using fulltext feature of SQL Server
2000,
and is going to adapt it to 2005, expecting much improvements.
We're looking forward to update about JDBC as well as fulltext features!
Hideaki:
The SQL Server 2000 JDBC driver isn't supported against SQL Server 2005.
Please try the SQL Server 2005 driver --
http://www.microsoft.com/sql/downloads/2005/jdbc.mspx.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"hideaki" <hideaki@.discussions.microsoft.com> wrote in message
news:21C664BC-0FD8-488B-BC58-0E119E58CBE0@.microsoft.com...
> This problem sounds like a problem of fulltext module, but it's not.
> I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
> through MS SQL Server JDBC Driver SP3
> from J2EE 1.4.2_08 on Windows 2k.
> I've indexed and am searching tons of Japanese news articles
> to test fulltext features of 2005.
>
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'??????3? ??')
> ON(ID=[KEY])
> (This Japanese sentense is a little complex, saying
> 'The Kansai-Electric has constructed three elder care facilities at a
> vacant lot where their employee welfare facilities which had given way
> because of the Great Hanshin Earthquake had existed.')
> This query returns no result if it was passed through JDBC,
> but if I pass it to SQLServer Management Studio directly,
> it returns tons of result with very good ranking.
> (btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
>
> But, as a strange thing, if I simplify the freetext like
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'?????')
> ON(ID=[KEY])
> (It says 'The Kansai-Electric has constructed facilities at a
> vacant lot where their employee welfare facilities had existed.')
> , it returns good result even through JDBC!
> Our company provides a product on Java using fulltext feature of SQL
> Server
> 2000,
> and is going to adapt it to 2005, expecting much improvements.
> We're looking forward to update about JDBC as well as fulltext features!
>
I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
through MS SQL Server JDBC Driver SP3
from J2EE 1.4.2_08 on Windows 2k.
I've indexed and am searching tons of Japanese news articles
to test fulltext features of 2005.
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は阪神大震災で倒 した従X員向け福利厚生施Xの跡地 特別養X老人3施Xを建Xした。')
ON(ID=[KEY])
(This Japanese sentense is a little complex, saying
'The Kansai-Electric has constructed three elder care facilities at a
vacant lot where their employee welfare facilities which had given way
because of the Great Hanshin Earthquake had existed.')
This query returns no result if it was passed through JDBC,
but if I pass it to SQLServer Management Studio directly,
it returns tons of result with very good ranking.
(btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
But, as a strange thing, if I simplify the freetext like
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は福利厚生施Xの 地に施Xを建Xした。')
ON(ID=[KEY])
(It says 'The Kansai-Electric has constructed facilities at a
vacant lot where their employee welfare facilities had existed.')
, it returns good result even through JDBC!
Our company provides a product on Java using fulltext feature of SQL Server
2000,
and is going to adapt it to 2005, expecting much improvements.
We're looking forward to update about JDBC as well as fulltext features!
Hideaki:
The SQL Server 2000 JDBC driver isn't supported against SQL Server 2005.
Please try the SQL Server 2005 driver --
http://www.microsoft.com/sql/downloads/2005/jdbc.mspx.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"hideaki" <hideaki@.discussions.microsoft.com> wrote in message
news:21C664BC-0FD8-488B-BC58-0E119E58CBE0@.microsoft.com...
> This problem sounds like a problem of fulltext module, but it's not.
> I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
> through MS SQL Server JDBC Driver SP3
> from J2EE 1.4.2_08 on Windows 2k.
> I've indexed and am searching tons of Japanese news articles
> to test fulltext features of 2005.
>
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'??????3? ??')
> ON(ID=[KEY])
> (This Japanese sentense is a little complex, saying
> 'The Kansai-Electric has constructed three elder care facilities at a
> vacant lot where their employee welfare facilities which had given way
> because of the Great Hanshin Earthquake had existed.')
> This query returns no result if it was passed through JDBC,
> but if I pass it to SQLServer Management Studio directly,
> it returns tons of result with very good ranking.
> (btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
>
> But, as a strange thing, if I simplify the freetext like
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'?????')
> ON(ID=[KEY])
> (It says 'The Kansai-Electric has constructed facilities at a
> vacant lot where their employee welfare facilities had existed.')
> , it returns good result even through JDBC!
> Our company provides a product on Java using fulltext feature of SQL
> Server
> 2000,
> and is going to adapt it to 2005, expecting much improvements.
> We're looking forward to update about JDBC as well as fulltext features!
>
FreeTextTable Rank
Hi. I have a question - does anyone know on what criteria is the rank set by
FreeTextTable ? I created some records in the table "titles" containing the
words "drink", "much" and "abstinence" and... well... I can find no rhyme
or reason to the way this rank is established. I got the ranks 293, 266, 266
and 154. The funny thing is that a title like "No *abstinence* for me,
please !" has a higher rank than "ABSTINENCE for dummies." with the word in
capitals.
So... how exactly is this rank set ? How reliable is it ?
I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid" to
set conditions like "where rank >..." (of course, I could always show all
the hits and page the datagrid).
Thanks a lot.
Alex.
After some searching, I found this (for SQL2005):
Ranking of FREETEXT
Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms.
Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 )
qtf / ( k3 + qtf ) ) )
Where:
w is the Robertson-Sparck Jones weight.
Originally, w is defined as:
w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) *
( n - r + 0.5 ) ) )
This was simplified to:
w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
n - r + 0.5 ) ) )
R is the number of documents marked relevant by a user. This is not
implemented in SQL Server 2005 full-text search, and thus is ignored.
r is the number of documents marked relevant by a user containing the term.
This is not implemented.
N is the number of documents with values for the property in the query.
n is the number of documents containing the term.
K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
dl is the document length, in word occurrences.
avdl is the average document length of the property over which the query
spans, in word occurrences.
k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
tf is the frequency of the term in a specific document.
qtf is the frequency of the term in the query.
So now I know :-)))) Anyway, apparently the ranking results have to be taken
with a *BIG" grain of salt.
Alex.
|||Hi Alex,
Yes, I do, but it is complex and you need to understand a bit about basic
Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
upon what is known as OKAPI or BM25 that was developed by Stephen Robertson
(http://research.microsoft.com/users/robertson/). You can see some of the
formula documented in "SQL Server 2005 Full-Text Search: Internals and
Enhancements" at:
http://msdn.microsoft.com/library/de...05ftsearch.asp
Specifically, under "Ranking of FREETEXT" -
"Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms."...
See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
"From MS Newsgroups: However it was my understanding that rank is based on
this formula:
? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
(j)/avdl])?K3(tf(i.j))
W(i) - rank from each term in the search phrase
idf(i) - iS the inverse document frequency of term i
tf(i,j) - is the term frequency for term i, in row j
K1,K2,K3 - are constants
dl - is row/column length in words
AVdl - is the average row/column length length in words"
Another factor is the number of rows and the number of unique non-noise
words per row as you must have a statistically significant number of rows
(at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
meaningful. How many rows are in your table "titles"? Also keep in mind that
the Rank values are specific to your freetext query and primarily useful for
ordering of the results. See SQL Server 2000 BOL title "Full-text Search
Recommendations" - "What is RANK and how is it determined when used with
CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
as "abstinence" with all other factors being equal. It is complex as you can
see, but what is your true objective? Could you provide the exact Freetext
query with sample data and results as well as the full output of SELECT
@.@.version ?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi. I have a question - does anyone know on what criteria is the rank set
by
> FreeTextTable ? I created some records in the table "titles" containing
the
> words "drink", "much" and "abstinence" and... well... I can find no
rhyme
> or reason to the way this rank is established. I got the ranks 293, 266,
266
> and 154. The funny thing is that a title like "No *abstinence* for me,
> please !" has a higher rank than "ABSTINENCE for dummies." with the word
in
> capitals.
> So... how exactly is this rank set ? How reliable is it ?
> I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid"
to
> set conditions like "where rank >..." (of course, I could always show all
> the hits and page the datagrid).
> Thanks a lot.
> Alex.
>
|||Alex,
Yep, so you found (via Google?) the SQL 2005 FTS paper published in Dec
2004. While it documents SQL 2005, I *believe* that as far as the contains
and freetext ranking formula's that they hold true for SQL Server 2000 as
well. Even so, I'm surprised that for SQL Server 2005 (or for that matter
SQL 2000) that relevance feedback (R & r in the formula) was not implemented
as there are standard methods using T-SQL and feedback tables that can be
use to implement automatic relevance feedback...
Oh, and that *BIG" grain of salt, that you speak of, not necessary... You
now know the formula, and with your table's unique non-noise words, you can
calculate the rank values from your query by yourself!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:eAAhomDBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> After some searching, I found this (for SQL2005):
> Ranking of FREETEXT
> Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms.
> Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 +
1 )
> qtf / ( k3 + qtf ) ) )
> Where:
> w is the Robertson-Sparck Jones weight.
> Originally, w is defined as:
> w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 )
*
> ( n - r + 0.5 ) ) )
> This was simplified to:
> w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
> n - r + 0.5 ) ) )
> R is the number of documents marked relevant by a user. This is not
> implemented in SQL Server 2005 full-text search, and thus is ignored.
> r is the number of documents marked relevant by a user containing the
term.
> This is not implemented.
> N is the number of documents with values for the property in the query.
> n is the number of documents containing the term.
> K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
> dl is the document length, in word occurrences.
> avdl is the average document length of the property over which the query
> spans, in word occurrences.
> k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
> tf is the frequency of the term in a specific document.
> qtf is the frequency of the term in the query.
> So now I know :-)))) Anyway, apparently the ranking results have to be
taken
> with a *BIG" grain of salt.
> Alex.
>
|||Hello, John. Thank you for your reply.
Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
about 50 titles in the 'Titles' table.
Here are the results of my query:
------
Rank/Title/Notes/First Name/Last Name
375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
121/No *abstinence* for me, please !/A smoker's paradise. Smoke-smoke-smoke,
boy, oh, isn't it a joy ?/Alberta/Curisor
121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
------
and this is the query, which is correct:
------
strSearch = "SELECT " & _
"SearchTable.[Rank], Titles.title as Title, Titles.notes as
Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]" &
_
"FROM " & _
"FREETEXTTABLE(Titles, *, '" & strText & "') as SearchTable
" & _
"INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id "
& _
"INNER JOIN TitleAuthor ON Titles.title_id =
TitleAuthor.title_id " & _
"INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id " &
_
"ORDER BY SearchTable.[Rank] DESC"
------
The version is:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Alex.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hi Alex,
> Yes, I do, but it is complex and you need to understand a bit about basic
> Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
> upon what is known as OKAPI or BM25 that was developed by Stephen
> Robertson
> (http://research.microsoft.com/users/robertson/). You can see some of the
> formula documented in "SQL Server 2005 Full-Text Search: Internals and
> Enhancements" at:
> http://msdn.microsoft.com/library/de...05ftsearch.asp
> Specifically, under "Ranking of FREETEXT" -
> "Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms."...
> See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
> "From MS Newsgroups: However it was my understanding that rank is based on
> this formula:
> ? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
> (j)/avdl])?K3(tf(i.j))
> W(i) - rank from each term in the search phrase
> idf(i) - iS the inverse document frequency of term i
> tf(i,j) - is the term frequency for term i, in row j
> K1,K2,K3 - are constants
> dl - is row/column length in words
> AVdl - is the average row/column length length in words"
> Another factor is the number of rows and the number of unique non-noise
> words per row as you must have a statistically significant number of rows
> (at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
> meaningful. How many rows are in your table "titles"? Also keep in mind
> that
> the Rank values are specific to your freetext query and primarily useful
> for
> ordering of the results. See SQL Server 2000 BOL title "Full-text Search
> Recommendations" - "What is RANK and how is it determined when used with
> CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
> Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
> SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
> as "abstinence" with all other factors being equal. It is complex as you
> can
> see, but what is your true objective? Could you provide the exact Freetext
> query with sample data and results as well as the full output of SELECT
> @.@.version ?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
> message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> by
> the
> rhyme
> 266
> in
> to
>
|||You're welcome, Alex,
Yes, I thought so. While the pubs and northwind database tables are good
examples for experimenting with SQL FTS queries, they are not large enough
to be used effectively with containstable or freetextable and RANK as you
need production level table sizes to get meaningful Ranking results from the
SQL FTS queries such as the one below.
FYI, I'd recommend that you apply the latest service pack to your SQL Server
2000 (8.00.194) Developer's Edition on WinXP SP2 as the build (194) of SQL
Server 2000 that you are using has no service packs applied and you may be
open security bugs with this RTM version.
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:O2wwSFEBFHA.3472@.TK2MSFTNGP14.phx.gbl...
> Hello, John. Thank you for your reply.
> Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
> about 50 titles in the 'Titles' table.
> Here are the results of my query:
> ----
> Rank/Title/Notes/First Name/Last Name
> 375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
> 266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
> beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
> 121/No *abstinence* for me, please !/A smoker's paradise.
Smoke-smoke-smoke,
> boy, oh, isn't it a joy ?/Alberta/Curisor
> 121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
> ----
> and this is the query, which is correct:
> ----
> strSearch = "SELECT " & _
> "SearchTable.[Rank], Titles.title as Title, Titles.notes
as
> Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]"
&
> _
> "FROM " & _
> "FREETEXTTABLE(Titles, *, '" & strText & "') as
SearchTable
> " & _
> "INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id
"
> & _
> "INNER JOIN TitleAuthor ON Titles.title_id =
> TitleAuthor.title_id " & _
> "INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id "
&
> _
> "ORDER BY SearchTable.[Rank] DESC"
> ----
> The version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on
Windows[vbcol=seagreen]
> NT 5.1 (Build 2600: Service Pack 2)
> Alex.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
basic[vbcol=seagreen]
based[vbcol=seagreen]
the[vbcol=seagreen]
http://msdn.microsoft.com/library/de...05ftsearch.asp[vbcol=seagreen]
in[vbcol=seagreen]
add[vbcol=seagreen]
http://wickedsmrt.blogspot.com/2003_...t_archive.html[vbcol=seagreen]
on[vbcol=seagreen]
rows[vbcol=seagreen]
English,[vbcol=seagreen]
value[vbcol=seagreen]
Freetext[vbcol=seagreen]
set[vbcol=seagreen]
266,[vbcol=seagreen]
word[vbcol=seagreen]
"afraid"
>
FreeTextTable ? I created some records in the table "titles" containing the
words "drink", "much" and "abstinence" and... well... I can find no rhyme
or reason to the way this rank is established. I got the ranks 293, 266, 266
and 154. The funny thing is that a title like "No *abstinence* for me,
please !" has a higher rank than "ABSTINENCE for dummies." with the word in
capitals.
So... how exactly is this rank set ? How reliable is it ?
I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid" to
set conditions like "where rank >..." (of course, I could always show all
the hits and page the datagrid).
Thanks a lot.
Alex.
After some searching, I found this (for SQL2005):
Ranking of FREETEXT
Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms.
Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 )
qtf / ( k3 + qtf ) ) )
Where:
w is the Robertson-Sparck Jones weight.
Originally, w is defined as:
w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) *
( n - r + 0.5 ) ) )
This was simplified to:
w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
n - r + 0.5 ) ) )
R is the number of documents marked relevant by a user. This is not
implemented in SQL Server 2005 full-text search, and thus is ignored.
r is the number of documents marked relevant by a user containing the term.
This is not implemented.
N is the number of documents with values for the property in the query.
n is the number of documents containing the term.
K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
dl is the document length, in word occurrences.
avdl is the average document length of the property over which the query
spans, in word occurrences.
k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
tf is the frequency of the term in a specific document.
qtf is the frequency of the term in the query.
So now I know :-)))) Anyway, apparently the ranking results have to be taken
with a *BIG" grain of salt.
Alex.
|||Hi Alex,
Yes, I do, but it is complex and you need to understand a bit about basic
Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
upon what is known as OKAPI or BM25 that was developed by Stephen Robertson
(http://research.microsoft.com/users/robertson/). You can see some of the
formula documented in "SQL Server 2005 Full-Text Search: Internals and
Enhancements" at:
http://msdn.microsoft.com/library/de...05ftsearch.asp
Specifically, under "Ranking of FREETEXT" -
"Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
the query is ranked, and the values are summed. Freetext queries will add
words to the query via inflectional generation (stemmed forms of the
original query terms); these words are treated as separate terms with no
special weighting or relationship with the words from which they were
generated. Synonyms generated from the Thesaurus feature are treated as
separate, equally weighted terms."...
See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
"From MS Newsgroups: However it was my understanding that rank is based on
this formula:
? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
(j)/avdl])?K3(tf(i.j))
W(i) - rank from each term in the search phrase
idf(i) - iS the inverse document frequency of term i
tf(i,j) - is the term frequency for term i, in row j
K1,K2,K3 - are constants
dl - is row/column length in words
AVdl - is the average row/column length length in words"
Another factor is the number of rows and the number of unique non-noise
words per row as you must have a statistically significant number of rows
(at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
meaningful. How many rows are in your table "titles"? Also keep in mind that
the Rank values are specific to your freetext query and primarily useful for
ordering of the results. See SQL Server 2000 BOL title "Full-text Search
Recommendations" - "What is RANK and how is it determined when used with
CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
as "abstinence" with all other factors being equal. It is complex as you can
see, but what is your true objective? Could you provide the exact Freetext
query with sample data and results as well as the full output of SELECT
@.@.version ?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi. I have a question - does anyone know on what criteria is the rank set
by
> FreeTextTable ? I created some records in the table "titles" containing
the
> words "drink", "much" and "abstinence" and... well... I can find no
rhyme
> or reason to the way this rank is established. I got the ranks 293, 266,
266
> and 154. The funny thing is that a title like "No *abstinence* for me,
> please !" has a higher rank than "ABSTINENCE for dummies." with the word
in
> capitals.
> So... how exactly is this rank set ? How reliable is it ?
> I'm "afraid" to filter the returns (for instance TOP N) and I am "afraid"
to
> set conditions like "where rank >..." (of course, I could always show all
> the hits and page the datagrid).
> Thanks a lot.
> Alex.
>
|||Alex,
Yep, so you found (via Google?) the SQL 2005 FTS paper published in Dec
2004. While it documents SQL 2005, I *believe* that as far as the contains
and freetext ranking formula's that they hold true for SQL Server 2000 as
well. Even so, I'm surprised that for SQL Server 2005 (or for that matter
SQL 2000) that relevance feedback (R & r in the formula) was not implemented
as there are standard methods using T-SQL and feedback tables that can be
use to implement automatic relevance feedback...
Oh, and that *BIG" grain of salt, that you speak of, not necessary... You
now know the formula, and with your table's unique non-noise words, you can
calculate the rank values from your query by yourself!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:eAAhomDBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> After some searching, I found this (for SQL2005):
> Ranking of FREETEXT
> Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms.
> Rank = ?[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 +
1 )
> qtf / ( k3 + qtf ) ) )
> Where:
> w is the Robertson-Sparck Jones weight.
> Originally, w is defined as:
> w = log10 ( ( ( r + 0.5 ) * ( N - n - R + r + 0.5 ) ) / ( ( R - r + 0.5 )
*
> ( n - r + 0.5 ) ) )
> This was simplified to:
> w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * (
> n - r + 0.5 ) ) )
> R is the number of documents marked relevant by a user. This is not
> implemented in SQL Server 2005 full-text search, and thus is ignored.
> r is the number of documents marked relevant by a user containing the
term.
> This is not implemented.
> N is the number of documents with values for the property in the query.
> n is the number of documents containing the term.
> K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) )
> dl is the document length, in word occurrences.
> avdl is the average document length of the property over which the query
> spans, in word occurrences.
> k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
> tf is the frequency of the term in a specific document.
> qtf is the frequency of the term in the query.
> So now I know :-)))) Anyway, apparently the ranking results have to be
taken
> with a *BIG" grain of salt.
> Alex.
>
|||Hello, John. Thank you for your reply.
Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
about 50 titles in the 'Titles' table.
Here are the results of my query:
------
Rank/Title/Notes/First Name/Last Name
375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
121/No *abstinence* for me, please !/A smoker's paradise. Smoke-smoke-smoke,
boy, oh, isn't it a joy ?/Alberta/Curisor
121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
------
and this is the query, which is correct:
------
strSearch = "SELECT " & _
"SearchTable.[Rank], Titles.title as Title, Titles.notes as
Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]" &
_
"FROM " & _
"FREETEXTTABLE(Titles, *, '" & strText & "') as SearchTable
" & _
"INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id "
& _
"INNER JOIN TitleAuthor ON Titles.title_id =
TitleAuthor.title_id " & _
"INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id " &
_
"ORDER BY SearchTable.[Rank] DESC"
------
The version is:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
Alex.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hi Alex,
> Yes, I do, but it is complex and you need to understand a bit about basic
> Informational Retrieval theory. The Rank value from FREETEXTTABLE is based
> upon what is known as OKAPI or BM25 that was developed by Stephen
> Robertson
> (http://research.microsoft.com/users/robertson/). You can see some of the
> formula documented in "SQL Server 2005 Full-Text Search: Internals and
> Enhancements" at:
> http://msdn.microsoft.com/library/de...05ftsearch.asp
> Specifically, under "Ranking of FREETEXT" -
> "Freetext ranking is based on the OKAPI BM25 ranking formula. Each term in
> the query is ranked, and the values are summed. Freetext queries will add
> words to the query via inflectional generation (stemmed forms of the
> original query terms); these words are treated as separate terms with no
> special weighting or relationship with the words from which they were
> generated. Synonyms generated from the Thesaurus feature are treated as
> separate, equally weighted terms."...
> See also http://wickedsmrt.blogspot.com/2003_...t_archive.html
> "From MS Newsgroups: However it was my understanding that rank is based on
> this formula:
> ? W(i)=(K1+1) ?idf(i)?(K2+1) ?tf(i.j) /( K1?[(1-b)+b?dl
> (j)/avdl])?K3(tf(i.j))
> W(i) - rank from each term in the search phrase
> idf(i) - iS the inverse document frequency of term i
> tf(i,j) - is the term frequency for term i, in row j
> K1,K2,K3 - are constants
> dl - is row/column length in words
> AVdl - is the average row/column length length in words"
> Another factor is the number of rows and the number of unique non-noise
> words per row as you must have a statistically significant number of rows
> (at least 10,000) for the OKAPI BM25 Freetexttable Rank value to be
> meaningful. How many rows are in your table "titles"? Also keep in mind
> that
> the Rank values are specific to your freetext query and primarily useful
> for
> ordering of the results. See SQL Server 2000 BOL title "Full-text Search
> Recommendations" - "What is RANK and how is it determined when used with
> CONTAINSTABLE and FREETEXTTABLE predicates?..." for more info.
> Yes, OKAPI BM25 is very reliable, but complex. Note, that for US English,
> SQL FTS is case insensitive and "ABSTINENCE" will have the same rank value
> as "abstinence" with all other factors being equal. It is complex as you
> can
> see, but what is your true objective? Could you provide the exact Freetext
> query with sample data and results as well as the full output of SELECT
> @.@.version ?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
> message news:#YEbgXDBFHA.3824@.TK2MSFTNGP10.phx.gbl...
> by
> the
> rhyme
> 266
> in
> to
>
|||You're welcome, Alex,
Yes, I thought so. While the pubs and northwind database tables are good
examples for experimenting with SQL FTS queries, they are not large enough
to be used effectively with containstable or freetextable and RANK as you
need production level table sizes to get meaningful Ranking results from the
SQL FTS queries such as the one below.
FYI, I'd recommend that you apply the latest service pack to your SQL Server
2000 (8.00.194) Developer's Edition on WinXP SP2 as the build (194) of SQL
Server 2000 that you are using has no service packs applied and you may be
open security bugs with this RTM version.
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:O2wwSFEBFHA.3472@.TK2MSFTNGP14.phx.gbl...
> Hello, John. Thank you for your reply.
> Yes, I understand. Unfortunately, I'm using the pubs db, and I only have
> about 50 titles in the 'Titles' table.
> Here are the results of my query:
> ----
> Rank/Title/Notes/First Name/Last Name
> 375/We don't drink that much/Essay on abstinence./Napoleon/Borcan
> 266/Que c'est bon, c'est bon, c'est bon !/Essai sur les joies de boire
> beaucoup. Abstinents s'abstenir (eng: abstinence)./Pisica/Rindunel
> 121/No *abstinence* for me, please !/A smoker's paradise.
Smoke-smoke-smoke,
> boy, oh, isn't it a joy ?/Alberta/Curisor
> 121/ABSTINENCE for dummies./An epicurian's guide./Pupu/Balacarescu
> ----
> and this is the query, which is correct:
> ----
> strSearch = "SELECT " & _
> "SearchTable.[Rank], Titles.title as Title, Titles.notes
as
> Notes, Authors.au_fname as [First Name], Authors.au_lname as [Last Name]"
&
> _
> "FROM " & _
> "FREETEXTTABLE(Titles, *, '" & strText & "') as
SearchTable
> " & _
> "INNER JOIN Titles ON SearchTable.[Key] = Titles.title_id
"
> & _
> "INNER JOIN TitleAuthor ON Titles.title_id =
> TitleAuthor.title_id " & _
> "INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id "
&
> _
> "ORDER BY SearchTable.[Rank] DESC"
> ----
> The version is:
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on
Windows[vbcol=seagreen]
> NT 5.1 (Build 2600: Service Pack 2)
> Alex.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:Oof9C5DBFHA.3644@.TK2MSFTNGP15.phx.gbl...
basic[vbcol=seagreen]
based[vbcol=seagreen]
the[vbcol=seagreen]
http://msdn.microsoft.com/library/de...05ftsearch.asp[vbcol=seagreen]
in[vbcol=seagreen]
add[vbcol=seagreen]
http://wickedsmrt.blogspot.com/2003_...t_archive.html[vbcol=seagreen]
on[vbcol=seagreen]
rows[vbcol=seagreen]
English,[vbcol=seagreen]
value[vbcol=seagreen]
Freetext[vbcol=seagreen]
set[vbcol=seagreen]
266,[vbcol=seagreen]
word[vbcol=seagreen]
"afraid"
>
FREETEXTTABLE question
i never seem to get the basics, but once i do it all falls into place.
this query is simple, but doesn't work and i'm sure someone will tell me why.
declare @.term as varchar(50)
set @.term = 'car repair'
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
the error is: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'car repair'.
just can't seem to get it. Must be a Yew Years Thing
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
and... This always has a habit of happening
I figure it out within a min. of my posting!
I needed to include the table name!!!!
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usr, usrBusDesc, 'car repair') ft on u.usrid =
ft.[KEY]
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"WebBuilder451" wrote:
> i never seem to get the basics, but once i do it all falls into place.
> this query is simple, but doesn't work and i'm sure someone will tell me why.
> declare @.term as varchar(50)
> set @.term = 'car repair'
> select u.usrCompany
> from usr u
> inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
> the error is: Msg 170, Level 15, State 1, Line 10
> Line 10: Incorrect syntax near 'car repair'.
> just can't seem to get it. Must be a Yew Years Thing
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
this query is simple, but doesn't work and i'm sure someone will tell me why.
declare @.term as varchar(50)
set @.term = 'car repair'
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
the error is: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'car repair'.
just can't seem to get it. Must be a Yew Years Thing
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
and... This always has a habit of happening
I figure it out within a min. of my posting!
I needed to include the table name!!!!
select u.usrCompany
from usr u
inner join FREETEXTTABLE(usr, usrBusDesc, 'car repair') ft on u.usrid =
ft.[KEY]
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"WebBuilder451" wrote:
> i never seem to get the basics, but once i do it all falls into place.
> this query is simple, but doesn't work and i'm sure someone will tell me why.
> declare @.term as varchar(50)
> set @.term = 'car repair'
> select u.usrCompany
> from usr u
> inner join FREETEXTTABLE(usrBusDesc, 'car repair') ft on u.usrid = ft.usrid
> the error is: Msg 170, Level 15, State 1, Line 10
> Line 10: Incorrect syntax near 'car repair'.
> just can't seem to get it. Must be a Yew Years Thing
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
freetexttable query continued
Still working on this query:
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>
FREETEXTTABLE on more than one indexed fields
Is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Or is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?Denis,
See, my reply in the fulltext newsgroup, but yes, this can be done, see "SQL
Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
and substitute freetexttable for containstable in the examples.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:uzC6lOJKFHA.1340@.TK2MSFTNGP10.phx.gbl...
> Is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Or is it possible to create a second FT-INDEX for the same table? I want
to
> index different fields in each one to use it in different areas.
>
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included.
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Or is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?Denis,
See, my reply in the fulltext newsgroup, but yes, this can be done, see "SQL
Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
and substitute freetexttable for containstable in the examples.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:uzC6lOJKFHA.1340@.TK2MSFTNGP10.phx.gbl...
> Is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Or is it possible to create a second FT-INDEX for the same table? I want
to
> index different fields in each one to use it in different areas.
>
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included.
FreeTextTable on image column of document metadata
I would like to be able to search the metadata of files (e.g. Title,
Keywords,
etc) that I have stored in a database. I have included the "metainfo" column
(image) in the full-text catalog. Running a freetexttable on the documents
table returns matching results for web pages (.htm, .aspx), however for
other documents (e.g. word, excel, etc), I am getting no results. Is this
an IFilter issue? Am I missing something here?
Thanks in advance!
This will work in SQL 2005, however you will not be able to search on only
the property. The contains search will be on all properties.
In SQL 2000 you will have to extract the properties and store them in
seperate columns and SQL FTI them.
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
>I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
> column
> (image) in the full-text catalog. Running a freetexttable on the
> documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||Jeremy,
Could you post the full output of -- SELECT @.@.version -- as this is most
helpful in understanding your environment and will help more quickly answer
your questions.
If you're using SQL Server 7.0, you are limited to extracting the text from
file and storing this text in a column defined with TEXT or NText datatype.
I'm assuming from your comments that you're using SQL Server 2000, you can
store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
defined with an IMAGE datatype. Note, you will need to also define a "file
extension" column that identifies the file type (use char(3), varchar(4) or
sysname) and populate this column with "doc", ".doc" respectively.
Can I assume that if you search on keywords that you know to be in the body
of the documents (e.g. word, excel, etc) that you do get the row that
contains that keyword returned in your CONTAINS or FREETEXT query?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
column
> (image) in the full-text catalog. Running a freetexttable on the
documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||btw-you might want to check out
http://www.indexserverfaq.com/blobs.htm for more information on how to index
blobs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeOh$1%23BFHA.2392@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> This will work in SQL 2005, however you will not be able to search on only
> the property. The contains search will be on all properties.
> In SQL 2000 you will have to extract the properties and store them in
> seperate columns and SQL FTI them.
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
this
>
|||John,
I am using SQL Server 2000. Here's my select statement:
SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
I have two columns defined on the document information, MetaInfo and Content
(as well as the extension column that stores the doc extension). When using
terms I know to be in the document, I can successfully return results from
the "Content" column, however, the "MetaInfo" column does not seem to return
any correct results (besides web pages). I have programmatically pulled the
column data and viewed the metainfo column in an application with no
problem. However, the freetexttable statement I'm using does not seem to be
finding the search term.
Ideas?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Could you post the full output of -- SELECT @.@.version -- as this is most
> helpful in understanding your environment and will help more quickly
answer
> your questions.
> If you're using SQL Server 7.0, you are limited to extracting the text
from
> file and storing this text in a column defined with TEXT or NText
datatype.
> I'm assuming from your comments that you're using SQL Server 2000, you can
> store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
> defined with an IMAGE datatype. Note, you will need to also define a "file
> extension" column that identifies the file type (use char(3), varchar(4)
or
> sysname) and populate this column with "doc", ".doc" respectively.
> Can I assume that if you search on keywords that you know to be in the
body[vbcol=seagreen]
> of the documents (e.g. word, excel, etc) that you do get the row that
> contains that keyword returned in your CONTAINS or FREETEXT query?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> column
> documents
this
>
|||Jeremy,
Actually, I have more follow-up questions as I want to be sure that I
understand your environment...
Could you confirm your FT-enabled table (Docs) column definitions? Content
defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
type of datatype? What is the content of the MetaInfo column? Is it document
property type data, such as Author, Company, Keywords, etc.? If so, how did
you import the text into this column?
Thanks!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> John,
> I am using SQL Server 2000. Here's my select statement:
> SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
> I have two columns defined on the document information, MetaInfo and
Content
> (as well as the extension column that stores the doc extension). When
using
> terms I know to be in the document, I can successfully return results from
> the "Content" column, however, the "MetaInfo" column does not seem to
return
> any correct results (besides web pages). I have programmatically pulled
the
> column data and viewed the metainfo column in an application with no
> problem. However, the freetexttable statement I'm using does not seem to
be[vbcol=seagreen]
> finding the search term.
> Ideas?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> answer
> from
> datatype.
can[vbcol=seagreen]
column[vbcol=seagreen]
"file[vbcol=seagreen]
> or
> body
for
> this
>
|||On the Docs table, I have FT-enabled the following:
DocName nvarchar(128)
MetaInfo image(16)
Content image(16)
Here's an example of the MetaInfo that I have pulled out programmatically.
vti_cachedcustomprops:VX|vti_title
vti_modifiedby:SR|lab2003\\jmaddrey
vti_cachedtitle:SR|catsdogs
vti_title:SR|catsdogs
vti_author:SR|lab2003\\jmaddrey
When I perform the search "catsdogs", nothing is found. However, any text I
place within the body of the document is accurately returned.
In regards to importing the data into the metainfo column, I am working with
a Windows Sharepoint Services site, so the database was generated by
Sharepoint. I am not exactly sure how that data is extracted by the
Sharepoint framework.
Thanks!
Jeremy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Actually, I have more follow-up questions as I want to be sure that I
> understand your environment...
> Could you confirm your FT-enabled table (Docs) column definitions?
Content
> defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
> type of datatype? What is the content of the MetaInfo column? Is it
document
> property type data, such as Author, Company, Keywords, etc.? If so, how
did[vbcol=seagreen]
> you import the text into this column?
> Thanks!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Content
> using
from[vbcol=seagreen]
> return
> the
to[vbcol=seagreen]
> be
most[vbcol=seagreen]
> can
> column
> "file
varchar(4)[vbcol=seagreen]
"metainfo"[vbcol=seagreen]
> for
Is
>
|||Anyone? Anyone?
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
|||Jeremy,
Sorry, for the late reply! I've had a lot of things going on lately, no
excuse, but just keeping me busy...
The thing about this is I don't have a real answer for you as metadata in
the MetaInfo column was generated by Sharepoint and I too am I am not
exactly sure how that data is extracted by the Sharepoint framework.
Fundamentally, and outside of Sharepoint, SQL FTS must have a valid file
extension, usually .doc for MS Word documents, or .pdf for Adobe PDF file
types as is with your Content column. This file extension is then used by
the MSSearch service and calls a daemon process to FT Index a specific file
type. However, there is no file extension column and file type that is
linked with the MetaInfo column (defined with the image datatype) in order
to successfully FT Index the metadata generated by SharePoint, I'm sad to
say...
At this point, I'd recommend that you open a support case with Microsoft PSS
Sharepoint support first (and if they can't help, then SQL Server support)
and have them help you through this understanding how to accomplish what
you're trying to do. If you don't mind, if they do provide a solution, could
you post it back here in this newsgroup for others to read?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:OhD8seVDFHA.512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Anyone? Anyone?
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
programmatically.[vbcol=seagreen]
text[vbcol=seagreen]
> I
> with
> what
how[vbcol=seagreen]
When[vbcol=seagreen]
results[vbcol=seagreen]
to[vbcol=seagreen]
> pulled
seem[vbcol=seagreen]
is[vbcol=seagreen]
quickly[vbcol=seagreen]
> text
> you
an[vbcol=seagreen]
a[vbcol=seagreen]
> the
> that
> Title,
the[vbcol=seagreen]
> however
results.
>
|||Can you explain what you mean by pulled out programmatically?
I took your content, stored it in and image data type column and using a
value of txt for the document column type I was able to query for catsdogs.
Can you perhaps tell us what the value of your document type column is?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
Keywords,
etc) that I have stored in a database. I have included the "metainfo" column
(image) in the full-text catalog. Running a freetexttable on the documents
table returns matching results for web pages (.htm, .aspx), however for
other documents (e.g. word, excel, etc), I am getting no results. Is this
an IFilter issue? Am I missing something here?
Thanks in advance!
This will work in SQL 2005, however you will not be able to search on only
the property. The contains search will be on all properties.
In SQL 2000 you will have to extract the properties and store them in
seperate columns and SQL FTI them.
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
>I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
> column
> (image) in the full-text catalog. Running a freetexttable on the
> documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||Jeremy,
Could you post the full output of -- SELECT @.@.version -- as this is most
helpful in understanding your environment and will help more quickly answer
your questions.
If you're using SQL Server 7.0, you are limited to extracting the text from
file and storing this text in a column defined with TEXT or NText datatype.
I'm assuming from your comments that you're using SQL Server 2000, you can
store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
defined with an IMAGE datatype. Note, you will need to also define a "file
extension" column that identifies the file type (use char(3), varchar(4) or
sysname) and populate this column with "doc", ".doc" respectively.
Can I assume that if you search on keywords that you know to be in the body
of the documents (e.g. word, excel, etc) that you do get the row that
contains that keyword returned in your CONTAINS or FREETEXT query?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
column
> (image) in the full-text catalog. Running a freetexttable on the
documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||btw-you might want to check out
http://www.indexserverfaq.com/blobs.htm for more information on how to index
blobs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeOh$1%23BFHA.2392@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> This will work in SQL 2005, however you will not be able to search on only
> the property. The contains search will be on all properties.
> In SQL 2000 you will have to extract the properties and store them in
> seperate columns and SQL FTI them.
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
this
>
|||John,
I am using SQL Server 2000. Here's my select statement:
SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
I have two columns defined on the document information, MetaInfo and Content
(as well as the extension column that stores the doc extension). When using
terms I know to be in the document, I can successfully return results from
the "Content" column, however, the "MetaInfo" column does not seem to return
any correct results (besides web pages). I have programmatically pulled the
column data and viewed the metainfo column in an application with no
problem. However, the freetexttable statement I'm using does not seem to be
finding the search term.
Ideas?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Could you post the full output of -- SELECT @.@.version -- as this is most
> helpful in understanding your environment and will help more quickly
answer
> your questions.
> If you're using SQL Server 7.0, you are limited to extracting the text
from
> file and storing this text in a column defined with TEXT or NText
datatype.
> I'm assuming from your comments that you're using SQL Server 2000, you can
> store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
> defined with an IMAGE datatype. Note, you will need to also define a "file
> extension" column that identifies the file type (use char(3), varchar(4)
or
> sysname) and populate this column with "doc", ".doc" respectively.
> Can I assume that if you search on keywords that you know to be in the
body[vbcol=seagreen]
> of the documents (e.g. word, excel, etc) that you do get the row that
> contains that keyword returned in your CONTAINS or FREETEXT query?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> column
> documents
this
>
|||Jeremy,
Actually, I have more follow-up questions as I want to be sure that I
understand your environment...
Could you confirm your FT-enabled table (Docs) column definitions? Content
defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
type of datatype? What is the content of the MetaInfo column? Is it document
property type data, such as Author, Company, Keywords, etc.? If so, how did
you import the text into this column?
Thanks!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> John,
> I am using SQL Server 2000. Here's my select statement:
> SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
> I have two columns defined on the document information, MetaInfo and
Content
> (as well as the extension column that stores the doc extension). When
using
> terms I know to be in the document, I can successfully return results from
> the "Content" column, however, the "MetaInfo" column does not seem to
return
> any correct results (besides web pages). I have programmatically pulled
the
> column data and viewed the metainfo column in an application with no
> problem. However, the freetexttable statement I'm using does not seem to
be[vbcol=seagreen]
> finding the search term.
> Ideas?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> answer
> from
> datatype.
can[vbcol=seagreen]
column[vbcol=seagreen]
"file[vbcol=seagreen]
> or
> body
for
> this
>
|||On the Docs table, I have FT-enabled the following:
DocName nvarchar(128)
MetaInfo image(16)
Content image(16)
Here's an example of the MetaInfo that I have pulled out programmatically.
vti_cachedcustomprops:VX|vti_title
vti_modifiedby:SR|lab2003\\jmaddrey
vti_cachedtitle:SR|catsdogs
vti_title:SR|catsdogs
vti_author:SR|lab2003\\jmaddrey
When I perform the search "catsdogs", nothing is found. However, any text I
place within the body of the document is accurately returned.
In regards to importing the data into the metainfo column, I am working with
a Windows Sharepoint Services site, so the database was generated by
Sharepoint. I am not exactly sure how that data is extracted by the
Sharepoint framework.
Thanks!
Jeremy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Actually, I have more follow-up questions as I want to be sure that I
> understand your environment...
> Could you confirm your FT-enabled table (Docs) column definitions?
Content
> defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
> type of datatype? What is the content of the MetaInfo column? Is it
document
> property type data, such as Author, Company, Keywords, etc.? If so, how
did[vbcol=seagreen]
> you import the text into this column?
> Thanks!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Content
> using
from[vbcol=seagreen]
> return
> the
to[vbcol=seagreen]
> be
most[vbcol=seagreen]
> can
> column
> "file
varchar(4)[vbcol=seagreen]
"metainfo"[vbcol=seagreen]
> for
Is
>
|||Anyone? Anyone?
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
|||Jeremy,
Sorry, for the late reply! I've had a lot of things going on lately, no
excuse, but just keeping me busy...
The thing about this is I don't have a real answer for you as metadata in
the MetaInfo column was generated by Sharepoint and I too am I am not
exactly sure how that data is extracted by the Sharepoint framework.
Fundamentally, and outside of Sharepoint, SQL FTS must have a valid file
extension, usually .doc for MS Word documents, or .pdf for Adobe PDF file
types as is with your Content column. This file extension is then used by
the MSSearch service and calls a daemon process to FT Index a specific file
type. However, there is no file extension column and file type that is
linked with the MetaInfo column (defined with the image datatype) in order
to successfully FT Index the metadata generated by SharePoint, I'm sad to
say...
At this point, I'd recommend that you open a support case with Microsoft PSS
Sharepoint support first (and if they can't help, then SQL Server support)
and have them help you through this understanding how to accomplish what
you're trying to do. If you don't mind, if they do provide a solution, could
you post it back here in this newsgroup for others to read?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:OhD8seVDFHA.512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Anyone? Anyone?
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
programmatically.[vbcol=seagreen]
text[vbcol=seagreen]
> I
> with
> what
how[vbcol=seagreen]
When[vbcol=seagreen]
results[vbcol=seagreen]
to[vbcol=seagreen]
> pulled
seem[vbcol=seagreen]
is[vbcol=seagreen]
quickly[vbcol=seagreen]
> text
> you
an[vbcol=seagreen]
a[vbcol=seagreen]
> the
> that
> Title,
the[vbcol=seagreen]
> however
results.
>
|||Can you explain what you mean by pulled out programmatically?
I took your content, stored it in and image data type column and using a
value of txt for the document column type I was able to query for catsdogs.
Can you perhaps tell us what the value of your document type column is?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
Freetexttable Not Finding Inflectional Forms
We're using SQL Server 2005 service pack 1. It was my understanding that
FreeTextTable automatically includes inflectional forms. We're not able to
get any inflectional forms of words during the search (even if we use
Contains with the special syntax). Are we missing something in the server
configuration? Or is there a bug somewhere?
Thanks,
Krip
Are you wrapping your freetext search in double quotes - this disables the
stemming (inflectional search)?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:9E599183-45E3-4CA8-8628-540038B91EB6@.microsoft.com...
> We're using SQL Server 2005 service pack 1. It was my understanding that
> FreeTextTable automatically includes inflectional forms. We're not able
> to get any inflectional forms of words during the search (even if we use
> Contains with the special syntax). Are we missing something in the server
> configuration? Or is there a bug somewhere?
> Thanks,
> Krip
>
|||Hilary,
Nope, not wrapping with double quotes. Here's the clause:
INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
I have 'test' in the data but 'tests' doesn't find it. That's just one
example (fox works but not foxes; landed works but not landing).
The following doesn't work either:
SELECT *
FROM myTable
WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
Also, I've now installed SP2 and rebuilt the catalag - same issue.
Is there some place to enable inflectional forms? Or is there a dictionary
to populate?
Thanks,
Krip
|||Perhaps it is a language issue, what does this return? sp_configure 'default
full-text language'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:3424FB65-92CF-40BA-B08E-436298FABB96@.microsoft.com...
> Hilary,
> Nope, not wrapping with double quotes. Here's the clause:
> INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
> I have 'test' in the data but 'tests' doesn't find it. That's just one
> example (fox works but not foxes; landed works but not landing).
> The following doesn't work either:
> SELECT *
> FROM myTable
> WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
> Also, I've now installed SP2 and rebuilt the catalag - same issue.
> Is there some place to enable inflectional forms? Or is there a
> dictionary to populate?
> Thanks,
> Krip
>
>
|||Hilary,
It returns the following:
name: default full-text language
minimum: 0
maximum: 2147483647
config_value: 1033
run_value: 1033
Thanks for your help,
Krip
FreeTextTable automatically includes inflectional forms. We're not able to
get any inflectional forms of words during the search (even if we use
Contains with the special syntax). Are we missing something in the server
configuration? Or is there a bug somewhere?
Thanks,
Krip
Are you wrapping your freetext search in double quotes - this disables the
stemming (inflectional search)?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:9E599183-45E3-4CA8-8628-540038B91EB6@.microsoft.com...
> We're using SQL Server 2005 service pack 1. It was my understanding that
> FreeTextTable automatically includes inflectional forms. We're not able
> to get any inflectional forms of words during the search (even if we use
> Contains with the special syntax). Are we missing something in the server
> configuration? Or is there a bug somewhere?
> Thanks,
> Krip
>
|||Hilary,
Nope, not wrapping with double quotes. Here's the clause:
INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
I have 'test' in the data but 'tests' doesn't find it. That's just one
example (fox works but not foxes; landed works but not landing).
The following doesn't work either:
SELECT *
FROM myTable
WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
Also, I've now installed SP2 and rebuilt the catalag - same issue.
Is there some place to enable inflectional forms? Or is there a dictionary
to populate?
Thanks,
Krip
|||Perhaps it is a language issue, what does this return? sp_configure 'default
full-text language'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:3424FB65-92CF-40BA-B08E-436298FABB96@.microsoft.com...
> Hilary,
> Nope, not wrapping with double quotes. Here's the clause:
> INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
> I have 'test' in the data but 'tests' doesn't find it. That's just one
> example (fox works but not foxes; landed works but not landing).
> The following doesn't work either:
> SELECT *
> FROM myTable
> WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
> Also, I've now installed SP2 and rebuilt the catalag - same issue.
> Is there some place to enable inflectional forms? Or is there a
> dictionary to populate?
> Thanks,
> Krip
>
>
|||Hilary,
It returns the following:
name: default full-text language
minimum: 0
maximum: 2147483647
config_value: 1033
run_value: 1033
Thanks for your help,
Krip
FreeTextTable multiple Columns
I'm trying to do a FREETEXTTABLE search on two columns.
I can get it to work correctly with searching only one column.
I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.
What I have so far is:
SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c
I picture it being in the WHERE clause as:
WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))
but I haven't found an example where I can put it in the where clause.
Thanks for any help,
Tim
Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:
use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows
Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows
Hope this helps!
Regards,
John
"Tim" <tim_cavins@.hotmail.com> wrote in message
news:3020d2f4.0407071416.2866d84b@.posting.google.c om...
> I'm trying to do a FREETEXTTABLE search on two columns.
> I can get it to work correctly with searching only one column.
> I basically need it to do an OR on the two columns and haven't been
> able to find anything on that. It seems like I'm limited to finding
> matches that have it in both columns.
> What I have so far is:
> SELECT a.Name, b.rank, c.rank
> FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
> FreeTextTable(Test, SolutionProblem, 'author) AS c
> I picture it being in the WHERE clause as:
> WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
> SolutionProblem, 'author'))
> but I haven't found an example where I can put it in the where clause.
> Thanks for any help,
> Tim
I can get it to work correctly with searching only one column.
I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.
What I have so far is:
SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c
I picture it being in the WHERE clause as:
WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))
but I haven't found an example where I can put it in the where clause.
Thanks for any help,
Tim
Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:
use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows
Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows
Hope this helps!
Regards,
John
"Tim" <tim_cavins@.hotmail.com> wrote in message
news:3020d2f4.0407071416.2866d84b@.posting.google.c om...
> I'm trying to do a FREETEXTTABLE search on two columns.
> I can get it to work correctly with searching only one column.
> I basically need it to do an OR on the two columns and haven't been
> able to find anything on that. It seems like I'm limited to finding
> matches that have it in both columns.
> What I have so far is:
> SELECT a.Name, b.rank, c.rank
> FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
> FreeTextTable(Test, SolutionProblem, 'author) AS c
> I picture it being in the WHERE clause as:
> WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
> SolutionProblem, 'author'))
> but I haven't found an example where I can put it in the where clause.
> Thanks for any help,
> Tim
FreeTextTable linked to multiple joins
I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help.
Chris,
It's un-clear to me from the information that you've provided below whether
or not "category", "SubCategory" are separate tables or are columns in the
same table. Could you provide more specific information? Specifically, the
output of sp_help <table_name(s)> ?
If what you are looking for is how to search multiple columns in one table
using FREETEXTTABLE, please review this T-SQL Query example using the
Northwind database table employees:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that the same table (Employees) is use with multiple columns (Notes
and Title) from this table.
Hopefully, this is what you're looking for.
Regards,
John
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
>
|||No I was looking for searches across multiple tables.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Chris,
> It's un-clear to me from the information that you've provided below
whether
> or not "category", "SubCategory" are separate tables or are columns in the
> same table. Could you provide more specific information? Specifically, the
> output of sp_help <table_name(s)> ?
> If what you are looking for is how to search multiple columns in one table
> using FREETEXTTABLE, please review this T-SQL Query example using the
> Northwind database table employees:
> use Northwind
> go
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> freetexttable(Employees, Notes, 'BA') as A,
> freetexttable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> Note, that the same table (Employees) is use with multiple columns (Notes
> and Title) from this table.
> Hopefully, this is what you're looking for.
> Regards,
> John
>
>
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
>
|||Chris,
Ok, try this...
SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID
from Employees AS e, EmployeeTerritories t,
containstable(Employees, Notes, 'BA') as A,
containstable(EmployeeTerritories, TerritoryID, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, in order for this example to work, you must alter the table
EmployeeTerritories and add a single non-null column in order to use as the
FT-Index key.
Regards,
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:Ok5aIorIEHA.964@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> No I was looking for searches across multiple tables.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> whether
the[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]
(Notes[vbcol=seagreen]
as[vbcol=seagreen]
and[vbcol=seagreen]
category[vbcol=seagreen]
I
>
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help.
Chris,
It's un-clear to me from the information that you've provided below whether
or not "category", "SubCategory" are separate tables or are columns in the
same table. Could you provide more specific information? Specifically, the
output of sp_help <table_name(s)> ?
If what you are looking for is how to search multiple columns in one table
using FREETEXTTABLE, please review this T-SQL Query example using the
Northwind database table employees:
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, that the same table (Employees) is use with multiple columns (Notes
and Title) from this table.
Hopefully, this is what you're looking for.
Regards,
John
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
>
|||No I was looking for searches across multiple tables.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> Chris,
> It's un-clear to me from the information that you've provided below
whether
> or not "category", "SubCategory" are separate tables or are columns in the
> same table. Could you provide more specific information? Specifically, the
> output of sp_help <table_name(s)> ?
> If what you are looking for is how to search multiple columns in one table
> using FREETEXTTABLE, please review this T-SQL Query example using the
> Northwind database table employees:
> use Northwind
> go
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> freetexttable(Employees, Notes, 'BA') as A,
> freetexttable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> Note, that the same table (Employees) is use with multiple columns (Notes
> and Title) from this table.
> Hopefully, this is what you're looking for.
> Regards,
> John
>
>
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:u4LHWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
>
|||Chris,
Ok, try this...
SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID
from Employees AS e, EmployeeTerritories t,
containstable(Employees, Notes, 'BA') as A,
containstable(EmployeeTerritories, TerritoryID, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Note, in order for this example to work, you must alter the table
EmployeeTerritories and add a single non-null column in order to use as the
FT-Index key.
Regards,
John
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:Ok5aIorIEHA.964@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> No I was looking for searches across multiple tables.
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ut7VbWZHEHA.3128@.TK2MSFTNGP12.phx.gbl...
> whether
the[vbcol=seagreen]
the[vbcol=seagreen]
table[vbcol=seagreen]
(Notes[vbcol=seagreen]
as[vbcol=seagreen]
and[vbcol=seagreen]
category[vbcol=seagreen]
I
>
freetexttable issue
Hi,
We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.
here is the codesnippet:
select * from freetexttable(<tablename>, *, 'business')
Any help is greatly appreciated.
Baldwin
bbudiongan@.misicompany.com
Are the two keyword int he same column ? if no, are there different word breakers defined on the column ? Are the two columns freetextindexed ?Jens Suessmeyer.
http://www.sqlserver2005.de
freetexttable issue
Hi,
I am using freetexttable as a search tool for our application but it
seems that it is partially working. I am able to search on some words
specified in my catalog but not on others. For example I got keywords
- stamps business cards. The freetexttable function can pick up only
'stamps' but not 'business'. I created another record in the table
included in the catalog and have the same keywords. Now I can search
on 'stamps' of the first record but not on the second record.
here is the code snippet:
select * from freetexttable(<table name>, *, 'keywordhere')
where: tablename is included in the catalog
keywordhere is the keyword in which i use 'stamps' or
'business'.
Any help is greatly appreciated.
thanks!
Baldwin
Can you try this
select * from freetexttable(<table name>, *, 'Stamps')
select * from freetexttable(<table name>, *, 'business')
select * from freetexttable(<table name>, *, 'Cards')
Do the same rows show up?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ibaldwinjr@.gmail.com> wrote in message
news:1184851168.766256.92770@.z28g2000prd.googlegro ups.com...
> Hi,
> I am using freetexttable as a search tool for our application but it
> seems that it is partially working. I am able to search on some words
> specified in my catalog but not on others. For example I got keywords
> - stamps business cards. The freetexttable function can pick up only
> 'stamps' but not 'business'. I created another record in the table
> included in the catalog and have the same keywords. Now I can search
> on 'stamps' of the first record but not on the second record.
> here is the code snippet:
> select * from freetexttable(<table name>, *, 'keywordhere')
> where: tablename is included in the catalog
> keywordhere is the keyword in which i use 'stamps' or
> 'business'.
> Any help is greatly appreciated.
> thanks!
> Baldwin
>
I am using freetexttable as a search tool for our application but it
seems that it is partially working. I am able to search on some words
specified in my catalog but not on others. For example I got keywords
- stamps business cards. The freetexttable function can pick up only
'stamps' but not 'business'. I created another record in the table
included in the catalog and have the same keywords. Now I can search
on 'stamps' of the first record but not on the second record.
here is the code snippet:
select * from freetexttable(<table name>, *, 'keywordhere')
where: tablename is included in the catalog
keywordhere is the keyword in which i use 'stamps' or
'business'.
Any help is greatly appreciated.
thanks!
Baldwin
Can you try this
select * from freetexttable(<table name>, *, 'Stamps')
select * from freetexttable(<table name>, *, 'business')
select * from freetexttable(<table name>, *, 'Cards')
Do the same rows show up?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<ibaldwinjr@.gmail.com> wrote in message
news:1184851168.766256.92770@.z28g2000prd.googlegro ups.com...
> Hi,
> I am using freetexttable as a search tool for our application but it
> seems that it is partially working. I am able to search on some words
> specified in my catalog but not on others. For example I got keywords
> - stamps business cards. The freetexttable function can pick up only
> 'stamps' but not 'business'. I created another record in the table
> included in the catalog and have the same keywords. Now I can search
> on 'stamps' of the first record but not on the second record.
> here is the code snippet:
> select * from freetexttable(<table name>, *, 'keywordhere')
> where: tablename is included in the catalog
> keywordhere is the keyword in which i use 'stamps' or
> 'business'.
> Any help is greatly appreciated.
> thanks!
> Baldwin
>
Freetexttable
Okay I have finally found full text-searching and got it working with SQL2000.
Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.
Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.
Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.
Incidentally should I use CONTAINSTABLE?
Quick Note: I am only wanting to search through one table, no more than 2000 rows.
So I have my table (lets call it Account_List) containing the following rows:
AccountID (int,Not Null) <-PrimaryKey
AccountName (char(50), Null)
AccountAddress1 (char(50), Null)
AccountAddress2 (char(50), Null)
AccountTown (char(50), Null)
AccountCounty (char(50), Null)
AccountPostcode (char(10), Null)
AccountTelephone (nvchar(50), Null)
I have a populated full-text catalog for the above table containing all of the rows.
Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.
Now the only example so far I have is:
USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO
Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.
I would like a example of a simple string to search my database from the user input form.
Can anyone enlighten me :)As far as JOINing goes this is from Microsoft:
Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.
For more examples :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_1m9f.asp|||Got to the bottom of it!
The correct Query is as follows for anyone wanting to know :eek:
USE mydata
GO
SELECT *
FROM Account_List AS FT_TBL
INNER JOIN
FREETEXTTABLE(Account_List, *,
'Argos in Milton Keynes') AS KEY_TBL
ON FT_TBL.AccountID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
Oh how I like technology (when it works :mad: )
Thanks!
Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.
Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.
Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.
Incidentally should I use CONTAINSTABLE?
Quick Note: I am only wanting to search through one table, no more than 2000 rows.
So I have my table (lets call it Account_List) containing the following rows:
AccountID (int,Not Null) <-PrimaryKey
AccountName (char(50), Null)
AccountAddress1 (char(50), Null)
AccountAddress2 (char(50), Null)
AccountTown (char(50), Null)
AccountCounty (char(50), Null)
AccountPostcode (char(10), Null)
AccountTelephone (nvchar(50), Null)
I have a populated full-text catalog for the above table containing all of the rows.
Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.
Now the only example so far I have is:
USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO
Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.
I would like a example of a simple string to search my database from the user input form.
Can anyone enlighten me :)As far as JOINing goes this is from Microsoft:
Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.
For more examples :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_1m9f.asp|||Got to the bottom of it!
The correct Query is as follows for anyone wanting to know :eek:
USE mydata
GO
SELECT *
FROM Account_List AS FT_TBL
INNER JOIN
FREETEXTTABLE(Account_List, *,
'Argos in Milton Keynes') AS KEY_TBL
ON FT_TBL.AccountID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO
Oh how I like technology (when it works :mad: )
Thanks!
FREETEXTTABLE
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
http://support.microsoft.com/?kbid=892924
HTH
|||Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.
The Search tool is working fine now in my website:www.engineeringprojects.net.
|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||Sorry for being too late...
The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.
FREETEXTTABLE
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
http://support.microsoft.com/?kbid=892924
HTH
|||Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.
The Search tool is working fine now in my website:www.engineeringprojects.net.
|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||Sorry for being too late...
The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.
Freetexttable
I am very new to the freetext searching and have not been
able to decipher some of the documentation steps in making
FreeTextTable work.
I have a table with a primary key of "KeyID", and my
search index is created on varchar fields of Problem and
Cause. I also have a field called WorkOrderID which is not
part of the index.
Basically I want to return the rank, the WorkOrderID, and
Problem/Cause if it matches. Pretty simple I should think.
I haven't found any line by line explanation of how the
query works.
It would be helpful if you could post the entire schema of this table. Here
is my stab in the dark as to what it would look like.
select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc
This will search for hits in any of the full text indexed columns, and will
search across columns. So if you are searching for James Bond, and one row
has the word James in the problem column and Bond in the Cause column this
will be a "hit"
If this won't work for you, you may have to do the more expensive:
select distinct KeyID, Problem, Cause, Rank=a.Rank +b.rank from authors,
FreeTextTable(TableName,Problem,'SearchPhrase') as a,
FreeTextTable(TableName,Cause,'SearchPhrase') as b where
a.[key]=KEYID or b.[key]=KEYID
order by Rank Desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:08f601c49cb2$87024080$a501280a@.phx.gbl...
> I am very new to the freetext searching and have not been
> able to decipher some of the documentation steps in making
> FreeTextTable work.
> I have a table with a primary key of "KeyID", and my
> search index is created on varchar fields of Problem and
> Cause. I also have a field called WorkOrderID which is not
> part of the index.
> Basically I want to return the rank, the WorkOrderID, and
> Problem/Cause if it matches. Pretty simple I should think.
> I haven't found any line by line explanation of how the
> query works.
>
|||I will try that also... table is:
KeyID - PK, int, identity
Problem - Varchar(2048)
Cause - varchar(2048)
WorkOrderID - int
All fields allow nulls except the PK of course.
able to decipher some of the documentation steps in making
FreeTextTable work.
I have a table with a primary key of "KeyID", and my
search index is created on varchar fields of Problem and
Cause. I also have a field called WorkOrderID which is not
part of the index.
Basically I want to return the rank, the WorkOrderID, and
Problem/Cause if it matches. Pretty simple I should think.
I haven't found any line by line explanation of how the
query works.
It would be helpful if you could post the entire schema of this table. Here
is my stab in the dark as to what it would look like.
select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc
This will search for hits in any of the full text indexed columns, and will
search across columns. So if you are searching for James Bond, and one row
has the word James in the problem column and Bond in the Cause column this
will be a "hit"
If this won't work for you, you may have to do the more expensive:
select distinct KeyID, Problem, Cause, Rank=a.Rank +b.rank from authors,
FreeTextTable(TableName,Problem,'SearchPhrase') as a,
FreeTextTable(TableName,Cause,'SearchPhrase') as b where
a.[key]=KEYID or b.[key]=KEYID
order by Rank Desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:08f601c49cb2$87024080$a501280a@.phx.gbl...
> I am very new to the freetext searching and have not been
> able to decipher some of the documentation steps in making
> FreeTextTable work.
> I have a table with a primary key of "KeyID", and my
> search index is created on varchar fields of Problem and
> Cause. I also have a field called WorkOrderID which is not
> part of the index.
> Basically I want to return the rank, the WorkOrderID, and
> Problem/Cause if it matches. Pretty simple I should think.
> I haven't found any line by line explanation of how the
> query works.
>
|||I will try that also... table is:
KeyID - PK, int, identity
Problem - Varchar(2048)
Cause - varchar(2048)
WorkOrderID - int
All fields allow nulls except the PK of course.
FREETEXTTABLE
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@.SearchString nvarchar(500),
@.CultureName nvarchar(50),
@.HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@.SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@.SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
AND FL.CultureID=dbo.GetCultureID(@.CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@.SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@.CultureName)
AND PL.CultureID=dbo.GetCultureID(@.CultureName)
SELECT @.HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @.HowManyResults int
EXEC SearchProject '?????','ar-SA',@.HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'?????') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
http://support.microsoft.com/?kbid=892924
HTH
|||Thank you very much for your respond and important information..I have found a solution for my problem before i view your message..The solution was changing the sql collation and the windows collation.
The Search tool is working fine now in my website:www.engineeringprojects.net.
|||could you provide me the changed code using windows collation and sql collation. plz mail me to ananth.ramasamymeenachi@.cognizant.com|||Sorry for being too late...
The code is the same but you can change the collation visually in the table designer..I dont remember what was the SqlCollation exactlly but the windows Collation was arabic.
FREETEXTTABLE
The following FREETEXTTABLE query works, but I'm getting all rows returned.
The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query (bottom), all the
results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
-- This FREETEXTABLE query returns all row in the Stock table. Regardless of
any matches.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
--This FREETEXT query works as expected
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
FREETEXTTABLE returns a table of all the keys and their ranking for your
search. Based on what you are saying, I am guessing that it also returns
items that don't match and they probably have a ranking of 0.
So you can filter out those by adding a WHERE clause like this:
WHERE
FS_TABLE.RANK > 0
For me, I use where it's greater than 20 since I only care about the 80%
that are relevant (in my case).
Hope that helps.
Brien King
"shank" <shank@.tampabay.rr.com> wrote in message
news:%23y$G1uFYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> The following FREETEXTTABLE query works, but I'm getting all rows
returned.
> The highest ranked are
> at the top like expected, but it's also returning all rows in the Stock
> table that have no match whatsoever. In my FREETEXT query (bottom), all
the
> results
> had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
> expecting a temp table to be created with the results. However, the code
> below is actually joining the created table. I don't get it.
> How do I get only matching results in the FREETEXTTABLE?
> thanks!
> -- This FREETEXTABLE query returns all row in the Stock table. Regardless
of
> any matches.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC
The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query (bottom), all the
results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
-- This FREETEXTABLE query returns all row in the Stock table. Regardless of
any matches.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
--This FREETEXT query works as expected
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
FREETEXTTABLE returns a table of all the keys and their ranking for your
search. Based on what you are saying, I am guessing that it also returns
items that don't match and they probably have a ranking of 0.
So you can filter out those by adding a WHERE clause like this:
WHERE
FS_TABLE.RANK > 0
For me, I use where it's greater than 20 since I only care about the 80%
that are relevant (in my case).
Hope that helps.
Brien King
"shank" <shank@.tampabay.rr.com> wrote in message
news:%23y$G1uFYEHA.1264@.TK2MSFTNGP11.phx.gbl...
> The following FREETEXTTABLE query works, but I'm getting all rows
returned.
> The highest ranked are
> at the top like expected, but it's also returning all rows in the Stock
> table that have no match whatsoever. In my FREETEXT query (bottom), all
the
> results
> had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
> expecting a temp table to be created with the results. However, the code
> below is actually joining the created table. I don't get it.
> How do I get only matching results in the FREETEXTTABLE?
> thanks!
> -- This FREETEXTABLE query returns all row in the Stock table. Regardless
of
> any matches.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC
freetextable and multiple tables
In the below FREETEXTTABLE query, I have the SELECT fields indexed in the FT
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.
Here is an example of such a parent child relationship:
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.
This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
usage is:
EXEC search 'microsoft sql server',''
Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @.@.error system variable.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the
FT
> catalog. I should be getting results and I'm not. The problem, as I see
it,
> is that I need to be searching all 3 tables when actually I'm only
searching
> 1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria).
At
> the very bottom, I removed 2 tables and the query works. How do I search
all
> 3 tables and get results?
> thanks
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> ========================================
> --This one works...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> --ItemTitles.Title,
> --ItemTitles.Artist,
> --ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
>
|||Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
-- --
1 06897
1 19713
*/
select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
-- -- -- -- -
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/
In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
-- -- --
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:
SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-- --
Davolio Nancy
(1 row(s) affected)
*/
Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> you can't query multiple tables in a single full text query unless there
is
> some sort of a parent child relationship between ItemStock.OrderNo and
> ItemHardware, and ItemTitles.
> Here is an example of such a parent child relationship:
>
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I am storing the text data in the child table and both the parent and the
> child share a common pk. There is no DRI constraint.
>
> This makes the ContainsTable query a little more complex. Here is my
stored
> procedure that I use to query this table.
>
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> usage is:
>
> EXEC search 'microsoft sql server',''
>
> Notice that what we are doing is returning the results set from MSSearch
as
> a derived table and joining this derived table (named FTTABLE) against the
> parent table. The child table which contains our text information is not
> queried as all. Also notice that I am not doing any error checking or
> returning any return codes from this stored procedure. My experience is
that
> building the search string can be made error free, and the errors
generated[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
FROM ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo = ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS FS_TABLE ON
FS_TABLE.[KEY] = ItemStock.OrderNo
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> Not sure if this going to help or not. Below are my 3 tables. I have a
PK/FK
> relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
> ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
> rebuilt the catalog and still not getting results. Any further help would
be
> appreciated. This is my query...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
> CREATE TABLE [dbo].[ItemStock] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_Update] [datetime] NULL ,
> [Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Inv] [decimal](5, 0) NULL ,
> [d_Date] [datetime] NULL ,
> [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManSort] [int] NULL ,
> [Weight] [decimal](5, 2) NULL ,
> [Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemTitles] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SortKey] [decimal](5, 0) NOT NULL ,
> [Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemHardware] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
catalog. I should be getting results and I'm not. The problem, as I see it,
is that I need to be searching all 3 tables when actually I'm only searching
1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria). At
the very bottom, I removed 2 tables and the query works. How do I search all
3 tables and get results?
thanks
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
========================================
--This one works...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
--ItemTitles.Title,
--ItemTitles.Artist,
--ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
you can't query multiple tables in a single full text query unless there is
some sort of a parent child relationship between ItemStock.OrderNo and
ItemHardware, and ItemTitles.
Here is an example of such a parent child relationship:
CREATE TABLE [dbo].[Parent] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Size] [int] NULL ,
[CreateDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TextTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am storing the text data in the child table and both the parent and the
child share a common pk. There is no DRI constraint.
This makes the ContainsTable query a little more complex. Here is my stored
procedure that I use to query this table.
CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
AS
SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
'+char(39)+char(34)
SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
FTTABLE.[key]'
SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
desc,[KEY] '
exec (@.@.strSearch)
usage is:
EXEC search 'microsoft sql server',''
Notice that what we are doing is returning the results set from MSSearch as
a derived table and joining this derived table (named FTTABLE) against the
parent table. The child table which contains our text information is not
queried as all. Also notice that I am not doing any error checking or
returning any return codes from this stored procedure. My experience is that
building the search string can be made error free, and the errors generated
by MSSearch will be handled by the calling application and in some cases
will not be trappable with the @.@.error system variable.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"shank" <shank@.tampabay.rr.com> wrote in message
news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> In the below FREETEXTTABLE query, I have the SELECT fields indexed in the
FT
> catalog. I should be getting results and I'm not. The problem, as I see
it,
> is that I need to be searching all 3 tables when actually I'm only
searching
> 1 table in this expression: FREETEXTTABLE(ItemStock, *, @.SearchCriteria).
At
> the very bottom, I removed 2 tables and the query works. How do I search
all
> 3 tables and get results?
> thanks
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> ========================================
> --This one works...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> --ItemTitles.Title,
> --ItemTitles.Artist,
> --ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
>
|||Hi Hilary,
While I respectively disagree that a "parent child relationship" must exist
to use multiple FT-enabled tables with FREETEXTTABLE or CONTAINSTABLE
query, I suspect that what you really meant was that a Primary key / Foreign
key relationship must exist between the tables to allow a join. It's a small
point, but not all PK / FK relationships have to be "parent child"
relationships. For example take the two tables Employees and
EmployeeTerritories in the Northwind database, while they are not in a
classic parent/child relationship, they do in fact share a PK/FK
relationship on their respective EmployeeID columns, specifically:
use Northwind
go
exec sp_help EmployeeTerritories -- PK is PK_EmployeeTerritories
(EmployeeID, TerritoryID)
exec sp_help Employees -- PK is PK_Employees (EmployeeID)
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID
-- --
1 06897
1 19713
*/
select EmployeeID, LastName, FirstName, Title, Address from Employees where
EmployeeID = 1
/* returns:
EmployeeID LastName FirstName Title
Address
-- -- -- -- -
1 Davolio Nancy Sales Representative
507 - 20th Ave. E.
*/
In order for the EmployeeTerritories to be FT Indexed, the table must be
altered to add a single column, non-nullable key and non-clustered, unique
index, for example:
ALTER TABLE EmployeeTerritories ADD ET_Ident int identity (1, 1) NOT NULL
go -- returns: 49 rows affected.
select * from EmployeeTerritories where EmployeeID = 1
/* returns:
EmployeeID TerritoryID ET_Ident
-- -- --
1 06897 1
1 19713 2
*/
CREATE UNIQUE INDEX ET_Ident_IDX on EmployeeTerritories(ET_Ident)
go
Then you can create a FT Catalog and FT-enable both tables - Employees and
EmployeeTerritories - and then run a Full Population on both tables and you
can execute a multiple FT-enabled FTS query, such as the one below:
SELECT distinct e.LastName, e.FirstName from Employees AS e,
EmployeeTerritories t,
containstable(Employees, FirstName, 'Nancy') as A,
containstable(EmployeeTerritories, TerritoryID, '06897') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName
-- --
Davolio Nancy
(1 row(s) affected)
*/
Shank, you should now be able to take the above example code and alter it to
fit your tables and selection criteria.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uzlWtk6ZEHA.3476@.tk2msftngp13.phx.gbl...
> you can't query multiple tables in a single full text query unless there
is
> some sort of a parent child relationship between ItemStock.OrderNo and
> ItemHardware, and ItemTitles.
> Here is an example of such a parent child relationship:
>
> CREATE TABLE [dbo].[Parent] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Author] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [vpath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [characterization] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [Size] [int] NULL ,
> [CreateDate] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[TextTable] (
> [pk] [int] IDENTITY (1, 1) NOT NULL ,
> [textcol] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> I am storing the text data in the child table and both the parent and the
> child share a common pk. There is no DRI constraint.
>
> This makes the ContainsTable query a little more complex. Here is my
stored
> procedure that I use to query this table.
>
> CREATE PROC search @.search nvarchar(20), @.@.strSearch nvarchar(500)
> AS
> SET @.@.strSearch='SELECT Title, author, vpath, characterization, size,
> createDate from parent as P,_ CONTAINSTABLE(TextTable, TextCol,
> '+char(39)+char(34)
> SELECT @.@.strSearch=@.@.strSearch + @.search +char(34)+char(39)
> SELECT @.@.strSearch=@.@.strSearch+ ', 100) AS FTTABLE WHERE P.pk=
> FTTABLE.[key]'
> SELECT @.@.strSearch= @.@.strSearch + ' AND size > 10000 ORDER BY FTTABLE.rank
> desc,[KEY] '
> exec (@.@.strSearch)
>
> usage is:
>
> EXEC search 'microsoft sql server',''
>
> Notice that what we are doing is returning the results set from MSSearch
as
> a derived table and joining this derived table (named FTTABLE) against the
> parent table. The child table which contains our text information is not
> queried as all. Also notice that I am not doing any error checking or
> returning any return codes from this stored procedure. My experience is
that
> building the search string can be made error free, and the errors
generated[vbcol=seagreen]
> by MSSearch will be handled by the calling application and in some cases
> will not be trappable with the @.@.error system variable.
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:u9IDOK6ZEHA.2516@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> FT
> it,
> searching
@.SearchCriteria).
> At
> all
> ItemTitles.OrderNo
>
|||Not sure if this going to help or not. Below are my 3 tables. I have a PK/FK
relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
rebuilt the catalog and still not getting results. Any further help would be
appreciated. This is my query...
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler*" '
SELECT
ItemStock.OrderNo,
ItemStock.Description,
ItemStock.Category,
ItemStock.s_Type,
ItemStock.Manuf,
ItemStock.Label,
ItemTitles.Title,
ItemTitles.Artist,
ItemHardware.m_Specs,
ItemStock.ManCode
FROM
ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo =
ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
WHERE
FS_TABLE.RANK > 0
ORDER BY
FS_TABLE.Rank DESC
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
CREATE TABLE [dbo].[ItemStock] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[d_Update] [datetime] NULL ,
[Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inv] [decimal](5, 0) NULL ,
[d_Date] [datetime] NULL ,
[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManSort] [int] NULL ,
[Weight] [decimal](5, 2) NULL ,
[Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemTitles] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SortKey] [decimal](5, 0) NOT NULL ,
[Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemHardware] (
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
|||Shank,
I've been able to use the below table structures and inserted some sample
data and test your query along with the below modified queries. I've also
attached a .SQL script file - Containstable_multiple_tables.sql that
contains the below queries as well as sample data.
I believe that the issue here is two fold - 1) using a common variable
(@.SearchCriteria) where a single word must exist in all tables columns, and
2) your SQL FTS FREETEXTTABLE query only references one table (ItemStock),
while the below Modified Original query using a common word (row) return the
search word from all three tables and is more flexible as you can use both
an OR as well as an AND condition between the freetexttable or containstable
clauses. Please, review carefully the attached sql file, the data as well
as the queries and experiment with them on either Win2003 or WinXP machines
as you may get different results, due to the os-supplied wordbreaker
depending upon your actual data.
-- Original problem query, works with the above data, but only with a common
word "row" is queried from all three tables.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- primary search column is ItemStock.Label
(other columns, left NULL)
SELECT ItemStock.OrderNo,ItemStock.Label
FROM ItemStock
INNER JOIN ItemTitles ON ItemStock.OrderNo = ItemTitles.OrderNo
INNER JOIN ItemHardware ON ItemStock.OrderNo = ItemHardware.OrderNo
INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS FS_TABLE ON
FS_TABLE.[KEY] = ItemStock.OrderNo
ORDER BY FS_TABLE.Rank DESC
-- 1st test: 4 rows becasue only ItemStock is referenced in FREETEXTTABLE
clasue
-- Modifed Original query
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "row*" ' -- Test with both containstable & freetext
AND OR Betewen for expected results
SELECT distinct e.OrderNo, e.Label -- disctinct requried to get 5 rows,
still not exactly same as above...
from ItemStock AS e, ItemTitles t, ItemHardware h,
freetexttable(ItemStock, Label, @.SearchCriteria) as A,
freetexttable(ItemTitles, Title, @.SearchCriteria) as B,
freetexttable(ItemHardware, s_page, @.SearchCriteria) as C
where
A.[KEY] = e.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
-- 1st test: NOT the same as above as this query gets 5 rows, additional row
is "This is Label 0005 of row five Interface"
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:NKEIc.15669$KP6.716193@.twister.tampabay.rr.co m...
> Not sure if this going to help or not. Below are my 3 tables. I have a
PK/FK
> relationship between ItemStock.OrderNo = ItemTitles.OrderNo and also
> ItemStock.OrderNo = ItemHardware.OrderNo. After setting up the indexes, I
> rebuilt the catalog and still not getting results. Any further help would
be
> appreciated. This is my query...
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler*" '
> SELECT
> ItemStock.OrderNo,
> ItemStock.Description,
> ItemStock.Category,
> ItemStock.s_Type,
> ItemStock.Manuf,
> ItemStock.Label,
> ItemTitles.Title,
> ItemTitles.Artist,
> ItemHardware.m_Specs,
> ItemStock.ManCode
> FROM
> ItemStock
> INNER JOIN ItemTitles ON ItemStock.OrderNo =
ItemTitles.OrderNo
> INNER JOIN ItemHardware ON ItemStock.OrderNo =
> ItemHardware.OrderNo
> INNER JOIN FREETEXTTABLE(ItemStock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = ItemStock.OrderNo
> WHERE
> FS_TABLE.RANK > 0
> ORDER BY
> FS_TABLE.Rank DESC
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
> CREATE TABLE [dbo].[ItemStock] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Label] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SoftHard] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [d_Update] [datetime] NULL ,
> [Exclusive] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Affiliate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NewRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Inv] [decimal](5, 0) NULL ,
> [d_Date] [datetime] NULL ,
> [Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SingleArtist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TypeDescrip] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cased] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Packed] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Manuf] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ManSort] [int] NULL ,
> [Weight] [decimal](5, 2) NULL ,
> [Icons75] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons100] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons200] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Icons300] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemTitles] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Title] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Artist] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SortKey] [decimal](5, 0) NOT NULL ,
> [Disc_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MP3Files] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemHardware] (
> [OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [s_page] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_Thumb] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [s_pic] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFScale] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [m_Specs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PDFSpecs] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Media] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Cass] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CDG] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [VCD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DVD] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
begin 666 Containstable_mutiple_tables.sql
M#0HM+0EF:6QE;F%M93H@.0V]N=&%I;G-T86)L95]M=71I<&QE7W1A8FQE<RYS
M<6P-"BTM"7!U<G!O<V4Z('1O(&1O8W5M96YT(&AO=R!T;R!U<V4@.8V ]N=&%I
M;G-T86)L92!A;F0@.9G)E971E>'1T86)L92!I;B!M=71I<&QE($944 R!Q=65R
M:65S#0HM+0EM;V1I9FEE9#H@.,3 Z,#4@.4$T@.-R\Q,B\R,# T#0H-"G5S92!S
M<6QF=',-"F=O#0IS96QE8W0@.0$!V97)S:6]N("TM($UI8W)O<V]F="!344P@.
M4V5R=F5R(" R,# P("T@.."XP,"XW-C @.;VX@.16YT97)P<FES92!%9&ET:6]N
M(&]N(%=I;F1O=W,@.3E0@.-2XR("A"=6EL9" S-SDP.B I#0HO*@.T*+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+ 2TM#0I-:6-R
M;W-O9G0@.4U%,(%-E<G9E<B @.,C P," M(#@.N,# N-S8P("A);G1E;"!8.#8I
M("T@.1&5V96QO<&5R($5D:71I;VX@.;VX@.5VEN9&]W<R!.5" U+C$@.*$)U:6QD
M(#(V,# Z(%-E<G9I8V4@.4&%C:R Q*0T*4$LO1DL@.<F5L871I;VYS:&EP(&)E
M='=E96X@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M5&ET;&5S+D]R9&5R3F\@.
M86YD(&%L<V\@.271E;5-T;V-K+D]R9&5R3F\@./2!)=&5M2&%R9'=A<F4N3W)D
M97).;RX@.#0HJ+PT*#0HM+2!$969I;F4@.=&%B;&5S+BXN#0I#4 D5!5$4@.5$%"
M3$4@.6V1B;UTN6TET96U3=&]C:UT@.* T*(%M/<F1E<DYO72!;=F%R8VAA<ET@.
M*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3D]4
M($Y53$P@.+" M+2!02R!)=&5M4W1O8VL@.+2!U;FEQ=64@./PT*(%M,86)E;%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y/5"!.54Q,("P-"B!;4V]F=$AA<F1=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;36%N0V]D95T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ
M7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$97-C<FEP=&EO;ET@.6W9A
M<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MD7U5P9&%T95T@.6V1A=&5T:6UE72!.54Q,("P-"B!;
M17AC;'5S:79E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?
M1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6T%F9FEL:6%T95T@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y53$P@.+ T*(%M.97=296QE87-E72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%
M(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U-T871U
M<UT@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M);G9=(%MD96-I;6%L72@.U+" P*2!.54Q,
M("P-"B!;9%]$871E72!;9&%T971I;65=($Y53$P@.+ T*(%M#871E9V]R>5T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M3:6YG;&5!<G1I<W1=(%MV87)C:&%R72 H,RD@.
M0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*
M(%MS7U1Y<&5=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'
M96YE<F%L7T-0,5]#25]!4R!.54Q,("P-"B!;5'EP941E<V-R:7!=(%MV87)C
M:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3E5,3" L#0H@.6T-A<V5D72!;=F%R8VAA<ET@.*#4P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!A8VME9%T@.
M6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M-86YU9ET@.6W9A<F-H87)=("@.U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-86Y3
M;W)T72!;:6YT72!.54Q,("P-"B!;5V5I9VAT72!;9&5C:6UA;%TH-2P@.,BD@.
M3E5,3" L#0H@.6TEC;VYS-S5=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-1
M3%],871I;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TEC;VYS,3 P
M72!;=F%R8VAA<ET@.*#$P,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?
M0U Q7T-)7T%3($Y53$P@.+ T*(%M)8V]N<S(P,%T@.6W9A<F-H87)=("@.Q,# I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;26-O;G,S,#!=(%MV87)C:&%R72 H,3 P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6TE$72!;;G5M97)I8UTH
M,3 L(# I($E$14Y42519("@.Q+" Q*2!.3U0@.3E5,3" M+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM/B!O<B!02R _(&IU<W0@.9F]R($94($EN9&5X(#\-
M"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"3$4@.271E; 5-T;V-K(%=)
M5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.0TQ54U1%4D5$("A/<F1E<DYO
M*0T*9V\-"BTM($EN<V5R="!T97-T('-A;7!L92!D871A#0II;G-E<G0@.:6YT
M;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES($QA8F5L(# P
M,#$@.;V8@.<F]W(&]N92!":6QL>2!*;V5L)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3 E5,3"Q.54Q,
M+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P,B<L
M("=4:&ES(&ES($QA8F5L(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E; 5-T;V-K('9A
M;'5E<R H)S P,#,G+" G5&AI<R!I<R!,86)E;" P,# S(&]F(')O=R!T:')E
M92!344P@.4V5R=F5R)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PI#0II;G-E
M<G0@.:6YT;R!)=&5M4W1O8VL@.=F%L=65S("@.G,# P-"<L("=4:&ES(&ES($QA
M8F5L(# P,#0@.;V8@.<F]W(&9O=7(@.3U)!0TQ%)RQ.54Q,+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"Q.
M54Q,+$Y53$PI#0HM+2!R;W<@.3D]4(&EN($ET96U4:71L97,L(&)U="!I<R!I
M;B!)=&5M2&%R9'=A<F4-"FEN<V5R="!I;G1O($ET96U3=&]C:R!V86QU97,@.
M*"<P,# U)RP@.)U1H:7,@.:7,@.3&%B96P@.,# P-2!O9B!R;W<@.9FEV92!);G1E
M<F9A8V4G+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"D-"F=O#0H-"D-214%4
M12!404),12!;9&)O72Y;271E;51I=&QE<UT@.* T*(%M/<F1E<DYO72!;=F%R
M8VAA<ET@.*#4P*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#4#%?0TE?
M05,@.3D]4($Y53$P@.+ T*(%M4:71L95T@.6W9A<F-H87)=("@.W,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y/5"!.54Q,("P-"B!;
M07)T:7-T72!;=F%R8VAA<ET@.*#8P*2!#3TQ,051%(%-13%],871I;C%?1V5N
M97)A;%]#4#%?0TE?05,@.3D]4($Y53$P@.+ T*(%M,;V-A=&EO;ET@.6W9A<F-H
M87)=("@.U,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3
M($Y/5"!.54Q,("P-"B!;4V]R=$ME>5T@.6V1E8VEM86Q=*#4L(# I($Y/5"!.
M54Q,("P-"B!;1&ES8U])1%T@.6W9A<F-H87)=("@.U,"D@.0T],3$%412!344Q?
M3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-4#-&:6QE<UT@.
M6W9A<F-H87)=("@.Q,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0
M,5]#25]!4R!.54Q,("P-"B!;241=(%MN=6UE<FEC72@.Q,"P@.,"D@.241%3E1)
M5%D@.*#$L(#$I($Y/5"!.54Q,#0HI($].(%M04DE-05)970T*1T\-"D%,5$52
M(%1!0DQ%($ET96U4:71L97,@.5TE42"!.3T-(14-+($%$1"!04DE-05)9($M%
M62!#3%535$52140@.*$]R9&5R3F\I#0IG;PT*+2T@.26YS97)T('1E<W0@.<V%M
M<&QE(&1A=&$-"FEN<V5R="!I;G1O($ET96U4:71L97,@.=F%L=65S("@.G,# P
M,2<L)U1H:7,@.:7,@.5&ET;&4@.,# P,2!O9B!R;W<@.;VYE(%-T<F%N9V5R)RPG
M0FEL;'D@.2F]E;"<L)R!3;VUE<&QA8V4@.:&5R92<L,2Q.54Q,+$Y53$PI#0I I
M;G-E<G0@.:6YT;R!)=&5M5&ET;&5S('9A;'5E<R H)S P,#(G+"=4:&ES(&ES
M(%1I=&QE(# P,#(@.;V8@.<F]W('1W;R!3=&EN9R<L)U-T:6YG)RPG(%-O;65P
M;&%C92!T:&5R92<L.2Q.54Q,+$Y53$PI#0II;G-E<G0@.:6YT;R!)=&5M5&ET
M;&5S('9A;'5E<R H)S P,#,G+"=4:&ES(&ES(%1I=&QE(# P,#,@.;V8@.<F]W
M('1H<F5E(%-13"!397)V97(G+"=-:6-R;W-O9G0G+"<@.4V]M97!L86-E(&5L
M<V4G+#@.L3E5,3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;51I= &QE<R!V86QU
M97,@.*"<P,# T)RPG5&AI<R!I<R!4:71L92 P,# T(&]F(')O=R!F;W5R($]2
M04-,12<L)T]R86-L92<L)R!3;VUE<&QA8V4@.96QS92<L-"Q.54Q,+$Y53$PI
M#0IG;PT*#0HM+2!)=&5M4W1O8VLN3W)D97).;R ]($ET96U(87)D=V%R92Y/
M<F1E<DYO+B -"D-214%412!404),12!;9&)O72Y;271E;4AA<F1W87)E72 H
M#0H@.6T]R9&5R3F]=(%MV87)C:&%R72 H-3 I($-/3$Q!5$4@.4U%,7TQA=&EN
M,5]'96YE<F%L7T-0,5]#25]!4R!.3U0@.3E5,3" L#0H@.6W-?<&%G95T@.6W9A
M<F-H87)=("@.V,"D@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%MS7U1H=6UB72!;=F%R8VAA<ET@.*#$U,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%MS7W!I
M8UT@.6W9A<F-H87)=("@.Q-3 I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L
M7T-0,5]#25]!4R!.54Q,("P-"B!;4$1&4V-A;&5=(%MV87)C:&%R72 H-3 I
M($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#25]!4R!.54Q,("P-
M"B!;;5]3<&5C<UT@.6W9A<F-H87)=("@.X,# P*2!#3TQ,051%(%-13%],871I
M;C%?1V5N97)A;%]#4#%?0TE?05,@.3E5,3" L#0H@.6U!$1E-P96-S72!;=F%R
M8VAA<ET@.*#@.P,# I($-/3$Q!5$4@.4U%,7TQA=&EN,5]'96YE<F%L7T-0,5]#
M25]!4R!.54Q,("P-"B!;3F]T97-=(%MV87)C:&%R72 H.# P,"D@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M-961I
M85T@.6W9A<F-H87)=("@.S*2!#3TQ,051%(%-13%],871I;C%?1V5N97)A;%]#
M4#%?0TE?05,@.3E5,3" L#0H@.6T-A<W-=(%MV87)C:&%R72 H,RD@.0T],3$%4
M12!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M#1$==
M(%MV87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q
M7T-)7T%3($Y53$P@.+ T*(%M60T1=(%MV87)C:&%R72 H,RD@.0T],3$%412!3
M44Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)7T%3($Y53$P@.+ T*(%M$5D1=(%MV
M87)C:&%R72 H,RD@.0T],3$%412!344Q?3&%T:6XQ7T=E;F5R86Q?0U Q7T-)
M7T%3($Y53$P@.+ T*(%M)1%T@.6VYU;65R:6-=*#$P+" P*2!)1$5.5$E462 H
M,2P@.,2D@.3D]4($Y53$P-"BD@.3TX@.6U!224U!4EE=#0I'3PT*04Q415(@.5$%"
M3$4@.271E;4AA<F1W87)E(%=)5$@.@.3D]#2$5#2R!!1$0@.4%))34%262!+15D@.
M0TQ54U1%4D5$("A/<F1E<DYO*0T*9V\-"B\J("TM($=O="!T:&4@.9F]L;&]W
M:6YG('=A<FYI;F<@.=VAE;B!C<F5A=&EN9R!T86)L93H@.271E; 4AA<F1W87)E
M#0I787)N:6YG.B!4:&4@.=&%B;&4@.)TET96U(87)D=V%R92<@.: &%S(&)E96X@.
M8W)E871E9"!B=70@.:71S(&UA>&EM=6T@.<F]W('-I>F4@.*#(T-3,R*2!E>&-E
M961S('1H92!M87AI;75M(&YU;6)E<B!O9B!B>71E<R!P97(@.< F]W("@.X,#8P
M*2X@.#0I)3E-%4E0@.;W(@.55!$051%(&]F(&$@.<F]W(&EN('1H:7,@.=&%B;&4@.
M=VEL;"!F86EL(&EF('1H92!R97-U;'1I;F<@.<F]W(&QE;F=T:"!E>&-E961S
M(#@.P-C @.8GET97,N#0HJ+PT*+2T@.26YS97)T(%-A;7!L92!D871A#0II;G-E
M<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S("@.G,# P,2<L("=4:&ES(&ES
M($QA8F5L(# P,#$@.;V8@.<F]W(&]N92!3=')A;F=E<B<L3E5,3"Q.54Q,+$Y5
M3$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3 "Q.54Q,*0T*
M:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A;'5E<R H)S P,#(G+" G5&AI
M<R!I<R!,86)E;" P,# R(&]F(')O=R!T=V\@.4W1I;F<G+$Y53$PL3E5,3"Q.
M54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53 $PL3E5,3"D-
M"FEN<V5R="!I;G1O($ET96U(87)D=V%R92!V86QU97,@.*"<P, # S)RP@.)U1H
M:7,@.:7,@.3&%B96P@.,# P,R!O9B!R;W<@.=&AR964@.4U%,(%-E<G9E<B<L3E5,
M3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y53$PL3E5,
M3"Q.54Q,*0T*:6YS97)T(&EN=&\@.271E;4AA<F1W87)E('9A; '5E<R H)S P
M,#0G+" G5&AI<R!I<R!,86)E;" P,# T(&]F(')O=R!F;W5R($]204-,12<L
M3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.5 4Q,+$Y53$PL
M3E5,3"Q.54Q,*0T*+2T@.<F]W($Y/5"!I;B!)=&5M5&ET;&5S+"!B=70@.:7,@.
M:6X@.271E;5-T;V-K#0II;G-E<G0@.:6YT;R!)=&5M2&%R9'=A<F4@.=F%L=65S
M("@.G,# P-2<L("=4:&ES(&ES($QA8F5L(# P,#4@.;V8@.<F]W(&9I=F4@.26YT
M97)F86-E)RQ.54Q,+$Y53$PL3E5,3"Q.54Q,+$Y53$PL3E5,3"Q.54Q,+ $Y5
M3$PL3E5,3"Q.54Q,+$Y53$PI#0IG;PT*#0HM+2!C;VYF:7)M( '-A;7!L92!D
M871A(&%N9"!&5"UE;F%B;&4@.=&%B;&5S('9I82!%;G1E<G!R: 7-E($UA;F%G
M97(N+BX-"G-E;&5C=" J(&9R;VT@.271E;5-T;V-K#0HM+2!T<G5N8V%T92!T
M86)L92!)=&5M4W1O8VL-"G-E;&5C=" J(&9R;VT@.271E;51I=&QE<PT*+2T@.
M=')U;F-A=&4@.=&%B;&4@.271E;51I=&QE<PT*<V5L96-T("H@.9G)O;2!)=&5M
M2&%R9'=A<F4-"BTM('1R=6YC871E('1A8FQE($ET96U(87)D=V%R90T*9V \-
M"@.T*+2T@.#0HM+2!/<FEG:6YA;"!P<F]B;&5M('%U97)Y+"!W;W)K<R!W:71H
M('1H92!A8F]V92!D871A+"!B=70@.;VYL>2!W:71H(&$@.8V]M;6]N('=O<F0@.
M(G)O=R(@.:7,@.<75E<FEE9"!F<F]M(&%L;"!T:')E92!T86)L97,N#0I$14-,
M05)%($!396%R8VA#<FET97)I82!V87)C:&%R*#$P,"D-"E-%5"! 4V5A<F-H
M0W)I=&5R:6$@./2 G(")R;W<J(B G("TM('!R:6UA<GD@.<V5A<F-H(&-O;'5M
M;B!I<R!)=&5M4W1O8VLN3&%B96P@.*&]T:&5R(&-O;'5M;G,L(&QE9G0@.3E5,
M3"D-"E-%3$5#5"!)=&5M4W1O8VLN3W)D97).;RQ)=&5M4W1O8VLN3&%B9 6P@.
M#0I&4D]-($ET96U3=&]C:PT*("!)3DY%4B!*3TE.($ET96U4:71L97,@.("!/
M3B!)=&5M4W1O8VLN3W)D97).;R ]($ET96U4:71L97,N3W)D97).;PT*("!)
M3DY%4B!*3TE.($ET96U(87)D=V%R92!/3B!)=&5M4W1O8VLN3W)D97).;R ]
M($ET96U(87)D=V%R92Y/<F1E<DYO#0H@.($E.3D52($I/24X@.1E)%151%6%14
M04),12A)=&5M4W1O8VLL("HL($!396%R8VA#<FET97)I82D@.0 5,@.1E-?5$%"
M3$4@.3TX@.1E-?5$%"3$4N6TM%65T@./2!)=&5M4W1O8VLN3W)D97).;PT*(" @.
M("!/4D1%4B!"62!&4U]404),12Y286YK($1%4T,-"BTM(#%S="!T97-T.B T
M(')O=W,@.8F5C87-U92!O;FQY($ET96U3=&]C:R!I<R!R969E<F5N8V5D(&EN
M($92145415A45$%"3$4@.8VQA<W5E#0H-"@.T*+2T@.36]D:69E9"!/<FEG:6YA
M;"!Q=65R>0T*1$5#3$%212! 4V5A<F-H0W)I=&5R:6$@.=F%R8VAA<B@.Q,# I
M#0I3150@.0%-E87)C:$-R:71E<FEA(#T@.)R B<F]W*B(@.)R @.+2T@.5&5S="!W
M:71H(&)O=&@.@.8V]N=&%I;G-T86)L92 F(&9R965T97AT($%.1"!/4B!"971E
M=V5N(&9O<B!E>'!E8W1E9"!R97-U;'1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/
M<F1E<DYO+"!E+DQA8F5L("TM(&1I<V-T:6YC="!R97%U<FEE9"!T;R!G970@.
M-2!R;W=S+"!S=&EL;"!N;W0@.97AA8W1L>2!S86UE(&%S(&%B;W9 E+BXN#0IF
M<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S('0L($ET96U(87)D=V%R
M92!H+ T*(" @.("!F<F5E=&5X='1A8FQE*$ET96U3=&]C:RP@.3&%B96PL($!3
M96%R8VA#<FET97)I82D@.87,@.02P-"B @.(" @.9G)E971E>'1T86)L92A)=&5M
M5&ET;&5S+"!4:71L92P@.0%-E87)C:$-R:71E<FEA*2!A<R!"+ T*(" @.("!F
M<F5E=&5X='1A8FQE*$ET96U(87)D=V%R92P@.<U]P86=E+"! 4V5A<F-H0W)I
M=&5R:6$I(&%S($,-"B @.(" @.("!W:&5R90T*(" @.(" @.(" @.02Y;2T5972 ]
M(&4N3W)D97).;R!A;F0-"B @.(" @.(" @.($(N6TM%65T@./2!T+D]R9&5R3F\@.
M86YD#0H@.(" @.(" @.("!#+EM+15E=(#T@.:"Y/<F1E<DYO#0HM+2 Q<W0@.=&5S
M=#H@.3D]4('1H92!S86UE(&%S(&%B;W9E(&%S('1H:7,@.<75E<GD@.9V5T< R U
M(')O=W,L(&%D9&ET:6]N86P@.<F]W(&ES(")4:&ES(&ES($QA8F5L(# P,#4@.
M;V8@.<F]W(&9I=F4@.26YT97)F86-E(@.T*#0H-"BTM(%1E<W0@.=VET:"!B;W1H
M(&-O;G1A:6YS=&%B;&4@.)B!F<F5E=&5X="!!3D0@.3U(@.0F5T97=E; B!E86-H
M(0T*4T5,14-4(&1I<W1I;F-T(&4N3W)D97).;RP@.92Y,86)E; T*9G)O;2!)
M=&5M4W1O8VL@.05,@.92P@.271E;51I=&QE<R!T+ T*(" @.("!C;VYT86EN<W1A
M8FQE*$ET96U3=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O
M;G1A:6YS=&%B;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A; F=E<B<I(&%S
M($(@.+2T@.;F]W(&=E='1I;F<@.<F5S=6QT<R A($UU<W0@.:&%V92!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F0A#0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.
M($$N6TM%65T@./2!E+D]R9&5R3F\@.;W(@.+2T@.3U(@.+2T@.9V5N97)A=&5S(&QA
M<F=E<B!R97-U;'0L('-O('5S92!D:7-T:6YC="!E+D]R9&5R3F\L(')E='5R
M;G,@.-2!R;W=S('=I=&@.@.9&ES=&EN8W0-"B @.(" @.(" @.($(N6TM%65T@./2!T
M+D]R9&5R3F\-"@.T*<V5L96-T("H@.9G)O;2!)=&5M4W1O8VL@.=VAE<F4@.8V]N
M=&%I;G,H*BPG0FEL;'DG*2 M+2!R971U<FYS(#$@.<F]W+@.T*#0H-"BTM($UO
M9&EF:65D($]R:6=I;F%L('%U97)Y+"!R96UO=F5D('1H92! 4V5A<F-H0W)I
M=&5R:6$@.=F%R:6%B;&4@.=&\@.=&5S="!W:71H('1A8FQE(&%N9 "!C;VQU;6XM
M<W!E8VEF:6,@.<V5A<F-H('=O<F1S#0I314Q%0U0@.9&ES=&EN8W0@.92Y/<F1E
M<DYO+"!E+DQA8F5L#0IF<F]M($ET96U3=&]C:R!!4R!E+"!)=&5M5&ET;&5S
M('0L($ET96U(87)D=V%R92!H+ T*(" @.("!C;VYT86EN<W1A8FQE*$ET96U3
M=&]C:RP@.3&%B96PL("=":6QL>2<I(&%S($$L#0H@.(" @.(&-O;G1A:6YS=&%B
M;&4H271E;51I=&QE<RP@.5&ET;&4L("=3=')A;F=E<B<I(&%S( $(L#0H@.(" @.
M(&-O;G1A:6YS=&%B;&4H271E;4AA<F1W87)E+"!S7W!A9V4L("=R; W<G*2!A
M<R!##0H@.(" @.(" @.=VAE<F4-"B @.(" @.(" @.($$N6TM%65T@./2!E+D]R9&5R
M3F\@.86YD("TM($]2(#T@.9V5N97)A=&5S(&UU=&EP;&4@.<F]W<RP@.86YD('1H
M97)E9F]R92!N965D<R @.9&ES=&EN8W0@.92Y/<F1E<DYO+@.T*(" @.(" @.(" @.
M0BY;2T5972 ]('0N3W)D97).;R!A;F0-"B @.(" @.(" @.($,N6TM%65T@./2!H
M+D]R9&5R3F\-"BTM(#%S="!T97-T.B P(')O=W,L('-A;64@.87,@.86)O=F4-
9"@.T*+2T@./&5O9CX-"@.T*#0H-"@.T*#0H-"@.``
`
end
Subscribe to:
Posts (Atom)