Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.
You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.
|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:

> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>

Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to conver
t
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:

> You could create the ifilter and have it emit the text data which is store
d
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in messag
e
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
>
>

Full text search of varbinary column

I'm looking for suggestions on the best approach for creating a full text
search index on a varbinary column. The varbinary data is the binary
serialization of a custom clr datatype (but to be clear -- the column is
typed varbinary, not the custom type).
I thought I might be able to create a computed column that converted the
varbinary to the custom type, then used methods on the custom type to convert
to string. But it seems like I can't do that without persisting the computed
column which I really don't want to do.
Another approach I'm considering is to create an iFilter for the datatype,
then create a computed column that just returns the column type I'd register
for the iFilter. That just seems a little heavy weight to me and has
deployment headaches.
I can't help think I'm missing something obvious. Any ideas?
Thanks.You could create the ifilter and have it emit the text data which is stored
in the varbinary column. I think you will find the overhead of this to be
significant, and would advise you to store the data as text data in a
varchar column and index that column.
--
http://www.zetainteractive.com - Shift Happens!
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
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> I'm looking for suggestions on the best approach for creating a full text
> search index on a varbinary column. The varbinary data is the binary
> serialization of a custom clr datatype (but to be clear -- the column is
> typed varbinary, not the custom type).
> I thought I might be able to create a computed column that converted the
> varbinary to the custom type, then used methods on the custom type to
> convert
> to string. But it seems like I can't do that without persisting the
> computed
> column which I really don't want to do.
> Another approach I'm considering is to create an iFilter for the datatype,
> then create a computed column that just returns the column type I'd
> register
> for the iFilter. That just seems a little heavy weight to me and has
> deployment headaches.
> I can't help think I'm missing something obvious. Any ideas?
> Thanks.|||Thanks. I'm leaning towards the approach you suggest.
It seems like it comes down to a space/time tradeoff to some extent -- I can
either take the hit of the space required to materialize an alternate
representation (e.g. persisted computed column, indexed view, etc.), or pay
the runtime costs of an ifilter (there's also a time cost to any
materialization, and a complexity cost to the ifilter so it's not quite that
simple).
One of the things that bugs me about the ifilter approach is that it seems
like a hack since I have to fake a document extension for my custom datatype
to make it work. Do you know if any other values are accepted for Column
Type? e.g. can I just use the clsid of the filter?
-Geoff
"Hilary Cotter" wrote:
> You could create the ifilter and have it emit the text data which is stored
> in the varbinary column. I think you will find the overhead of this to be
> significant, and would advise you to store the data as text data in a
> varchar column and index that column.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
> news:42CBA330-A7CC-4E7C-BEF9-663E96BA3A69@.microsoft.com...
> > I'm looking for suggestions on the best approach for creating a full text
> > search index on a varbinary column. The varbinary data is the binary
> > serialization of a custom clr datatype (but to be clear -- the column is
> > typed varbinary, not the custom type).
> >
> > I thought I might be able to create a computed column that converted the
> > varbinary to the custom type, then used methods on the custom type to
> > convert
> > to string. But it seems like I can't do that without persisting the
> > computed
> > column which I really don't want to do.
> >
> > Another approach I'm considering is to create an iFilter for the datatype,
> > then create a computed column that just returns the column type I'd
> > register
> > for the iFilter. That just seems a little heavy weight to me and has
> > deployment headaches.
> >
> > I can't help think I'm missing something obvious. Any ideas?
> >
> > Thanks.
>
>sql

full text catalog empty

Hi,

I am trying to setup a full text catalog on one table in my database. Although the catalog is created fine and no erros are reported when creating or populating the catalog. It seems to be empty as its size is only 1Mb and my query:

select top 10* from kmuser.tbl_webpages
where contains (WebPageHTML_FT, '"aruba"'

returns no rows, while:

select * from kmuser.tbl_webpages
where webpagehtml_ft like '%aruba%'

returns 6 rows.

I have read several articles on the subject and have followed them to a T. I've deleted it and recreated so many times now both through Enterprise manager and through query analyser using the stored procedure sp_fulltext_catalog.

I've checked that full text catalog is enabled using:

select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')

I am really stuck. Does anyone have any ideas? Btw I am using SQL Server 2000 still

Many thanks

Is FulltextServices started, what do the logs of FullText Service tell you ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

FTS Catalogs won't populate

Hi

After creating a full text catalog in SQL Server 2000 and performing the initial population, the catalog say has an index count of 1 (should be several thousand) and all "contains" queries return no results. The gather log contains the following error message: Error Fetching URL, (80070002 - The system can not find the file specified) . Any catalog I try to make has the same problem. All catalogs were working fine last week. Any help would be very appreciated.

Thanks!

We are having the exact same problem. Did you find a resolution? Thanks. Also, is your SQL 2000 databases in a SQL cluster? Ours is, but don't know if that's part of the problem.|||Hey, we fixed our problem by re-registering tquery.dll on SQL Server. See http://support.microsoft.com/kb/817301/en-us. Hope this helps.|||Our DB is on a cluster too. I will definitely try your solution tomorrow and see what happens. Thanks!|||Hope it fixes it. Best of luck.

FTS Catalogs won't populate

Hi

After creating a full text catalog in SQL Server 2000 and performing the initial population, the catalog say has an index count of 1 (should be several thousand) and all "contains" queries return no results. The gather log contains the following error message: Error Fetching URL, (80070002 - The system can not find the file specified) . Any catalog I try to make has the same problem. All catalogs were working fine last week. Any help would be very appreciated.

Thanks!

We are having the exact same problem. Did you find a resolution? Thanks. Also, is your SQL 2000 databases in a SQL cluster? Ours is, but don't know if that's part of the problem.|||Hey, we fixed our problem by re-registering tquery.dll on SQL Server. See http://support.microsoft.com/kb/817301/en-us. Hope this helps.|||Our DB is on a cluster too. I will definitely try your solution tomorrow and see what happens. Thanks!|||Hope it fixes it. Best of luck.