I'm using a remote win2000 web hosting company, with give us the use
of MSSQL2000, and MS Index server. I've written a database, and have
index server switched on. I now want to write my own site ( simple )
search engine, as commerical solutions are too expensive.

The hosting company does not allow the use of linked servers in MSSQL
( I do not have admin rights to my MSSQL account), and I cannot write
files to my hosting space directly using asp.

Question ( Finally ! ) : Is there a way of connecting MSSQL &  Index
Server together, without the use of a linked server - so that both
MSSQL & index Server can be queried ( joined ) in one sql ( asp )
statement i.e. one recordset being produced ? I do not want to create
a static page for every possible page in the database !

thanks

Jamie

---

Request for Question Clarification by answerguru-ga on 18 Aug 2002 10:05 PDT

Hi jamwales-ga,

Would you consider running two simultaneous, identical queries on both
sides and then compiling the results within your script? Of course,
I'm assuming that the schema of both sets of data are identical.

Thanks,
answerguru-ga

---

Clarification of Question by jamwales-ga on 19 Aug 2002 04:38 PDT

Thank you both  for responding, and so onto the clarification :

Both data sets ( index server and static html pages ) are linked, but
do not have the same schemas, so identical queries will not work.
Sorry, my fault for not explaining in the first instance.

Basically :

1. The MSSQL2000 database contains information for categories ( cat )
and areas ( area ) for advertisers ( adcode ), and the static pages
called adcode.html ( i.e. the link to the sql database ) contain the
actual html for the resulting advert - contained in the index server
catalog. I store the html code outside mssql, to save expensive and
limited mssql space.

2. The problem is, that the index server catalog does not contain the
areas and categories to which the static advert relates.


So for example, static html advert called : testad.html

can contain database records :

AdCode	Categories		Areas

testad	cat1			area1
testad	cat1			area3

testad	cat6			area2	


Ideally, rather than pull up the static page, I would like to pull up
the asp pages for all cat1 in area1 adverts ...

thanks

Jamie

Hi Jamwales,
I'd say it's unlikely that there is another server-side mechanism that
you can use to combine the two SQL sources, and if there is one, I
doubt your host will allow it.
Perhaps you might rethink your ideas a little. You said you wanted a
simple search: I'm assuming that your static content that would be
collated in Index Server will not be essentially related to your data
in SQL server? If so, it might be a nice idea to fire the same query
criteria at both servers with different statements and actually
present the two sets separately, with a slightly different design..

For instance, if on your static side you had, say, "news articles" and
in your database you had "Cars" and "Prices", you might present the
results of your search thus:

_News Articles_

1. Result 1.. blah.. blah..
2. Result 2.. etc.. etc..

_Cars_

a. Ford             $20,000
b. Mercedes         $40,000


etc, etc..

The delineation of contexts (news, cars etc..) makes it very clear
what the results mean. It also helps you to prioritise results. For
instance, you might know that people will normally want to search for
Car information. Showing the results from News might be secondary. You
might even allow the user to choose different sources for the results.
Whereas combining the two results is a nice technical feat, would it
actually make for a clean and obvious search result page? Something to
think of...

If you really, really do want to do it, a cheap and cheerful way might
be to create a prodecure in ASP that grabs the Index Server results
and fires them into a temporary table in your database, then you can
query that table and your  SQL data together (the temporary table
would have to be global and you'd have to use the same connection for
inserting the index server data into your main database and search
from both result sets). A bit crappy, but it might work.

Hope that was of help..

BB

I think what you're after is full-text indexing on the SQL db?  It
uses Index server under the covers I understand, and the syntax is
very simple once you've created the full-text catalogs - see SQL Books
Online for help.

Or I may have missed the point altogether!

i think what you want to look at is the OPENROWSET statement; as long
as you have an oledb provider you're good...i think there's also a
statement that supports odbc but not 100% on that