Hello all,
it seems to me that ranking of full-text search results does not work
correctly. Here's my scenario:
- one table with a full-text indexed text column and several full-text
indexed meta data columns
- another table with a full-text indexed image column containing .pdf,
..gif, and .jpg files; full-text indexed meta data columns as in the
first table
- a stored procedure that makes a full-text search to the two
full-text indexes (using containstable) and combines the result sets
-Windows 2000 Server, SQL Server 2000 sp3, Adobe PDF iFilter
Now, the search itself seems work as expected. All the rows that
should be returned seem to be returned. The problem is the ranking
with .pdf files: all the .pdf files seem to get a rank value far too
high. A simplified example demonstrates the problem.
TABLE 1
nvc_Heading nvc_Subject txt_Data
Talentless hack missing (long text)
TABLE 2
nvc_Heading nvc_Subject img_Data
Talentless hack missing (binary data)
Making a containstable query to both tables with search term
"talentless hack", the search result from table 1 gets a significantly
lower rank than the result from table 2. It seems that data in column
img_Data is not processed when determining the rank value.
Anyone come across this problem? TIA,
sdr
Recently indexed rows get a higher ranking than rows that have been indexed previously.
Can you change your system time to 1 minute before midnight, wait till the midnight merge occurs and then check to see if the ranking is more consistent.
|||SDR,
Can you confirm if you have "Change Tracking" with "Update Index in
Background" enabled for your FT-enable tables (TABLE 1 and TABLE 2 in your
example)? Also, can you tell us how many rows are in each of these table and
if you're using one FT Catalog for both tables or have one FT Catalog for
each table?
While Hilary suggests changing your server system time, I do not think this
is a good idea as such a change will affect other processes on this server
as well as any date/time sensitive processing for SQL Server table with
datetime columns. An alternative is to change the following Registry key
value (replace <physical_FT_Catalog> your actual FT Catalog folder, for
example: SQL0000500005)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys
ical_FT_Catalog>
indexer:ci:MidNightMasterMergeTimeDelta value= <time>
<time> is Time, in minutes, after midnight at which a master merge will
occur. and the default value is 0. Setting <time> to 60, would force the
Master Merge to occur at 1am.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:51F282E4-24EA-4A61-B4A5-CD8B5FA0F622@.microsoft.com...
> Recently indexed rows get a higher ranking than rows that have been
indexed previously.
> Can you change your system time to 1 minute before midnight, wait till the
midnight merge occurs and then check to see if the ranking is more
consistent.
|||Oops, it looks like this problem was fixed with Sp3.
Sorry about that.
Hilary
|||John,
I tried changing the suggested registry value, but no change in
ranking values took place - actually I didn't notice any merge taking
place either.
Change Tracking is not enabled in my database, but I'm running a full
population to both catalogs every night. I suppose the master merge is
done right after the full population, am I correct?
Both tables have their own, dedicated catalogs. There are approx.
15000-20000 rows in each table.
Any more ideas?
sdr
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#IhuyMWOEHA.3312@.tk2msftngp13.phx.gbl>...
> SDR,
> Can you confirm if you have "Change Tracking" with "Update Index in
> Background" enabled for your FT-enable tables (TABLE 1 and TABLE 2 in your
> example)? Also, can you tell us how many rows are in each of these table and
> if you're using one FT Catalog for both tables or have one FT Catalog for
> each table?
> While Hilary suggests changing your server system time, I do not think this
> is a good idea as such a change will affect other processes on this server
> as well as any date/time sensitive processing for SQL Server table with
> datetime columns. An alternative is to change the following Registry key
> value (replace <physical_FT_Catalog> your actual FT Catalog folder, for
> example: SQL0000500005)
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys
> ical_FT_Catalog>
> indexer:ci:MidNightMasterMergeTimeDelta value= <time>
>
> <time> is Time, in minutes, after midnight at which a master merge will
> occur. and the default value is 0. Setting <time> to 60, would force the
> Master Merge to occur at 1am.
>
> Regards,
> John
>
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:51F282E4-24EA-4A61-B4A5-CD8B5FA0F622@.microsoft.com...
> indexed previously.
> midnight merge occurs and then check to see if the ranking is more
> consistent.
|||sdr,
You may have to stop & restart the MSSearch service, for the master merger
change to be effective with changing the registry key value. Could you post
the actual FTS query (containstable or freetexttable) and the rank value
along with the full output of -- SELECT @.@.version -- as this will provide
both SQL Server and OS platform version info.
Regards,
John
"sdr" <viiksi_reima@.hotmail.com> wrote in message
news:3652826d.0405180353.6d3c5ef0@.posting.google.c om...
> John,
> I tried changing the suggested registry value, but no change in
> ranking values took place - actually I didn't notice any merge taking
> place either.
> Change Tracking is not enabled in my database, but I'm running a full
> population to both catalogs every night. I suppose the master merge is
> done right after the full population, am I correct?
> Both tables have their own, dedicated catalogs. There are approx.
> 15000-20000 rows in each table.
> Any more ideas?
> sdr
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#IhuyMWOEHA.3312@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
your[vbcol=seagreen]
and[vbcol=seagreen]
for[vbcol=seagreen]
this[vbcol=seagreen]
server[vbcol=seagreen]
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Search\1.0Ca talogNames\SQLServer\<phys[vbcol=seagreen]
the[vbcol=seagreen]
Showing posts with label full-text. Show all posts
Showing posts with label full-text. Show all posts
Saturday, February 25, 2012
CONTAINS with substring
I have a table field that is full-text indexed. I am trying to locate
records where a substring of data is present in a string without spaces. The
format is something like this:
"AAAAAABBAAAA"
If I am looking for the existence of "AABBA" in the string, CONTAINS
apparently will not work, because this command works only with complete words
with spaces between. So this type of query fails:
CONTAINS(fieldname, "AABBA")
or
CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
Is there a way to use CONTAINS with substrings like this?
seeker
No, the only thing you can do is store the string in reverse in your table
you are FTI'ing, and then reserve the search string and do wildcarding. This
only works if you are searching for suffixes, not letter patterns in the
middle of a word/token.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:3F5BA0DE-AC1B-44A0-886E-8D54E2AFC3A0@.microsoft.com...
> I have a table field that is full-text indexed. I am trying to locate
> records where a substring of data is present in a string without spaces.
The
> format is something like this:
> "AAAAAABBAAAA"
> If I am looking for the existence of "AABBA" in the string, CONTAINS
> apparently will not work, because this command works only with complete
words
> with spaces between. So this type of query fails:
> CONTAINS(fieldname, "AABBA")
> or
> CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
> Is there a way to use CONTAINS with substrings like this?
> --
> seeker
records where a substring of data is present in a string without spaces. The
format is something like this:
"AAAAAABBAAAA"
If I am looking for the existence of "AABBA" in the string, CONTAINS
apparently will not work, because this command works only with complete words
with spaces between. So this type of query fails:
CONTAINS(fieldname, "AABBA")
or
CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
Is there a way to use CONTAINS with substrings like this?
seeker
No, the only thing you can do is store the string in reverse in your table
you are FTI'ing, and then reserve the search string and do wildcarding. This
only works if you are searching for suffixes, not letter patterns in the
middle of a word/token.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"seeker" <seeker@.discussions.microsoft.com> wrote in message
news:3F5BA0DE-AC1B-44A0-886E-8D54E2AFC3A0@.microsoft.com...
> I have a table field that is full-text indexed. I am trying to locate
> records where a substring of data is present in a string without spaces.
The
> format is something like this:
> "AAAAAABBAAAA"
> If I am looking for the existence of "AABBA" in the string, CONTAINS
> apparently will not work, because this command works only with complete
words
> with spaces between. So this type of query fails:
> CONTAINS(fieldname, "AABBA")
> or
> CONTAINS(SUBSTRING(fieldname, 5,5), "AABBA")
> Is there a way to use CONTAINS with substrings like this?
> --
> seeker
CONTAINS with *
Hi,
I read in MSDN that
If the text and asterisk are not delimited by double quotation marks, as in
CONTAINS (column, 'text*'), full-text search considers the asterisk as a
character and will search for exact matches to text*.
However... I have this code:
If chkOptions.Items(1).Selected = True Then 'Starts With...
strText = "" & strText & "*" & ""
End If
If chkOptions.Items(0).Selected = True Then 'Allow Flexionary Forms...
strText = "FormsOf(INFLECTIONAL," & strText & ")"
End If
strSearch = "SELECT " & _
"ST.[Rank], Titles.title as Title, Titles.notes as Notes, Authors.au_fname
as [F.Name], Authors.au_lname as [L.Name] " & _
"FROM " & _
"ContainsTable (Titles, *, '" & strText & "') as ST " & _
"INNER JOIN Titles ON ST.[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 ST.[Rank] DESC"
I enter the string cook as strText. Then I build strText as "cook*"
(containing the double quotes).
For some reason, which I have yet to understand, VB takes away my double
quotes, so in the end strSearch comes out as:
SELECT ST.[Rank], Titles.title as Title, Titles.notes as Notes,
Authors.au_fname as [F.Name], Authors.au_lname as [L.Name] FROM
ContainsTable(Titles, *, 'cook*') as ST INNER JOIN Titles ON ST.[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 ST.[Rank] DESC
Anyway, my question is this - since as per MSDN the engine searches for the
string 'cook*' (containing the asterisc), it should NOT find any (I have no
string 'cook*' in my table). However, it returns the exact same result as if
I only searched for 'cook', without any asterisc - I get back two records.
The "INFLECTIONAL" part, though,works just fine.
I think I should go to bed now - apparently I can't see something obvious
;-)))
Thank you.
Alex.
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:%234z9qZEBFHA.1084@.tk2msftngp13.phx.gbl...
> strText = "" & strText & "*" & ""
> I enter the string cook as strText. Then I build strText as "cook*"
> (containing the double quotes).
> For some reason, which I have yet to understand, VB takes away my double
> quotes, so in the end strSearch comes out as:
Change the above line to
strText = """" & strText & "*" & """"
A "" by itself is an empty string (you have the double quotes to enclose a
string, and there's nothing between them). A """" tells VB you want a
literal " in a string (you need 2 of them as VB would treat a single one as
the end of a string enclosure and then error because it thinks you have
another opening string quote after it).
> Anyway, my question is this - since as per MSDN the engine searches for
> the string 'cook*' (containing the asterisc), it should NOT find any (I
> have no string 'cook*' in my table). However, it returns the exact same
> result as if I only searched for 'cook', without any asterisc - I get back
> two records.
Because you have not enclosed the cook* in double quotes (see above for how
to fix this) the * is being treated as a wildcard, hence cook is a match.
With the change to the one line above, it should work as you expect.
Dan
I read in MSDN that
If the text and asterisk are not delimited by double quotation marks, as in
CONTAINS (column, 'text*'), full-text search considers the asterisk as a
character and will search for exact matches to text*.
However... I have this code:
If chkOptions.Items(1).Selected = True Then 'Starts With...
strText = "" & strText & "*" & ""
End If
If chkOptions.Items(0).Selected = True Then 'Allow Flexionary Forms...
strText = "FormsOf(INFLECTIONAL," & strText & ")"
End If
strSearch = "SELECT " & _
"ST.[Rank], Titles.title as Title, Titles.notes as Notes, Authors.au_fname
as [F.Name], Authors.au_lname as [L.Name] " & _
"FROM " & _
"ContainsTable (Titles, *, '" & strText & "') as ST " & _
"INNER JOIN Titles ON ST.[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 ST.[Rank] DESC"
I enter the string cook as strText. Then I build strText as "cook*"
(containing the double quotes).
For some reason, which I have yet to understand, VB takes away my double
quotes, so in the end strSearch comes out as:
SELECT ST.[Rank], Titles.title as Title, Titles.notes as Notes,
Authors.au_fname as [F.Name], Authors.au_lname as [L.Name] FROM
ContainsTable(Titles, *, 'cook*') as ST INNER JOIN Titles ON ST.[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 ST.[Rank] DESC
Anyway, my question is this - since as per MSDN the engine searches for the
string 'cook*' (containing the asterisc), it should NOT find any (I have no
string 'cook*' in my table). However, it returns the exact same result as if
I only searched for 'cook', without any asterisc - I get back two records.
The "INFLECTIONAL" part, though,works just fine.
I think I should go to bed now - apparently I can't see something obvious
;-)))
Thank you.
Alex.
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:%234z9qZEBFHA.1084@.tk2msftngp13.phx.gbl...
> strText = "" & strText & "*" & ""
> I enter the string cook as strText. Then I build strText as "cook*"
> (containing the double quotes).
> For some reason, which I have yet to understand, VB takes away my double
> quotes, so in the end strSearch comes out as:
Change the above line to
strText = """" & strText & "*" & """"
A "" by itself is an empty string (you have the double quotes to enclose a
string, and there's nothing between them). A """" tells VB you want a
literal " in a string (you need 2 of them as VB would treat a single one as
the end of a string enclosure and then error because it thinks you have
another opening string quote after it).
> Anyway, my question is this - since as per MSDN the engine searches for
> the string 'cook*' (containing the asterisc), it should NOT find any (I
> have no string 'cook*' in my table). However, it returns the exact same
> result as if I only searched for 'cook', without any asterisc - I get back
> two records.
Because you have not enclosed the cook* in double quotes (see above for how
to fix this) the * is being treated as a wildcard, hence cook is a match.
With the change to the one line above, it should work as you expect.
Dan
Friday, February 24, 2012
CONTAINS in SQL Server
Hi,
I have a question about MS SQL Server 2005:
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
Or there is a more compact way to write this query.
Thank you very much for your answer,
Regard,
Djamila.that's one way. If you don't care which column the hit is found in you could
do this.
Select f1, ..., fm From table
Where CONTAINS(*, expr)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<djamilabouzid@.gmail.com> wrote in message
news:1159835993.016620.306290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a question about MS SQL Server 2005:
> I have a table in the database that contains 70 fields. I must perform
> full-text search in about 60 fields. I use for that the full-text
> searching "CONTAINS" like that:
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
> My question is: for the 60 fields in the table that I want to do
> full-text search for the same expression, may I write a query like
> that:
> Select f1, ..., fm
> From table
> Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
> CONTAINS(f60,expr)
> Or there is a more compact way to write this query.
> Thank you very much for your answer,
> Regard,
> Djamila.
>
I have a question about MS SQL Server 2005:
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
Or there is a more compact way to write this query.
Thank you very much for your answer,
Regard,
Djamila.that's one way. If you don't care which column the hit is found in you could
do this.
Select f1, ..., fm From table
Where CONTAINS(*, expr)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<djamilabouzid@.gmail.com> wrote in message
news:1159835993.016620.306290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a question about MS SQL Server 2005:
> I have a table in the database that contains 70 fields. I must perform
> full-text search in about 60 fields. I use for that the full-text
> searching "CONTAINS" like that:
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
> My question is: for the 60 fields in the table that I want to do
> full-text search for the same expression, may I write a query like
> that:
> Select f1, ..., fm
> From table
> Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
> CONTAINS(f60,expr)
> Or there is a more compact way to write this query.
> Thank you very much for your answer,
> Regard,
> Djamila.
>
CONTAINS full-text XML problem
I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:
> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:
> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:
> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:
> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:
> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:
> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>
Contains
I have created a full-text catalog in pubs and also include the lname of
employee table in the index.
However, I got empty result set with the SQL when execute in the Query
Analyser :
SELECT *
FROM employee
WHERE contains(lname, 'Perente')I think the pubs database has 'Parente' not 'Perente' :-)
If lname was well written check that the service account used by Full-Text
Search has permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> I have created a full-text catalog in pubs and also include the lname of
> employee table in the index.
> However, I got empty result set with the SQL when execute in the Query
> Analyser :
> SELECT *
> FROM employee
> WHERE contains(lname, 'Perente')
>
>
employee table in the index.
However, I got empty result set with the SQL when execute in the Query
Analyser :
SELECT *
FROM employee
WHERE contains(lname, 'Perente')I think the pubs database has 'Parente' not 'Perente' :-)
If lname was well written check that the service account used by Full-Text
Search has permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> I have created a full-text catalog in pubs and also include the lname of
> employee table in the index.
> However, I got empty result set with the SQL when execute in the Query
> Analyser :
> SELECT *
> FROM employee
> WHERE contains(lname, 'Perente')
>
>
Subscribe to:
Posts (Atom)