Combining Index Server and SQL Server search results

Discussion in 'ASP General' started by Alan, Sep 1, 2003.

  1. Alan

    Alan Guest

    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, Sep 1, 2003
    #1
    1. Advertising

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

    --
    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
    >
    >
    Manohar Kamath [MVP], Sep 1, 2003
    #2
    1. Advertising

  3. Alan

    John Kane Guest

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

    >
    >
    John Kane, Sep 2, 2003
    #3
  4. Alan

    Alan Guest

    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" <> 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.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.
    > >
    > > --
    > > 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, Sep 10, 2003
    #4
  5. 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" <> 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.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.
    > > >
    > > > --
    > > > 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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Manohar Kamath [MVP], Sep 10, 2003
    #5
  6. Alan

    Alan Guest

    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" <> 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.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.
    > > > >
    > > > > --
    > > > > 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, Sep 11, 2003
    #6
  7. 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" <> 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.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.
    > > > > >
    > > > > > --
    > > > > > 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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Manohar Kamath [MVP], Sep 11, 2003
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. savvy
    Replies:
    0
    Views:
    763
    savvy
    Jan 14, 2006
  2. Yas
    Replies:
    0
    Views:
    475
  3. Ken Fine
    Replies:
    3
    Views:
    521
    Steven Cheng [MSFT]
    Jul 23, 2008
  4. Tomasz Chmielewski

    sorting index-15, index-9, index-110 "the human way"?

    Tomasz Chmielewski, Mar 4, 2008, in forum: Perl Misc
    Replies:
    4
    Views:
    270
    Tomasz Chmielewski
    Mar 4, 2008
  5. Craig Keightley
    Replies:
    10
    Views:
    256
    Craig Keightley
    Jun 29, 2005
Loading...

Share This Page