Combining Index Server and SQL Server search results

[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

[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

[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.

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

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.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> 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
>
>

[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.

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

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.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> 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
>
>

[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




"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> 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.
>
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
>
> 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.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> > 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
> >
> >

>
>

[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




"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> 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.
>
> [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
>
> 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.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> > 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
> >
> >

>
>

[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


"John Kane" <jt-kane@comcast.net> wrote in message
news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> 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
>
>
>
>
> "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> > 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.
> >
> > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> >
> > 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.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > [url]www.dotnetbooks.com[/url]
> >
> >
> > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> > > 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
> > >
> > >

> >
> >

>
>

[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


"John Kane" <jt-kane@comcast.net> wrote in message
news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> 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
>
>
>
>
> "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> > 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.
> >
> > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> >
> > 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.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > [url]www.dotnetbooks.com[/url]
> >
> >
> > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...

> > > 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
> > >
> > >

> >
> >

>
>

[Manohar Kamath [MVP]]

Since you can UNION across databases, you can JOIN as well. I don't see any
problems there.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> 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
>
>
> "John Kane" <jt-kane@comcast.net> wrote in message
> news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> > 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
> >
> >
> >
> >
> > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> > > 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.
> > >
> > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > >
> > > 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.

> > >
> > > --
> > > Manohar Kamath
> > > Editor, .netBooks
> > > [url]www.dotnetbooks.com[/url]
> > >
> > >
> > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Manohar Kamath [MVP]]

Since you can UNION across databases, you can JOIN as well. I don't see any
problems there.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> 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
>
>
> "John Kane" <jt-kane@comcast.net> wrote in message
> news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> > 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
> >
> >
> >
> >
> > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...

> > > 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.
> > >
> > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > >
> > > 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.

> > >
> > > --
> > > Manohar Kamath
> > > Editor, .netBooks
> > > [url]www.dotnetbooks.com[/url]
> > >
> > >
> > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[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

"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:ulPpWy%23dDHA.392@TK2MSFTNGP12.phx.gbl...

> Since you can UNION across databases, you can JOIN as well. I don't see

any

> problems there.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> > 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
> >
> >
> > "John Kane" <jt-kane@comcast.net> wrote in message
> > news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> > > 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
> > >
> > >
> > >
> > >
> > > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in

message

> > > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...
> > > > 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.
> > > >
> > > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > > >
> > > > 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.

> > > >
> > > > --
> > > > Manohar Kamath
> > > > Editor, .netBooks
> > > > [url]www.dotnetbooks.com[/url]
> > > >
> > > >
> > > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[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

"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:ulPpWy%23dDHA.392@TK2MSFTNGP12.phx.gbl...

> Since you can UNION across databases, you can JOIN as well. I don't see

any

> problems there.
>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> > 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
> >
> >
> > "John Kane" <jt-kane@comcast.net> wrote in message
> > news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...

> > > 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
> > >
> > >
> > >
> > >
> > > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in

message

> > > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...
> > > > 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.
> > > >
> > > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > > >
> > > > 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.

> > > >
> > > > --
> > > > Manohar Kamath
> > > > Editor, .netBooks
> > > > [url]www.dotnetbooks.com[/url]
> > > >
> > > >
> > > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Manohar Kamath [MVP]]

Depends on your table definition in SQL, although file path/name would be an
obvious choice.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:eVM2QUAeDHA.2300@TK2MSFTNGP10.phx.gbl...

> 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
>
> "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> news:ulPpWy%23dDHA.392@TK2MSFTNGP12.phx.gbl...

> > Since you can UNION across databases, you can JOIN as well. I don't see

> any

> > problems there.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > [url]www.dotnetbooks.com[/url]
> >
> >
> > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> > > 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
> > >
> > >
> > > "John Kane" <jt-kane@comcast.net> wrote in message
> > > news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in

> message

> > > > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...
> > > > > 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.
> > > > >
> > > > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > > > >
> > > > > 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.

> > > > >
> > > > > --
> > > > > Manohar Kamath
> > > > > Editor, .netBooks
> > > > > [url]www.dotnetbooks.com[/url]
> > > > >
> > > > >
> > > > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Manohar Kamath [MVP]]

Depends on your table definition in SQL, although file path/name would be an
obvious choice.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
news:eVM2QUAeDHA.2300@TK2MSFTNGP10.phx.gbl...

> 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
>
> "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
> news:ulPpWy%23dDHA.392@TK2MSFTNGP12.phx.gbl...

> > Since you can UNION across databases, you can JOIN as well. I don't see

> any

> > problems there.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > [url]www.dotnetbooks.com[/url]
> >
> >
> > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > news:%23DTMFB%23dDHA.3072@TK2MSFTNGP11.phx.gbl...

> > > 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
> > >
> > >
> > > "John Kane" <jt-kane@comcast.net> wrote in message
> > > news:eZ8$BmVcDHA.2436@TK2MSFTNGP12.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > > "Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in

> message

> > > > news:#ujtzxDcDHA.1204@TK2MSFTNGP12.phx.gbl...
> > > > > 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.
> > > > >
> > > > > [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;198493[/url]
> > > > >
> > > > > 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.

> > > > >
> > > > > --
> > > > > Manohar Kamath
> > > > > Editor, .netBooks
> > > > > [url]www.dotnetbooks.com[/url]
> > > > >
> > > > >
> > > > > "Alan" <XalanX.XhowardX@XparadiseX.XnetX.XnzX> wrote in message
> > > > > news:ercGgvCcDHA.1744@TK2MSFTNGP12.phx.gbl...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>