«

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

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...b;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.

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com


"Alan" <> wrote in message
news:...
> 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,
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]" <> wrote in message
news:#...
> 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...b;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.
>
> --
> Manohar Kamath
> Editor, .netBooks
> www.dotnetbooks.com
>
>
> "Alan" <> wrote in message
> news:...
> > 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
> >
> >
>
>

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-> wrote in message
news:eZ8$...
> 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]" <> wrote in message
> news:#...
> > 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...b;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.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > www.dotnetbooks.com
> >
> >
> > "Alan" <> wrote in message
> > news:...
> > > 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
> > >
> > >
> >
> >
>
>

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

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com


"Alan" <> wrote in message
news:%23DTMFB%...
> 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-> wrote in message
> news:eZ8$...
> > 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]" <> wrote in message
> > news:#...
> > > 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...b;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.
> > >
> > > --
> > > Manohar Kamath
> > > Editor, .netBooks
> > > www.dotnetbooks.com
> > >
> > >
> > > "Alan" <> wrote in message
> > > news:...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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]" <> wrote in message
news:ulPpWy%...
> Since you can UNION across databases, you can JOIN as well. I don't see
any
> problems there.
>
> --
> Manohar Kamath
> Editor, .netBooks
> www.dotnetbooks.com
>
>
> "Alan" <> wrote in message
> news:%23DTMFB%...
> > 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-> wrote in message
> > news:eZ8$...
> > > 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]" <> wrote in
message
> > > news:#...
> > > > 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...b;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.
> > > >
> > > > --
> > > > Manohar Kamath
> > > > Editor, .netBooks
> > > > www.dotnetbooks.com
> > > >
> > > >
> > > > "Alan" <> wrote in message
> > > > news:...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com


"Alan" <> wrote in message
news:...
> 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]" <> wrote in message
> news:ulPpWy%...
> > Since you can UNION across databases, you can JOIN as well. I don't see
> any
> > problems there.
> >
> > --
> > Manohar Kamath
> > Editor, .netBooks
> > www.dotnetbooks.com
> >
> >
> > "Alan" <> wrote in message
> > news:%23DTMFB%...
> > > 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-> wrote in message
> > > news:eZ8$...
> > > > 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]" <> wrote in
> message
> > > > news:#...
> > > > > 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...b;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.
> > > > >
> > > > > --
> > > > > Manohar Kamath
> > > > > Editor, .netBooks
> > > > > www.dotnetbooks.com
> > > > >
> > > > >
> > > > > "Alan" <> wrote in message
> > > > > news:...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>