Get Next Number: keywords

I would like to be able to search the metadata of files (e.g. Title,
Keywords,
etc) that I have stored in a database. I have included the "metainfo" column
(image) in the full-text catalog. Running a freetexttable on the documents
table returns matching results for web pages (.htm, .aspx), however for
other documents (e.g. word, excel, etc), I am getting no results. Is this
an IFilter issue? Am I missing something here?
Thanks in advance!
This will work in SQL 2005, however you will not be able to search on only
the property. The contains search will be on all properties.
In SQL 2000 you will have to extract the properties and store them in
seperate columns and SQL FTI them.
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
>I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
> column
> (image) in the full-text catalog. Running a freetexttable on the
> documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||Jeremy,
Could you post the full output of -- SELECT @.@.version -- as this is most
helpful in understanding your environment and will help more quickly answer
your questions.
If you're using SQL Server 7.0, you are limited to extracting the text from
file and storing this text in a column defined with TEXT or NText datatype.
I'm assuming from your comments that you're using SQL Server 2000, you can
store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
defined with an IMAGE datatype. Note, you will need to also define a "file
extension" column that identifies the file type (use char(3), varchar(4) or
sysname) and populate this column with "doc", ".doc" respectively.
Can I assume that if you search on keywords that you know to be in the body
of the documents (e.g. word, excel, etc) that you do get the row that
contains that keyword returned in your CONTAINS or FREETEXT query?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> I would like to be able to search the metadata of files (e.g. Title,
> Keywords,
> etc) that I have stored in a database. I have included the "metainfo"
column
> (image) in the full-text catalog. Running a freetexttable on the
documents
> table returns matching results for web pages (.htm, .aspx), however for
> other documents (e.g. word, excel, etc), I am getting no results. Is this
> an IFilter issue? Am I missing something here?
> Thanks in advance!
>
|||btw-you might want to check out
http://www.indexserverfaq.com/blobs.htm for more information on how to index
blobs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OeOh$1%23BFHA.2392@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> This will work in SQL 2005, however you will not be able to search on only
> the property. The contains search will be on all properties.
> In SQL 2000 you will have to extract the properties and store them in
> seperate columns and SQL FTI them.
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:%23K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
this
>
|||John,
I am using SQL Server 2000. Here's my select statement:
SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
I have two columns defined on the document information, MetaInfo and Content
(as well as the extension column that stores the doc extension). When using
terms I know to be in the document, I can successfully return results from
the "Content" column, however, the "MetaInfo" column does not seem to return
any correct results (besides web pages). I have programmatically pulled the
column data and viewed the metainfo column in an application with no
problem. However, the freetexttable statement I'm using does not seem to be
finding the search term.
Ideas?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Could you post the full output of -- SELECT @.@.version -- as this is most
> helpful in understanding your environment and will help more quickly
answer
> your questions.
> If you're using SQL Server 7.0, you are limited to extracting the text
from
> file and storing this text in a column defined with TEXT or NText
datatype.
> I'm assuming from your comments that you're using SQL Server 2000, you can
> store binary files (MS word, MS Excel, Adobe PDF files, etc.) in an column
> defined with an IMAGE datatype. Note, you will need to also define a "file
> extension" column that identifies the file type (use char(3), varchar(4)
or
> sysname) and populate this column with "doc", ".doc" respectively.
> Can I assume that if you search on keywords that you know to be in the
body[vbcol=seagreen]
> of the documents (e.g. word, excel, etc) that you do get the row that
> contains that keyword returned in your CONTAINS or FREETEXT query?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:#K$d4z9BFHA.3940@.TK2MSFTNGP09.phx.gbl...
> column
> documents
this
>
|||Jeremy,
Actually, I have more follow-up questions as I want to be sure that I
understand your environment...
Could you confirm your FT-enabled table (Docs) column definitions? Content
defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
type of datatype? What is the content of the MetaInfo column? Is it document
property type data, such as Author, Company, Keywords, etc.? If so, how did
you import the text into this column?
Thanks!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> John,
> I am using SQL Server 2000. Here's my select statement:
> SELECT * FROM FreeTextTable(Docs, MetaInfo, @.SearchTerm)
> I have two columns defined on the document information, MetaInfo and
Content
> (as well as the extension column that stores the doc extension). When
using
> terms I know to be in the document, I can successfully return results from
> the "Content" column, however, the "MetaInfo" column does not seem to
return
> any correct results (besides web pages). I have programmatically pulled
the
> column data and viewed the metainfo column in an application with no
> problem. However, the freetexttable statement I'm using does not seem to
be[vbcol=seagreen]
> finding the search term.
> Ideas?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uxQNbJACFHA.1452@.TK2MSFTNGP11.phx.gbl...
> answer
> from
> datatype.
can[vbcol=seagreen]
column[vbcol=seagreen]
"file[vbcol=seagreen]
> or
> body
for
> this
>
|||On the Docs table, I have FT-enabled the following:
DocName nvarchar(128)
MetaInfo image(16)
Content image(16)
Here's an example of the MetaInfo that I have pulled out programmatically.
vti_cachedcustomprops:VX|vti_title
vti_modifiedby:SR|lab2003\\jmaddrey
vti_cachedtitle:SR|catsdogs
vti_title:SR|catsdogs
vti_author:SR|lab2003\\jmaddrey
When I perform the search "catsdogs", nothing is found. However, any text I
place within the body of the document is accurately returned.
In regards to importing the data into the metainfo column, I am working with
a Windows Sharepoint Services site, so the database was generated by
Sharepoint. I am not exactly sure how that data is extracted by the
Sharepoint framework.
Thanks!
Jeremy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Jeremy,
> Actually, I have more follow-up questions as I want to be sure that I
> understand your environment...
> Could you confirm your FT-enabled table (Docs) column definitions?
Content
> defined with an IMAGE datatype. (Correct?) and MetaInfo defined with what
> type of datatype? What is the content of the MetaInfo column? Is it
document
> property type data, such as Author, Company, Keywords, etc.? If so, how
did[vbcol=seagreen]
> you import the text into this column?
> Thanks!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uNVt0tHCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Content
> using
from[vbcol=seagreen]
> return
> the
to[vbcol=seagreen]
> be
most[vbcol=seagreen]
> can
> column
> "file
varchar(4)[vbcol=seagreen]
"metainfo"[vbcol=seagreen]
> for
Is
>
|||Anyone? Anyone?
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>
|||Jeremy,
Sorry, for the late reply! I've had a lot of things going on lately, no
excuse, but just keeping me busy...
The thing about this is I don't have a real answer for you as metadata in
the MetaInfo column was generated by Sharepoint and I too am I am not
exactly sure how that data is extracted by the Sharepoint framework.
Fundamentally, and outside of Sharepoint, SQL FTS must have a valid file
extension, usually .doc for MS Word documents, or .pdf for Adobe PDF file
types as is with your Content column. This file extension is then used by
the MSSearch service and calls a daemon process to FT Index a specific file
type. However, there is no file extension column and file type that is
linked with the MetaInfo column (defined with the image datatype) in order
to successfully FT Index the metadata generated by SharePoint, I'm sad to
say...
At this point, I'd recommend that you open a support case with Microsoft PSS
Sharepoint support first (and if they can't help, then SQL Server support)
and have them help you through this understanding how to accomplish what
you're trying to do. If you don't mind, if they do provide a solution, could
you post it back here in this newsgroup for others to read?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:OhD8seVDFHA.512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Anyone? Anyone?
>
> "Jeremy" <jmaddreysp@.cox.net> wrote in message
> news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
programmatically.[vbcol=seagreen]
text[vbcol=seagreen]
> I
> with
> what
how[vbcol=seagreen]
When[vbcol=seagreen]
results[vbcol=seagreen]
to[vbcol=seagreen]
> pulled
seem[vbcol=seagreen]
is[vbcol=seagreen]
quickly[vbcol=seagreen]
> text
> you
an[vbcol=seagreen]
a[vbcol=seagreen]
> the
> that
> Title,
the[vbcol=seagreen]
> however
results.
>
|||Can you explain what you mean by pulled out programmatically?
I took your content, stored it in and image data type column and using a
value of txt for the document column type I was able to query for catsdogs.
Can you perhaps tell us what the value of your document type column is?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:uX$hrbTCFHA.1408@.TK2MSFTNGP10.phx.gbl...
> On the Docs table, I have FT-enabled the following:
> DocName nvarchar(128)
> MetaInfo image(16)
> Content image(16)
> Here's an example of the MetaInfo that I have pulled out programmatically.
> vti_cachedcustomprops:VX|vti_title
> vti_modifiedby:SR|lab2003\\jmaddrey
> vti_cachedtitle:SR|catsdogs
> vti_title:SR|catsdogs
> vti_author:SR|lab2003\\jmaddrey
> When I perform the search "catsdogs", nothing is found. However, any text
I
> place within the body of the document is accurately returned.
> In regards to importing the data into the metainfo column, I am working
with[vbcol=seagreen]
> a Windows Sharepoint Services site, so the database was generated by
> Sharepoint. I am not exactly sure how that data is extracted by the
> Sharepoint framework.
> Thanks!
> Jeremy
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:enMKQrMCFHA.1404@.TK2MSFTNGP11.phx.gbl...
> Content
what[vbcol=seagreen]
> document
> did
> from
pulled[vbcol=seagreen]
> to
> most
text[vbcol=seagreen]
you[vbcol=seagreen]
> varchar(4)
the[vbcol=seagreen]
that[vbcol=seagreen]
Title,[vbcol=seagreen]
> "metainfo"
however
> Is
>

Hi,
I have a Photo Database, with a Photo table. Each photo has several
Varchar fields for storing caption, description, keywords, etc. I have
a Full-Text index/catalog for these fields.
This is the problem:
I want to find all photos that are related to "Simon" and "Jane", but
no photos taken when they entered or exited the "clinic".
So, my Query looks like:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'ISABOUT("Simon") AND
ISABOUT("Jane") AND NOT ISABOUT("clinic")') AS KEY_TBL ON Fotos.Cod =
KEY_TBL.[KEY]
WHERE KEY_TBL.Rank>0
ORDER BY ISNULL(KEY_TBL.Rank,0) DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
The problem is that it returns photos with descriptions that include
the word "Clinic" (in this particular query, the first row in the
resultset has the word clinic in it!).
I noticed "Simon", "Jane" and "clinic" could reside on different
columns, so I rewrote the query:
SELECT *
FROM Fotos LEFT JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic') AS
KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
LEFT JOIN CONTAINSTABLE(Fotos,*, Jane AND NOT clinic') AS KEY_TBL1 ON
Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank+KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
The same problem occurred.
What Am I doing wrong?
Thanks for your help.
JOEL
Thanks for your answer Dan.
I modified the query as follows:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
I changed it to use Inner Join, but also ensured that any
Containstable returns significant photos, by having separate
Containstabel.Rank>0 in the WHERE clause. But it returns a row where a
field has something like "Simon and Jane entering the magnetic
resonance clinic...". I don't see any error on this new query...
I ended up solving this with and aditional NOT CONTAINS in the WHERE
clause:
SELECT *
FROM Fotos INNER JOIN CONTAINSTABLE(Fotos,*, 'Simon AND NOT clinic')
AS KEY_TBL0 ON Fotos.Cod = KEY_TBL0.[KEY]
INNER JOIN CONTAINSTABLE(Fotos,*, 'Jane AND NOT clinic') AS
KEY_TBL1 ON Fotos.Cod = KEY_TBL1.[KEY]
WHERE KEY_TBL0.Rank>0 AND KEY_TBL1.Rank>0 AND NOT
CONTAINS(Fotos.*,'clinic')
ORDER BY KEY_TBL0.Rank+KEY_TBL1.Rank DESC, ISNULL(Fotos.FotoDate,
Fotos.CreatedDate) DESC
There is no other way to , but I am concerned with performance.
Thanks for your help.
All the best,
JOEL
On Aug 9, 3:36 pm, "Daniel Crichton" <msn...@.worldofspack.com> wrote:
> joel wrote on Thu, 09 Aug 2007 07:13:00 -0700:
>
>
>
> By using a LEFT JOIN you've told the processor to include all the rows where
> 'Simon AND NOT clinic' is true, whether or not there is a match to them in
> 'Jane AND NOT clinic'. Assuming you have only two indexed columns (because
> you're only doing two CONTAINSTABLE clauses), this means that if a row has
> "Simon" in col1, and "Jane clinic" in col2, then you'll still get a result
> because the first clause finds a row and you're not filtering out failures
> in the second clause.
> If you use an INNER JOIN you'll only get the rows where both conditions are
> true. However, if there is a possibility of "clinic" occurring in a 3rd
> column then you'll need to add another CONTAINSTABLE clause to filter those
> out.
> An easier solution might to be create a new column that has the contents of
> all of the indexed varchar columns concatentated together and index that -
> they you need only one clause. I do this myself for my own sites where I
> want to create a simple query over a number of indexed columns, so I have an
> extra column with all the words from those others dumped into it. Makes life
> a bit simpler

> Dan- Hide quoted text -
> - Show quoted text -

Wednesday, March 7, 2012

FreeTextTable on image column of document metadata

Free-Text Search "AND NOT" Wrong results - HELP, please!

FreeText on multiple columns

Get Next Number

Blog Archive

About Me