SQL 2005 Bug? Cannot create Full Text Index for VARBINARY column that's populated with converted NVARCHAR values

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 - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.

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.


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/11/2007 5:49:41 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

12 Replies
719 Views

Similar Articles

[PageSpeed] 1
Get it on Google Play
Get it on Apple App Store

Hi,

check this 

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20

Mehedi Hasan


Mark as answer if the post meets your requirement!
0
M
7/11/2007 6:41:14 AM

Hi Mehedi,

Thanks for the input - unfortunately the article you provided seems to be an overview of creating full text catalogs and not related to my specific problem. I have no problem creating full text catalogs and using them unless the varbinary column source is being populared from a converted (CONVERT) nvarchar input?!? It seems to be a very specific problem.

Regards,

Gavin. 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/11/2007 9:36:33 AM
Below is a script that will quickly re-create my problem for you... 
-- 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')
 

Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/11/2007 9:04:07 PM

Going out on a limb here, but try:

SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, N'test')

or

SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, CONVERT(NVARCHAR(4),'test'))

I could be completely wrong, but I'm confused as to why you've stuffed HTML into a varbinary field.  It seems to me the correct field definition would be nvarchar(max).  It is probably getting confused because the UTF-8/Unicode version is not the same binarily (I made that word up -- I think) as the varchar version of it.

 

0
Motley
7/11/2007 11:01:28 PM

Hi Motley,

Cheers for the input, but it doesn't solve the problem I'm afraid :(

There's actually a good reason for using a varbinary - it's because 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 used 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. By default sql server expects a single language on a table. 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).

Cheers,

Gavin.
 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/12/2007 1:22:12 AM

try removing the period from the document extension?

0
Motley
7/12/2007 8:08:42 PM

Hi Motley,

I've tried that one as well and it doesn't help either :(

If you run my code example above you'll see that the one bit of html being populated without conversion into nvarchar does get found. Just the two records that use nvarchar that don't. All 3 records are using '.html' (but I have tried with '.htm', 'html', 'htm' and still no joy).

Cheers,

Gavin.
 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/12/2007 9:39:36 PM

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')
 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
7/15/2007 7:58:36 PM

Hi all,

I want to extend this topic with my basic full text search question and glad to have a positive feedback. I am working on something similar to Gavin's project and I read lot of articles for this but still confused how to start. So my question is that I have html pages (almost 8000 non english) in which I want to make search and populate their links for view the html file. In my database table I have one column named 'Path' with VARCHAR() datatype which contains path for the html pages i.e. 'A10987.html' which are indexed and stored in folder outside the database and other column 'Extension' with VARCHAR() datatype which contains extension for the html file i.e '.html', the questions making me confused are:

 
1. How file name will be in the column "A10987.html" or "A10987" because the file extension is already saved in "Extension" column i.e. ".html".

2. What data types I have to use for file paths.

3. To search in html files we need to store the file data with all the tags in database table or save the html files in separate folder like I did and use the path to search it.

4. Or any other way to search in html pages.

Thanks for any help.
 

0
Sindbad_nav
4/23/2008 9:45:06 AM

Hi Sindbad,

I don't think SQL Server 2005 will be able to index your external files for it's full text search functionality. I believe you'll find that the information to be indexed must be stored by SQL Server itself.

Gav ;) 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
4/23/2008 10:09:52 AM

Thanks Gav for your quick response. I already just finished experimenting the tip you gave. I upload the html files through my web form and SQL Server indexed the files it self properly and place them in a folder with the same name of the DB table. I just need some more tips to move further. Like I want to know that am I doing fine and what next I have to do. I also want to mention that I am not using VARBINARY() datatype it is just a VARCHAR() datatype. how far I understand that we use VARBINARY() when we need to save the large amount of data in to our table and if we are using file system then you can use VARCHAR(). may be I am wrong.

 Thanks again.
 

0
Sindbad_nav
4/23/2008 11:56:49 AM

Hi Sindbad,

If you're just storing text files in a database column then you should be fine using just VARCHAR(MAX). VARBINARY would only be needed if you were storing things like images and pdfs for instance.

You're probably best starting off a new thread with your problem as it's not really related to the problem discussed here. You might get better feedback from the community then.

Best of luck,

Gavin. 


Gavin Harriss
Portfolio: www.gavinharriss.com
0
gavinharriss
4/24/2008 11:11:49 PM
Reply:

Similar Artilces:

full-text indexing in SQL server 2005
in object explorer ,do right-click on database and is selecting preoperties and is selecting "files" page "use full-text indexing" ckeck box is disable. how can enabled this check box? thanks , mohsen Hi mohsendeveloper, Which version of sql server do you have ? Based on my understanding, only enterprise edition and standard edition (and editions above) have this feature. Express edition and Dev edition do not have full-text index feature. Run   print @@version in your sql server management query window to see which version of sql server you are usin...

