So here's a cheap demo of FTS property search in Denali. For the motivation for this feature, see the previous blog entry. I'm a minimalist (minimalism is a teaching aid), so its just the basics. In some places, I may be doing more than is minimally required for clarity.
First off, I didn't use the AdventureWorks version for Denali plus BOL examples to do this. I tried, though. AdventureWorks Production.Document table only has .doc files; the IFilter for these doesn't support extended property extraction according to BOL. And, just trying to put the pieces in place eventually gives me Msg 30045 "fulltext index error…". Don't know why. Rather than trying to debug this, I'll start from scratch.
First, acquire an IFilter that extracts extended properties. The Office 2010 IFilter Packs do. You must install the FilterPack on the SQL Server machine, naturally. After installation, refresh FTS's IFilter list from the registry.
exec sp_fulltext_service 'load_os_resources', 1
Now, create yourself an Office 2010 document (actually sounds like Office 2007 and above). Type some text. Change the Author and Title properties, check it with Windows Explorer (or you can accomplish this with Windows Explorer).
Create a database. Create a table with three columns. Here's mine:
create table dbo.docs (
id int identity not null,
doctype varchar(20),
doc varbinary(max),
constraint pk_id primary key (id)
)
Add your Office 2010 document from the file system using OPENROWSET BULK and specifying .docx in the doctype column.
Create a search property list. The example from BOL works fine, and they used the correct canonical GUIDs for Title, Author, and Tags.
Create a fulltext catalog and fullindex index. The fulltext index should reference your search property list. Specify change_tracking auto (the default) on the index or cause a full population of the fulltext index.
Now you can use the new systax to search for properties:
SELECT * FROM dbo.docs WHERE CONTAINS(PROPERTY(doc,'Author'), 'Bob');
Here's a script that does this. And even a document too. Enjoy.