Use of contains with image data types
-
I made a full text index over an image column in conjunction with a string column which retains the file extension (it may be .doc for example).
The full text index was populated and I try to query data from that column using contains for example (in Books Online says it must work properly).
But my problem is that any query I make get no results.
If you were working with full text search using image data types please give me an advice. Mabe I made some mistakes I don't know.
For example I've got a table Table_1 with a column _Image which contains the text 'Test'. The column _Image is associated with a column _Extension varchar(10) which store the value doc (i tried with .doc also)
If I realise the query
select * from Table_1 where contains (_Image , 'Test')
I get no results although the full text index exists on column _Image and this index have associated the document type column the column _Extension (see the stored procedures sp_fulltext_column
Thanks'
-
You need the full-stop, so ".doc", ".html" etc. in the doc type column.
Might be silly, but have you started the catalogue population off? ie. Started off full pop, turned on change tracking/ pop in background or whatever? The change
tracking can be slow because it waits for a fairly idle cpu.
I'm not sure what happens if fti isn't set up ok, but you can check this in QA with:
select fulltextserviceproperty('IsFulltextInstalled'),
databaseproperty(db_name(),'IsFulltextEnabled')
which should return 1's.
-
I am exasperated because anything I do it doesn't work.
Do you interogate an image data type using contains?
If so tell me how, please.
Mabe I didn't create the indexz properly but I don't know what test to do.
I created the full text index as it writes in books online, I created a char column with the extension and associate this column in the image index definition.
The problem is that I must query this fields it is important. Now I am in a break in this matter but I will review in 1 or two days. I already lost almost 2 days testing and reading and fighting with this image and contains problem.
Tranks.
-
simplest is:
SELECT UniqueID
FROM Table
WHERE CONTAINS(*,@Criteria)
where @Criteria would be something like
'FORMSOF(INFLECTIONAL,"test") AND FORMSOF(INFLECTIONAL,"criteria")'
or just 'test'
if you modify and run the following script against your db (just need to edit the table fti defn bit in step 4 - it's called "Item") then it should set stuff up ok - normally works but you never know
--//to add new tables to fti, just add the info to Step(4), section(3)
--//dcls
declare
@continue int
declare
@catid int
declare
@ftlang int
declare
@catname varchar(100)
declare
@tbname varchar(100)
declare
@index varchar(100)
--//initialise
set
@continue = 0
set @ftlang = 0x0809 --UK
--//step 1: check full text service is installed
if
(select fulltextserviceproperty('IsFulltextInstalled')) != 1
begin
select 'full text indexing not installed'
set @continue = 1
end
--//step 2: clean up fti
if
@continue = 0
begin
--//clean up non-registered resources
exec sp_fulltext_service 'clean_up'
--select fulltextserviceproperty('resource_usage')
--select fulltextserviceproperty('connect_timeout')
--select fulltextserviceproperty('data_timeout')
--//if fti not off, turn it off
if (select databaseproperty(db_name(),'IsFulltextEnabled')) = 1
begin
exec sp_fulltext_database 'disable'
end
--//reboot full text indexing
exec sp_fulltext_database 'enable'
end
--//step 3: drop any existing catalogs from the database
if
@continue = 0
begin
--//(1) drop any existing catalogs from the current database
declare cat_csr cursor for select ftcatid, name from dbo.sysfulltextcatalogs
open cat_csr
fetch next from cat_csr into @catid, @catname
while @@FETCH_STATUS = 0
begin
--//(1) first need to drop all tables from catalog
declare tb_csr cursor for select name from dbo.sysobjects where ftcatid = @catid
open tb_csr
fetch next from tb_csr into @tbname
while @@FETCH_STATUS = 0
begin
exec sp_fulltext_table @tbname, 'drop'
fetch next from tb_csr into @tbname
end
close tb_csr
deallocate tb_csr
--//(2) now can drop catalog
exec sp_fulltext_catalog @catname, 'drop'
fetch next from cat_csr into @catid, @catname
end
close cat_csr
deallocate cat_csr
end
--//step 4: create catalog and add tables
if
@continue = 0
begin
--//(2) Create new catalog
set @catname = db_name()+'_FTI'
exec sp_fulltext_catalog @catname, 'create'
--//(3) Add tables to catalog
--//Item
set @tbname = 'Item'
set @index = 'IX_Item_SystemUID'
exec sp_fulltext_table @tbname , 'create', @catname, @index
exec sp_fulltext_column @tbname , 'Title','add', @ftlang
exec sp_fulltext_column @tbname , 'Author','add', @ftlang
exec sp_fulltext_column @tbname , 'LastUpdatedBy','add', @ftlang
exec sp_fulltext_column @tbname , 'Summaryinfo','add', @ftlang
--//start population
declare tb_csr cursor for select b.name from dbo.sysfulltextcatalogs a join dbo.sysobjects b on b.ftcatid = a.ftcatid
open tb_csr
fetch next from tb_csr into @tbname
while @@FETCH_STATUS = 0
begin
exec sp_fulltext_table @tbname , 'Activate'
exec sp_fulltext_table @tbname , 'Start_change_tracking'
exec sp_fulltext_table @tbname , 'Start_background_updateindex'
exec sp_fulltext_table @tbname , 'Start_full'
fetch next from tb_csr into @tbname
end
close tb_csr
deallocate tb_csr
--//display set up tables
select a.ftcatid as CatId, a.name as CatName, b.name as TableName
from dbo.sysfulltextcatalogs a join dbo.sysobjects b on b.ftcatid = a.ftcatid
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply