Combining Index Server and SQL Server search results

A

Alan

I'm just about to start a project that needs to combine the results of a SQL
Server query with the results of an Index Server query. The basic idea is
that the user enters/selects a bunch of search criteria on a form. Most of
the criteria selected by the user will be used to select records from the
database - standard WHERE clause stuff - but the user can also enter
free-text that should be searched for in associated uploaded documents. The
documents are sitting in the file-system with file-name pointers only stored
in the database (not the document). Only records where the associated
free-text is found in the documents should be returned. I'm new to Index
Server and am wondering how is this done. Any good references/tutes?

ASP 3.0
IIS 5.0
Windows 2000 Server
SQL Server 2000

Cheers,

Alan
 
M

Manohar Kamath [MVP]

There are some good references in the Index server documentation itself, or
on MSDN. I have done index queries using Oledb driver for Index server, but
not combined with SQL server.

One idea is to create a linked server from SQL Server -- not sure if this is
even possible. But if it is, then you could query them together and combine
results.

http://support.microsoft.com/default.aspx?scid=kb;en-us;198493

The above article has precisely what you are looking for -- querying index
server from a SQL server. Just add another query and union the results.
 
J

John Kane

Alan,
Manohar, here's an example of both a local IS for the files and UNION'ed
with SQL FTS:

use master
go
EXEC sp_addlinkedserver 'Monarch', '', 'MSIDXS', 'Web', NULL, NULL
EXEC sp_addlinkedsrvlogin 'Monarch', 'FALSE', NULL, 'abc', ''
go

-- test IS query
select * from OpenQuery(Monarch, 'Select Directory,
FileName, size, Create, Write From SCOPE() Where size <= 200')
go

-- MSIDXS combined or UNIONed with SQL FTS query...

select * from titles where contains(*, 'books')
union
select * from OpenQuery(Monarch,
'select Directory, FileName, size, Create, Write
from SCOPE() where CONTAINS(Contents,''Index'')> 0 ')

Regards,
John
 
A

Alan

I've been a bit slack in replying and haven't tested any of this yet but all
this talk of UNIONing is getting me worried. Basically I'm looking for JOIN
functionality, where a row of data in my Applicants table includes the
path-name to a resume and covering letter stored in the file-system. I'm
hoping to query IS for content in the documents (keyword search), JOIN these
results with the results of a normal ...WHERE xxx LIKE 'xxx' AND yyy =
'yyy'... (etc.) query of the Applicant row data itself, and then display a
row for each Applicant record that satisfied both the SQL and IS search
criteria.

I'll have a read of the references provided but thought I'd add the above in
the meantime just to make sure I haven't misrepresented what I'm trying to
do. Apologies if this is all explained in the links provided.

Cheers,

Alan
 
A

Alan

Thanks for the reply (again). What does IS use as a key value - is it common
to take the filename of the document as found by IS and (in my case) join
that with the value of the filename column in SQL?

Cheers,

Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top