By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,630 Members | 1,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,630 IT Pros & Developers. It's quick & easy.

Combining Index Server and SQL Server search results

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:er**************@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

Jul 19 '05 #2

P: n/a
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]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:#u**************@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.

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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:er**************@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


Jul 19 '05 #3

P: n/a
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*****@comcast.net> wrote in message
news:eZ**************@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]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:#u**************@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.

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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:er**************@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



Jul 19 '05 #4

P: n/a
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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:%2******************@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*****@comcast.net> wrote in message
news:eZ**************@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]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:#u**************@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.

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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:er**************@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
>
>



Jul 19 '05 #5

P: n/a
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]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:ul***************@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
www.dotnetbooks.com
"Alan" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:%2******************@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*****@comcast.net> wrote in message
news:eZ**************@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]" <mk*****@TAKETHISOUTkamath.com> wrote in message news:#u**************@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.
>
> 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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
> news:er**************@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
> >
> >
>
>



Jul 19 '05 #6

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

--
Manohar Kamath
Editor, .netBooks
www.dotnetbooks.com
"Alan" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:eV**************@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]" <mk*****@TAKETHISOUTkamath.com> wrote in message
news:ul***************@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
www.dotnetbooks.com
"Alan" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
news:%2******************@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*****@comcast.net> wrote in message
news:eZ**************@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]" <mk*****@TAKETHISOUTkamath.com> wrote in

message > news:#u**************@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.
> >
> > 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" <Xa*************@XparadiseX.XnetX.XnzX> wrote in message
> > news:er**************@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
> > >
> > >
> >
> >
>
>



Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.