El TecnoBaúl de Kiquenet

Kiquenet boring stories

Posts Tagged ‘msidx’

Indexing Server and Sql Server

Posted by kiquenet en 21 junio 2016

http://microsoft.public.sqlserver.fulltext.narkive.com/zbQKh2Km/ole-db-provider-msidxs-reported-an-error-access-denied

EXEC sp_addlinkedserver Web,’Index Server’,’MSIDXS’,’CarenetWeb’

where CarenetWeb is a catalog on the local server.

You can’t build a linked server to a remote IS catalog, but you can query it like this

Select * from openquery(LinkedServerName,’Select DocTitle, Vpath, Characterization from RemoteServerName.RemoteCatalogName..Scope()
where FileName like ”%.txt”’)

Using Query analyzer connected to the Sql server I executed a sp_addlinked server:

EXEC sp_addlinkedserver FileSystem, ‘Index Server’, ‘MSIDXS’, ‘CatalogName’

In the Sql Management console, under Security –> Linked Servers –>Properties, I set the Security tab option to "Be made using this security context:" and assigned a domain account that is a member of the local Administrators group on the remote server where the queried catalog resides.

Then constructed a query like this:

SELECT DocTitle, vpath, Filename, Size, Contents, characterization, PATH, URL, A_Href, rank, write
FROM RemoteServer.CatalogName..SCOPE(”" + this.Scope + "”)
Where FREETEXT(Contents,”" SearchPhrase"”)

Which I was able to then execute while connected with a sql account that was a db_owner.

The Sql Server Agent runs under a domain account, which is a member of the remote server’s Administrators group.

check
1) the IS server is running locally.
2) the catalog is indexing directories, expand the directories folder and verify there are directories there
3) the directories indexed are both real and virtual (the virtual directories have blue globes on them),
4) there are documents in the physical directories.

Also can you try this code agains your remote catalog – make sure you change this entry to reflect your actual remote server name and catalog

@objIxsso, ‘catalog’, ‘query://remoteservername.remotecatalogname’

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’, ‘query://remoteservername.remotecatalogname’
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

Posted in .NET, SQLServer | Etiquetado: , , | Leave a Comment »