sql express 2005 and full text indexing
I am following the how to in the sql studio management express and for text indexing it says   ================================================ To enable a database for full-text indexing In Object Explorer, expand the server group, right-click the database for which you want to enable full-text indexing, and then select Properties. Select the Files page, and then select Use full-text indexing  This worked ok  ==================================================   next it says to   ================================================== To enable a ta...

How to convert MS SQL's 'smalldatetime' to PB 'date' in DW?
Suppose I have a Powerbuilder datawindow with a column of type 'date'. I also have a MS SQL 7 database table with a column of type 'date' where 'date' is a MS SQL's user defined type which maps to MS SQL's 'smalldatetime'. Now I retrieve the datawindow. On Windows XP Professional in the datawindow I have a beautiful date, let's say '1-2-03' but on Windows 95 I have garbage: '??-??-1241' or similar. Here is my question: how to convert the MS SQL's 'smalldatetime' value to the PB 'date' type 'on-the...

boolean: {[If [table with this name] already exists in [this sql database] then [ don't create another one] else [create it and populate it with these values]}
the subject pretty much says it all, I want to be able to do the following in in VB.net code):   {[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}   How would I do this?  run the below query, if it returns 1, table already exists. otherwise not exists. (select count(*) from sysobjects where xtype='u' and name = @name) Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever. Hello Sabrick, Can you try the following script...

convert SQL 2005 express .mdf to SQL 2005
Hi Forum, I need some good info regards remote host requirements. I dev web app using VS 2005 express edition and SQL Server Management Studio express. Everything is ready to transfer to host running SQL 2005. My ? do I have to convert my SQL 2005 express .mdf to a SQL 2005 file type. (hosting company state I do, then save as .bak) much appreciate good data, Paul    A mdb file is Access based, an mdf file is the data file of SQL Server. They are not convertible. You will have to import the data from access or export / upsize from access to make them stored on the server.....

Full Text Index using SQL Server 2005
Hi, In my requirement, i have to work with full text indexing,  i enabled the full text indexing, but i am confused in setting the columns like, i tried to set the column (DocDatafld varbinary(MAX)) for full text indexing and TypeColumn(varchar(50) for Type Column. But i don't understand what is this type column and what is the purpose of this, what should be the data type. After creating the full text index, i am not able to query the database with the following query. select docdatafld from docdatatbl where freetext(DocDatafld,'C#')  Please help me and let me know ...

What's the Access query equivalent of SQL's "CONVERT"?
I'm trying to use the following query in a new Access-based project. It works fine with SQL Server, but Access doesn't seem to like "CONVERT". Dim MyQuery as string = "SELECT * from Deadlines WHERE ([DueDate] > CONVERT(DATETIME, '" & CurrDate & "', 112)) ORDER BY DueDate ASC" Anyone have an equivalent statement for Access? I need to only pull those records where 'DueDate' is today or later. Thanks in advance.Stephen Here's the error I get: Server Error in '/' Application. ---------------------------------------------------...

Help! I cannot access the DataGrid's ItemTemplate's LinkButton's Text Atrribute 's Value!!!!!!!!!!!
I write a datagrid's ItemDataBound method as follows:   private void dgTable_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)  {   if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)   {    if(!isRole("Manager"))    {     LinkButton tmp = (LinkButton)e.Item.FindControl("lbPassed");     if(tmp.Text == "Passed")     {      ((LinkBu...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

SQL Server 05 & .NET .dll's: executing .dll from T-SQL
Can I manually execute a .dll stored in SQL Server 2005?Is it possible for this .dll to do anything: call a Web Service, process XML, etc?Thanks. ...

Running sql query to reset idenity column's value
Hi, I got following information to reset value of identity column But i dont know how to run this query ? DBCC CHECKIDENT (TableName, RESEED, 0) I mean where to go ? can i run from visual studio? which option?   To reset the value of the identity column in a Microsoft SQL table run the below query: DBCC CHECKIDENT (TableName, RESEED, 0) You will need to change TableName to the name of the table you wish to reset. This query will reset the identity column to 0, meaning the next row will have the identity 1. You can change 0 to whatever identity you wish to start fr...

SQL 2005 FULL TEXT (DIFFERENT WEIGHT FACTOR FOR DIFFERENT COLUMNS)
Hi All, Any help on this will be appreciated. I want to implement full text search on SQL 2005. With SQL 2005. However I want to have different weight factor for different columns. For example: If my table has 3 columns, ProductName, ManufacturerName, ProductDescription, and if the user searches for the keyword "simcity", I want to give maximum importance if the keyword is found in the ProductName column, and minimum importance if the keyword is found on the ProductDescription column I know the easiest but the worst way to do this is use 3 different fulltext queri...

Web resources about - SQL 2005 Bug? Cannot create Full Text Index for VARBINARY column that's populated with converted NVARCHAR values - asp.net.sql-datasource

Resources last updated: 11/23/2015 2:39:42 PM