Fulltextsearch in varbinary field

Question

Post reply

Fulltextsearch in varbinary field

robert.jammernegg

Valued Member

Points: 72
More actions
August 23, 2013 at 3:35 am

#282394

Hi,
we have a database which should store different files (doc, pdf, XML ,...) with filestream.
We would like to implement fulltext search for the documents stored in varbinary field.
create database dbKnowledgebase
on primary
(
name = 'dbKnowledgebase',
filename = 'D:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\dbKnowledgebase.mdf',
size = 10 MB
),
Filegroup KnowledgeFS contains filestream
(
name = 'KnowledgeFS',
filename = 'D:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\KnowledgeFS'
)
LOG ON
(NAME = dbSoftysoft1_log1,
FILENAME = 'D:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\dbKnowledgebase.ldf',
SIZE = 2MB,
FILEGROWTH = 20%
)
GO
create table tblRessources
(
IDRessource int identity(1,1) primary key,
FKUser int not null references tblUser,
Url varchar(500),
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE default newid(),
Files varbinary(max) Filestream ,
Notice varchar(500)
)
exec sp_fulltext_database 'enable';
select FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')
CREATE FULLTEXT CATALOG [fulltextCatalogue]
CREATE UNIQUE INDEX fullIndex
ON tblRessources (IDRessource);
GO
create fulltext index on tblRessources(Files)
key index fullIndex
on [fulltextCatalogue]
with stoplist = system,
change_tracking = manual
alter fulltext index on tblRessources start update population
If we create the index we receive an error:
Meldung 7655, Ebene 16, Status 1, Zeile 1
The Option TYPE COLUMN has to be specified for a column from type 'image' oder 'varbinary(max)'.
Your help would be appreciated,
thanks a lot.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You need to specify TYPE COLUMN which holds file type extention.

Read BoL: http://technet.microsoft.com/en-us/library/ms187317.aspx

So you need a column to keep extention in.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sue-651097 Say Hey Kid Points: 675 More actions · Answer 2

Hi,

so you need a further column in which the file type is stored:

filetype content: .doc, .xml,....

create table tblRessources

(

IDRessource int identity(1,1) primary key,

FKUser int not null references tblUser,

Url varchar(500),

ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE default newid(),

Files varbinary(max) Filestream ,

Notice varchar(500),

Filetype varchar(30)

)

exec sp_fulltext_database 'enable';

select FULLTEXTSERVICEPROPERTY('IsFulltextInstalled')

CREATE FULLTEXT CATALOG [fulltextCatalogue]

CREATE UNIQUE INDEX fullIndex

ON tblRessources (IDRessource);

GO

create fulltext index on tblRessources(Files, TYPE COLUMN Filetype)

key index fullIndex

on [fulltextCatalogue]

with stoplist = system,

change_tracking = manual

Bis Dienstag - bin gespannt :hehe:!

LG, S.J.

Susanne

robert.jammernegg Valued Member Points: 72 More actions · Answer 3

Thanks for your help, I hope it works I will it try it today.

Kind Regards Robert.