File properties for files stored in IMAGE field

Is there a way (in SQL Server 2005 full-text search) to search the
properties (DocType, Keyword, etc) of files stored and indexed in image
fields? I'm searching the indexed contents with no problem but I also
want to search the properties. I know that this isn't possible in
versions prior to 2005 but I've seen referances that 2005 enables this,
I just can't find the details on how to do it. Was it maybe a feature
that got pulled before the finial release?
Hello,
Its not available directly. You would have to use index server and then your
performance will plummit
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Is there a way (in SQL Server 2005 full-text search) to search the
> properties (DocType, Keyword, etc) of files stored and indexed in
> image fields? I'm searching the indexed contents with no problem but
> I also want to search the properties. I know that this isn't possible
> in versions prior to 2005 but I've seen referances that 2005 enables
> this, I just can't find the details on how to do it. Was it maybe a
> feature that got pulled before the finial release?
>
|||Its possible, I'll post a repro later.
Hilary Cotter
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
<ddaiker@.gmail.com> wrote in message
news:1166469151.256705.174690@.48g2000cwx.googlegro ups.com...
> Is there a way (in SQL Server 2005 full-text search) to search the
> properties (DocType, Keyword, etc) of files stored and indexed in image
> fields? I'm searching the indexed contents with no problem but I also
> want to search the properties. I know that this isn't possible in
> versions prior to 2005 but I've seen referances that 2005 enables this,
> I just can't find the details on how to do it. Was it maybe a feature
> that got pulled before the finial release?
>
|||try this - save this as createdocument.vbs, make sure you have a c:\temp
directory.
set wordobj=createobject("Word.application")
set activedoc=wordObj.documents.Add
activeDoc.BuiltInDocumentProperties.item(2)="summa ry info is written here"
set docProp=ActiveDoc.CustomDocumentProperties
docProp.add "Property1", 0,4,"Property1Value"
activedoc.saveAs "C:\temp\Document1.doc"
activedoc.close
wordobj.quit
set docprop=nothing
set activedoc=nothing
set wordobj=nothing
After this has run this script in your SQL 2005 database.
create database test
use test
sp_fulltext_database 'enable'
GO
Create table DocumentPropertyTest(pk int not null identity constraint
DocumentPropertyTestPK primary key, imagecol image, documenttype char(4))
GO
create fulltext catalog doc as default
GO
create fulltext index on DocumentPropertyTest(imagecol type column
documenttype) key index DocumentPropertyTestPK
GO
then run this, save it as loadme.vbs
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.RecordSet")
Set objStream=CreateObject("ADODB.Stream")
objConn.Open
"Provider=SQLNCLI;Server=dev-hcotter;Database=fulltext;UID=sa;PWD=se1cure#;"
Set objFileSystem=createobject("Scripting.FileSystemOb ject")
Set objDir=objFileSystem.GetFolder("c:\temp")
for each objFile in objDir.Files
count=count+1
wscript.echo objFile.name
objConn.Execute "insert into DocumentPropertyTest (ImageCol,documenttype)
values ('Jibberish','doc')"
objRs.Open "select imagecol from DocumentPropertyTest where pk=" & count,
objConn, 1, 3
objStream.Type = 1
objStream.Open
objStream.LoadFromFile objFile.Path
objRs.Fields("ImageCol").Value=objStream.Read
objRs.Update
objRs.Close
objStream.Close
next
objConn.Close
Set objStream=nothing
Set objShell=nothing
Set objConn=nothing
Set objFileSystem=nothing
Set objDir=nothing
then try this
select * from DocumentPropertyTest where contains(*,'Property1Value') -- no
hit
select * from DocumentPropertyTest where contains(*,'summary') --hit
Hilary Cotter
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OgxTfxwIHHA.320@.TK2MSFTNGP06.phx.gbl...
> Its possible, I'll post a repro later.
> --
> Hilary Cotter
> 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
> <ddaiker@.gmail.com> wrote in message
> news:1166469151.256705.174690@.48g2000cwx.googlegro ups.com...
>
|||Wow, thank you for taking the time to write and post all that.
Unfortunatly I'm getting the same results with your sample as I got
with my own testing. The query with "summary" doesn't return a hit
either. I checked the poperties of the doc file from explorer and from
Word and the "Subject" and "Property1" properties are set correctly.
To make sure the indexing was working I put some content in the .doc
and added it agian. If I search for data in the file I get a hit, but
not for anything in the "Subject" property. I tried "summary" and
"written" with no luck.
Any idea what might be wrong?
Here is my setup of everything that I think could be relavant.
Windows XP SP2
IE7
SQL Server 2005 Developer Editition installed as second instance beside
SQL Server 2000 Developer Edition
Office 2003 SP2
Visual Studio 2003 and Visual Studio 2005
My Offfilt.dll version is 2003.5.28.0
|||So my sample does not work on your machine? It works on mine with the same
setup. It is the SQL 2005 instance you can't get it to work on right?
Hilary Cotter
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
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166535897.899606.47840@.73g2000cwn.googlegrou ps.com...
> Wow, thank you for taking the time to write and post all that.
> Unfortunatly I'm getting the same results with your sample as I got
> with my own testing. The query with "summary" doesn't return a hit
> either. I checked the poperties of the doc file from explorer and from
> Word and the "Subject" and "Property1" properties are set correctly.
> To make sure the indexing was working I put some content in the .doc
> and added it agian. If I search for data in the file I get a hit, but
> not for anything in the "Subject" property. I tried "summary" and
> "written" with no luck.
> Any idea what might be wrong?
> Here is my setup of everything that I think could be relavant.
> Windows XP SP2
> IE7
> SQL Server 2005 Developer Editition installed as second instance beside
> SQL Server 2000 Developer Edition
> Office 2003 SP2
> Visual Studio 2003 and Visual Studio 2005
> My Offfilt.dll version is 2003.5.28.0
>
|||Ok, it seems I'm having bigger problems. filtdump only brings back the
content of 2 Word and 1 Excel document that has properties on it. I
ran one of my files through an Index Server catalog and queried it on a
word in it's subject and it didn't get a hit either. I'm having issues
with the iFilter but I need to look into more. Do our versions of
offfilt.dll match?
|||Can you send me some of your problem docs?
My version has a date stamp of 8/18/2006 at 8:34, and has a version of
2006.0.5486.108.
Hilary Cotter
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
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
> Ok, it seems I'm having bigger problems. filtdump only brings back the
> content of 2 Word and 1 Excel document that has properties on it. I
> ran one of my files through an Index Server catalog and queried it on a
> word in it's subject and it didn't get a hit either. I'm having issues
> with the iFilter but I need to look into more. Do our versions of
> offfilt.dll match?
>
|||There was an error in my repro. There were some pre-existing word docs in
the doc directory and one of them had the word summary in it.
I can't get it to work now either, except with html. Once upon a time it did
work on RTM.
Hilary Cotter
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
"ddaiker" <ddaiker@.gmail.com> wrote in message
news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
> Ok, it seems I'm having bigger problems. filtdump only brings back the
> content of 2 Word and 1 Excel document that has properties on it. I
> ran one of my files through an Index Server catalog and queried it on a
> word in it's subject and it didn't get a hit either. I'm having issues
> with the iFilter but I need to look into more. Do our versions of
> offfilt.dll match?
>
|||I received word from Microsoft that SQL FTS 2005 does index and allow
querying of document properties should they be emitted by the iFilters as
strings.
The problem David and myself were having was that for the Word and Excel
documents the properties were not part of the Office documents themselves
but were stored in the file system.
Hilary Cotter
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23FOeh9KJHHA.3936@.TK2MSFTNGP02.phx.gbl...
> There was an error in my repro. There were some pre-existing word docs in
> the doc directory and one of them had the word summary in it.
> I can't get it to work now either, except with html. Once upon a time it
> did work on RTM.
> --
> Hilary Cotter
> 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
>
> "ddaiker" <ddaiker@.gmail.com> wrote in message
> news:1166556861.260065.50920@.t46g2000cwa.googlegro ups.com...
>