Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')Varbinary is supported in FT see these links:
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features,-part-2/ and http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx|||Hi Satya,
I know varbinary is supported by FT, but if you run my example code you'll see it has problems when an nvarchar is being converted into varbinary. Those rows of data don't show us when you do a CONTAINS or FREETEXT search.
This is my problem as I need to take nvarchars in order allow full multi-lingual functionality support.
Regards,
Gavin.|||I've also tried adding the following extra meta tag - <META http-equiv="Content-Type" content="text/html; charset=utf-16">
But this seemed to break the Full Text Index even further. If applied to the example T-SQL I give above then even the non-nvarchar input is no longer returned:
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type" content="text/html; charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
2012年3月11日星期日
Full Text Index not working when populated from nvarchar. Bug?
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table I'm trying to apply a full text catalog to, however no
results are ever returned due to the text column being cataloged being of
varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating
full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to
'.txt' in case it was a fault with the html ifilter but the problem persists
so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table
holds multi-lingual text and I'm adding a html meta tag <META
NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
indexing word breaker to select the correct language to catalog the text
with. The aim being to provide more relevant searches in users native
languages from a single table (I've read a few articles that describe this
technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm
experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered
directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
'.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken
from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running
the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
CONTAINS(varbinarycol, 'test')
I don't believe you need to use nvarchar(max) here, varchar(max) will work.
HTML will have a code page or utf metatag in it, which the html ifilter will
understand.
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
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:3CDA0C57-863D-43CE-898C-B0F18DEA7A4E@.microsoft.com...
> Hi, I was wondering if any SQL Server gurus out there could help me...
> I have a table I'm trying to apply a full text catalog to, however no
> results are ever returned due to the text column being cataloged being of
> varbinary(max) that's being populated from a converted nvarchar(max)
> value.
> To re-create the problem quickly...
> If I populate the column via
> CONVERT(varbinary(max), 'test text')
> then there is no problem, I get results as expected.
> However if I populate the column via
> CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
> no results are ever returned.
> Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily
> creating
> full text catalogs when an nvarchar is not getting converted into a
> varbinary.
> I'm setting the Document Type column to '.html' (I've tried changing this
> to
> '.txt' in case it was a fault with the html ifilter but the problem
> persists
> so I believe I can rule this out).
> The reason I need to convert an nvarchar to varbinary is that the table
> holds multi-lingual text and I'm adding a html meta tag <META
> NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
> indexing word breaker to select the correct language to catalog the text
> with. The aim being to provide more relevant searches in users native
> languages from a single table (I've read a few articles that describe this
> technique, but it's the first time I've tried to apply it).
> Any pointers / suggestions would be greatly appreciated. Cheers,
> Gavin.
> Below is a T-SQL script you can run to demonstrate the effect I'm
> experiencing...
> -- Create test database
> CREATE DATABASE FullTextTest
> GO
> USE FullTextTest
> GO
> -- Create test data table
> CREATE TABLE TestTable
> (
> pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
> varbinarycol VARBINARY(MAX),
> documentExtension VARCHAR(5),
> )
> GO
> -- The below single entry WILL BE FOUND (the text source is being entered
> directly)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry
> 1'),
> '.html')
> -- The bellow two entries below WILL NOT BE FOUND (the text source is
> taken
> from an NVARCHAR(MAX) value)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 2' AS NVARCHAR(MAX))), '.html')
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 3' AS NVARCHAR(MAX))), '.html')
> GO
> -- Create the full text catalog
> sp_fulltext_database 'enable'
> GO
> CREATE FULLTEXT CATALOG TEST AS DEFAULT
> GO
> CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
> documentExtension LANGUAGE 1033)
> KEY INDEX tablePK
> GO
> -- NOTE: You might need to give the catalog a chance to build before
> running
> the script below.
> -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
> RETURNED
> SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
> CONTAINS(varbinarycol, 'test')
|||Hi Hilary,
Thanks for the input, but I was under the impression from what I've read so
far that I would need to use nvarchar to future proof the input for the table
for when I start translating my application into Japanese, Chinese, and other
non-latin based scripts? Can store all these inputs safely in a varchar
variable? I was under the impression nvarchar had been introduced to get
around the language support limitation of varchars? I'm still on a learning
curve here so please tell me if I'm wrong?
I'm actually new to multi-lingual applications, but perhaps I should provide
more detail of what I'm trying to achieve...
I want to store multiple languages in the same table. In order to get the
full text index to use the correct word breakers on a row by row basis the
extra html meta tag is being added to the text to describe the language the
ifilter should go and fetch the relevant word breaker for. From what I
understand, this is only possible with varbinary data so I'm trying to
populate it with a passed in nvarchar parameter to my stored procedure. By
default sql server expects a single language on a table and this technique
should allow me to circumnavigate this limitation. The technique is described
in this article here -
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
(look for the heading "Via language tags in the content being indexed" about
a quarter of the way down the article).
So at the end of the day: I'm storing plain text (in any language), adding
an html meta tag to describe the language and pretending it's html to get SQL
Server to index based on language on a row by row basis.
Regards,
Gavin.
|||Hi Hilary, just woke up a bit more and made the connection - you wrote the
article I was using as my inspiration! Nice article by the way ;)
|||I've also tried adding the following extra meta tag - <META
http-equiv="Content-Type" content="text/html; charset=utf-16">
But this seemed to break the Full Text Index even further. If applied to the
example T-SQL I give above then even the non-nvarchar input is no longer
returned:
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type" content="text/html;
charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
|||You need to complete html document, not just the metatags. here is an
example which works for me.
CREATE DATABASE CODE_SAMPLE5
GO
USE CODE_SAMPLE5
GO
CREATE TABLE CODE_SAMPLE5
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
VARBINARYCOL VARBINARY(MAX),
DOCUMENTTYPECOLUMN CHAR(4))
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE5 (VARBINARYCOL TYPE COLUMN
DOCUMENTTYPECOLUMN) KEY INDEX PRIMARYKEY
GO
--The first document is an html file. We are building this on the fly. Note
the title metatag, and the
--keywords metatag.
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),' <HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'),'.htm')
SELECT* from code_sample5 where contains(*,'test')
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
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:026EDE33-B2EC-4761-AADE-A79EA13AA4F4@.microsoft.com...
> I've also tried adding the following extra meta tag - <META
> http-equiv="Content-Type" content="text/html; charset=utf-16">
> But this seemed to break the Full Text Index even further. If applied to
> the
> example T-SQL I give above then even the non-nvarchar input is no longer
> returned:
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type"
> content="text/html;
> charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
> '.html')
|||Hi Hilary,
I've also tried playing around with a full set of html wrapping tags as you
give in your example. I still get the same problem. If you tweak you're code
a little I believe you will experience the same problem. Convert your text
into an nvarchar before converting to varbinary and you'll see.
-- Ensure input is NVARCHAR
DECLARE @.Text nvarchar(max)
SET @.Text = '<HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'
-- Insert
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),@. Text),'.htm')
Regards,
Gavin.
|||Have you checked the FTS crawl log for possible errors?
ML
http://milambda.blogspot.com/
I have a table I'm trying to apply a full text catalog to, however no
results are ever returned due to the text column being cataloged being of
varbinary(max) that's being populated from a converted nvarchar(max) value.
To re-create the problem quickly...
If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.
However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.
Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating
full text catalogs when an nvarchar is not getting converted into a varbinary.
I'm setting the Document Type column to '.html' (I've tried changing this to
'.txt' in case it was a fault with the html ifilter but the problem persists
so I believe I can rule this out).
The reason I need to convert an nvarchar to varbinary is that the table
holds multi-lingual text and I'm adding a html meta tag <META
NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
indexing word breaker to select the correct language to catalog the text
with. The aim being to provide more relevant searches in users native
languages from a single table (I've read a few articles that describe this
technique, but it's the first time I've tried to apply it).
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
Below is a T-SQL script you can run to demonstrate the effect I'm
experiencing...
-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO
-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO
-- The below single entry WILL BE FOUND (the text source is being entered
directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
'.html')
-- The bellow two entries below WILL NOT BE FOUND (the text source is taken
from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry
3' AS NVARCHAR(MAX))), '.html')
GO
-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO
-- NOTE: You might need to give the catalog a chance to build before running
the script below.
-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
CONTAINS(varbinarycol, 'test')
I don't believe you need to use nvarchar(max) here, varchar(max) will work.
HTML will have a code page or utf metatag in it, which the html ifilter will
understand.
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
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:3CDA0C57-863D-43CE-898C-B0F18DEA7A4E@.microsoft.com...
> Hi, I was wondering if any SQL Server gurus out there could help me...
> I have a table I'm trying to apply a full text catalog to, however no
> results are ever returned due to the text column being cataloged being of
> varbinary(max) that's being populated from a converted nvarchar(max)
> value.
> To re-create the problem quickly...
> If I populate the column via
> CONVERT(varbinary(max), 'test text')
> then there is no problem, I get results as expected.
> However if I populate the column via
> CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
> no results are ever returned.
> Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily
> creating
> full text catalogs when an nvarchar is not getting converted into a
> varbinary.
> I'm setting the Document Type column to '.html' (I've tried changing this
> to
> '.txt' in case it was a fault with the html ifilter but the problem
> persists
> so I believe I can rule this out).
> The reason I need to convert an nvarchar to varbinary is that the table
> holds multi-lingual text and I'm adding a html meta tag <META
> NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text
> indexing word breaker to select the correct language to catalog the text
> with. The aim being to provide more relevant searches in users native
> languages from a single table (I've read a few articles that describe this
> technique, but it's the first time I've tried to apply it).
> Any pointers / suggestions would be greatly appreciated. Cheers,
> Gavin.
> Below is a T-SQL script you can run to demonstrate the effect I'm
> experiencing...
> -- Create test database
> CREATE DATABASE FullTextTest
> GO
> USE FullTextTest
> GO
> -- Create test data table
> CREATE TABLE TestTable
> (
> pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
> varbinarycol VARBINARY(MAX),
> documentExtension VARCHAR(5),
> )
> GO
> -- The below single entry WILL BE FOUND (the text source is being entered
> directly)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry
> 1'),
> '.html')
> -- The bellow two entries below WILL NOT BE FOUND (the text source is
> taken
> from an NVARCHAR(MAX) value)
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 2' AS NVARCHAR(MAX))), '.html')
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test
> entry
> 3' AS NVARCHAR(MAX))), '.html')
> GO
> -- Create the full text catalog
> sp_fulltext_database 'enable'
> GO
> CREATE FULLTEXT CATALOG TEST AS DEFAULT
> GO
> CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN
> documentExtension LANGUAGE 1033)
> KEY INDEX tablePK
> GO
> -- NOTE: You might need to give the catalog a chance to build before
> running
> the script below.
> -- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS
> RETURNED
> SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE
> CONTAINS(varbinarycol, 'test')
|||Hi Hilary,
Thanks for the input, but I was under the impression from what I've read so
far that I would need to use nvarchar to future proof the input for the table
for when I start translating my application into Japanese, Chinese, and other
non-latin based scripts? Can store all these inputs safely in a varchar
variable? I was under the impression nvarchar had been introduced to get
around the language support limitation of varchars? I'm still on a learning
curve here so please tell me if I'm wrong?
I'm actually new to multi-lingual applications, but perhaps I should provide
more detail of what I'm trying to achieve...
I want to store multiple languages in the same table. In order to get the
full text index to use the correct word breakers on a row by row basis the
extra html meta tag is being added to the text to describe the language the
ifilter should go and fetch the relevant word breaker for. From what I
understand, this is only possible with varbinary data so I'm trying to
populate it with a passed in nvarchar parameter to my stored procedure. By
default sql server expects a single language on a table and this technique
should allow me to circumnavigate this limitation. The technique is described
in this article here -
http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/
(look for the heading "Via language tags in the content being indexed" about
a quarter of the way down the article).
So at the end of the day: I'm storing plain text (in any language), adding
an html meta tag to describe the language and pretending it's html to get SQL
Server to index based on language on a row by row basis.
Regards,
Gavin.
|||Hi Hilary, just woke up a bit more and made the connection - you wrote the
article I was using as my inspiration! Nice article by the way ;)
|||I've also tried adding the following extra meta tag - <META
http-equiv="Content-Type" content="text/html; charset=utf-16">
But this seemed to break the Full Text Index even further. If applied to the
example T-SQL I give above then even the non-nvarchar input is no longer
returned:
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(),
CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type" content="text/html;
charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')
|||You need to complete html document, not just the metatags. here is an
example which works for me.
CREATE DATABASE CODE_SAMPLE5
GO
USE CODE_SAMPLE5
GO
CREATE TABLE CODE_SAMPLE5
(PK INT NOT NULL IDENTITY CONSTRAINT PRIMARYKEY PRIMARY KEY,
VARBINARYCOL VARBINARY(MAX),
DOCUMENTTYPECOLUMN CHAR(4))
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON CODE_SAMPLE5 (VARBINARYCOL TYPE COLUMN
DOCUMENTTYPECOLUMN) KEY INDEX PRIMARYKEY
GO
--The first document is an html file. We are building this on the fly. Note
the title metatag, and the
--keywords metatag.
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),' <HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'),'.htm')
SELECT* from code_sample5 where contains(*,'test')
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
"gavin" <gavin@.discussions.microsoft.com> wrote in message
news:026EDE33-B2EC-4761-AADE-A79EA13AA4F4@.microsoft.com...
> I've also tried adding the following extra meta tag - <META
> http-equiv="Content-Type" content="text/html; charset=utf-16">
> But this seemed to break the Full Text Index even further. If applied to
> the
> example T-SQL I give above then even the non-nvarchar input is no longer
> returned:
> INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES
> (NEWID(),
> CONVERT(VARBINARY(MAX),'<META http-equiv="Content-Type"
> content="text/html;
> charset=utf-16"><META NAME="MS.LOCALE" CONTENT="EN">test entry 1'),
> '.html')
|||Hi Hilary,
I've also tried playing around with a full set of html wrapping tags as you
give in your example. I still get the same problem. If you tweak you're code
a little I believe you will experience the same problem. Convert your text
into an nvarchar before converting to varbinary and you'll see.
-- Ensure input is NVARCHAR
DECLARE @.Text nvarchar(max)
SET @.Text = '<HTML><HEAD><TITLE>this is
the title</TITLE><meta name=''keywords''
content=''keyword''></HEAD><BODY>test</BODY></HTML>'
-- Insert
INSERT INTO CODE_SAMPLE5(VARBINARYCOL,
DOCUMENTTYPECOLUMN)VALUES(CONVERT(VARBINARY(500),@. Text),'.htm')
Regards,
Gavin.
|||Have you checked the FTS crawl log for possible errors?
ML
http://milambda.blogspot.com/
2012年3月7日星期三
Full Text "And Not" Fails
I have set up a simple "ProdTest1" table with 3 columns: ID (int) which is an Identity column and indexed, Name (nvarchar 45), and Description (nvarchar 2000). I have then entered a single row with the following data:
ID: 1
Name: Purple Black Blue
Description: Black Blue
I have then set up a Full-Text index based on the Name and Description columns.
When I execute the following query that row is returned in the query results:
select * from ProdTest1 where contains (*,'Black and not purple')
I would not expect any returned rows. Am I missing something?
I am using Microsoft SQL Server 2000 - 8.00.2039 (Enterprise Edition)
Thanks
Adatacorp,Yes... You need to use double quotes for your words &/or phrases, for example:
"black" and NOT "purple" within the single quotes of the CONTAINS clause.
From BOL title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains search conditions. When <contains_search_condition> contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed.
NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.
More specificly, try the following examples:
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
select pub_id, pr_info from pub_info
where contains(*,'"moon"') and NOT contains(*,'"Binnet"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||John,
Including double-quotes in the SQL does not solve the problem. Both of the following two SQL statements incorrectly return my example row:
select * from ProdTest1 where contains (*,'black and not purple')
select * from ProdTest1 where contains (*,'"black" and not "purple"')|||
How about this version? Does it return the results you are expecting?
select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')
Additional, do you have more than one column FT-enabled? There are well known issues with searching "accross" columns when used with CONTAINS or CONTAINSTABLE, see KB article 294809 "FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns" at http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 for more details.
Finally, the OS platform that you have SQL Server installed and depending upon the text in your FT-enabled column, can be an issue as well. Does your FT-enabled column contain any punctuation characters in contact with your search words? Could you post the full output of SELECT @.@.version ?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John,
You have answered the question.
I was expecting the full-text query to be applied across the concatenation of all the columns. But instead (per the KB article you reference), the query
select * from ProdTest1 where contains (*,'black and not purple')
is actually executed as...
select * from ProdTest1 where contains (Name,'black and not purple') OR contains (Description,'black and not purple')
Thus the Full-Text query correctly returns my row because the data in the Description column (Black Blue) meets the query requirement.
Indeed your suggested query:
select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')
does return the results I desire.
However for my desired behavior in all searches I think I will need to create yet another column and in a trigger concatenate all the query column data into that added column.
I wish Microsoft had an option to simply do the Full-Text searches on the catenation of all the columns as I desire.
Thanks for your help.
|||You're welcome, Bob,I suspected so, as you were using the "*" (astrick) that indicates to search all FT-enabled column and behavior the you are seeing is the default behavior for SQL Server 2000.
However, for SQL Server 2005 (codename Yukon) supports a new syntax of multiple column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
The above query is functionally equivalent to the following SQL 2000 FTS query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Specifically, an OR between columns is supported in a single statement, but
not an AND between columns and to the best of my knowledge NOT is not supported within this new syntax. I'll confirm this as well.
As for doing FT Searches on concatenated columns, I suspect you'll have to wait for the next version of SQL Server (post-Yukon) as IBM's DB2 Text Information Extender can do this somewhat via a UDF.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
订阅：
博文 (Atom)