Linked Server - Limiting Index Search Result

Hi all,
I'm making some experiments with the Full Text features offered by
MS-SQLServer (MSSEARCH) and the Indexing Service.
Querying MSSEARCH, I can limit the result using top_n_by_rank
in CONTAINSTABLE / FREETEXTTABLE.
I wasn't able to find out anything I can use to get the same result querying
the Indexing Service as a linked server.
If I try the following:
SELECT Q.*
FROM OpenQuery(FileSystem,
'SELECT TOP 100 rank, FileName, Size, DocAuthor, DocTitle
FROM TESTCATALOG..SCOPE('' "" '')
WHERE FREETEXT(''enhacing security'')
ORDER by rank') AS Q
I got an error:
Server: Msg 7321, Level 16, State 2, Line 1
[OLE/DB provider returned message: Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000 ]
[OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare returned 0x80040e14].
(I guess TOP is not supported by syntax).
Of course I can do something like this:
SELECT TOP 100 Q.*
FROM OpenQuery(FileSystem,
'SELECT rank, FileName, Size, DocAuthor, DocTitle
FROM TESTCATALOG..SCOPE('' "" '')
WHERE FREETEXT(''enhacing security'')' ) AS Q
order by Q.rank desc
but in this case (I think..) all the matching rows are returned by
Indexing Service and then SQLServer get the first N rows discarding
the rest.
Do you know if there is some way to instruct the Index Service
(i.e. MSIDXS provider) to return only the first N best matches ?
Thanks a lot for your help
MadMax
The MSIDXS provider does not provide a method to limit the number of rows it will return.
Another option is to use the sp_oacreate objects and instantiate the ixsso object.
Here is an example. Here I am limitiing it to 200 records.
--usage: sp_IndexServer 'microsoft'
create procedure sp_indexServer @.input char(255)
as
declare @.HResult int
declare @.objIxsso int
declare @.RecordSet int
declare @.recordCount int
declare @.Count int
declare @.fields int
declare @.path varchar(100)
declare @.vpath varchar(100)
declare @.doctitle varchar(100)
declare @.size varchar(100)
declare @.filename varchar(100)
declare @.write varchar(100)
declare @.rank varchar(100)
declare @.hitcount varchar(100)
EXEC @.HResult = sp_OACreate 'Ixsso.query', @.objIxsso Out
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Query', @.input
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'catalog', 'web'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'Columns', _
'path, vpath, doctitle,size,filename,write,rank,hitcount'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'MaxRecords', '200'
EXEC @.HResult = sp_OASetProperty @.objIxsso, 'SortBy', 'rank[d]'
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
exec @.HResult =sp_OAMethod @.objIxsso,_
'CreateRecordSet("nonsequential")',@.recordSet OUTPUT
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
EXEC @.HResult = sp_OAGetProperty @.recordSet, 'RecordCount',_
@.RecordCount output
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
if @.recordcount =0
print 'No matches found'
else
begin
print convert (varchar(5),@.recordcount) +' matches found'
SET @.Count = 1
WHILE ( @.Count <= @.RecordCount )
BEGIN
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,0
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.path output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,1
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.vpath output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,2
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.doctitle output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,3
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.size output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', _
@.fields out,4
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', _
@.filename output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,5
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.write output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,6
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.rank output
EXEC @.HResult = sp_OAGetProperty @.RecordSet, 'fields', @.fields out,7
EXEC @.HResult = sp_OAGetProperty @.fields, 'Value', @.hitcount output
print @.path +', ' +@.vpath+', '+@.doctitle+', '+@.size +', '+_
@.filename +', '+@.write+', '+@.rank+', '+@.hitcount
EXEC @.HResult =sp_OAMethod @.RecordSet, 'MoveNext'
select @.count=@.Count+1
END
print @.count
EXEC @.HResult = sp_OADestroy @.fields
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.fields
RETURN
END
END
EXEC @.HResult = sp_OADestroy @.recordset
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.recordset
RETURN
END
EXEC @.HResult = sp_OADestroy @.objIxsso
IF @.HResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.objIxsso
RETURN
END
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MadMax" wrote:

> Hi all,
> I'm making some experiments with the Full Text features offered by
> MS-SQLServer (MSSEARCH) and the Indexing Service.
> Querying MSSEARCH, I can limit the result using top_n_by_rank
> in CONTAINSTABLE / FREETEXTTABLE.
> I wasn't able to find out anything I can use to get the same result querying
> the Indexing Service as a linked server.
> If I try the following:
> SELECT Q.*
> FROM OpenQuery(FileSystem,
> 'SELECT TOP 100 rank, FileName, Size, DocAuthor, DocTitle
> FROM TESTCATALOG..SCOPE('' "" '')
> WHERE FREETEXT(''enhacing security'')
> ORDER by rank') AS Q
>
> I got an error:
> Server: Msg 7321, Level 16, State 2, Line 1
> [OLE/DB provider returned message: Incorrect syntax near '100'. Expected ',', '.', FROM. SQLSTATE=42000 ]
> [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare returned 0x80040e14].
> (I guess TOP is not supported by syntax).
> Of course I can do something like this:
> SELECT TOP 100 Q.*
> FROM OpenQuery(FileSystem,
> 'SELECT rank, FileName, Size, DocAuthor, DocTitle
> FROM TESTCATALOG..SCOPE('' "" '')
> WHERE FREETEXT(''enhacing security'')' ) AS Q
> order by Q.rank desc
> but in this case (I think..) all the matching rows are returned by
> Indexing Service and then SQLServer get the first N rows discarding
> the rest.
> Do you know if there is some way to instruct the Index Service
> (i.e. MSIDXS provider) to return only the first N best matches ?
> Thanks a lot for your help
> MadMax