Local and Remote Index Servers returning different resultsets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan Howard - 14 Jul 2004 10:08 GMT

I have a project that uses a linked server in SQL Server 2000 to query a
local Index Server. This works fine.

I've been experimenting with moving the catalog to a remote Index Server and
querying it via Index Server running on the SQL Server machine (using the
four-part SCOPE syntax). I've followed plenty of other pointers relating to
syntax and security and seem to be getting there, but have noticed some
strange behaviour.

I seem to be getting a different number of results returned from the local
and remote index servers when I query them through Query Analyzer. My test
queries (which run over a catalog of about 12,000 documents) are very
simple:

select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope()')

This returns 12,012 results from NTS-1 (where the Index Server actually is)
and only 9,915 results when run from NTS-2 (where the SQL Server instance
and linked server are set up).

As soon as I add a CONTAINS clause:

select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope() where contains(''microsoft'')')

...I get 2,691 results on NTS-1 and no results on NTS-2. Changing the search
term makes no difference - no results (or errors) are returned from NTS-2 as
soon as I add the CONTAINS clause.

The two machines are setup very similarly - two Win2K Servers with SQL
Server 2000 Ent. Ed. Could the database and collation settings of the linked
server be acting up? Does anyone know what this strangeness is due to? I
seem to be passed all the permissions problems but this one is weird.

Thanks,

Alan

Reply to this Message

Hilary Cotter - 14 Jul 2004 16:08 GMT

NZ! I used to live in New Plymouth!

results are trimmed by security and scope. You have to verify that
1) you are using the same catalogs
2) the account you are querying with in SQL Server is present on the remote
server and on both servers they have identical rights
3) the content is the same on both servers
4) the default scope (\) is the same on both servers. To verify this open up
ciadv.msc and expand your default catalog. expand the directories folder and
ensure the directories which show up there are the same and they are both
physical, ie don't have globes on them
5) when you are not doing a contains, you are basically enumerating the file
system on the local and remote servers. If AllowEnumeration is turned off,
this will account for this behavior
6) try something like this

select * from openquery (ImpelHR, 'select filename from
"NTS-1".ImpelHR..scope(''shallow traversal of ""c:\""'')')

thats scope ( ' ' deep traversal of " " c:\ " " ' ' ) ' )

I think your problems are due to different content and probably that the
index is not finished building on one or more servers.

BTW - are all IS servers on the same version? IE is one on IS 2 (NT 4.0) and
the other on IS 3.x?

> I have a project that uses a linked server in SQL Server 2000 to query a
> local Index Server. This works fine.
[quoted text clipped - 34 lines]
>
> Alan

Reply to this Message