Not sure if I should post here of inetserver.indexserver, so I apologize
ahead of time.
In Sql2000, I've added a linked server to a development box.
EXEC sp_addlinkedserver Web,
'Index Server',
'MSIDXS',
'stjoedev01.CarenetWeb'
I ran the same command on my local Sql box.
When I execute this query
Select * from OpenQuery(web, 'Select DocTitle, vpath, Filename, Size,
Contents, characterization, PATH, URL, A_Href, rank, write
from stjoedev01.CarenetWeb..Scope(''"d:\Inetpub\"'')
where FREETEXT(Contents, ''adoption'') AND Rank > 50 order by rank desc,
write desc')
on my local box I get a result set. When I excute the same query on the
development sql box I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSIDXS' reported an error. Access denied.
[OLE/DB provider returned message: Invalid catalog name 'CARENETWEB'.
SQLSTATE=42000 ]
OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare
returned 0x80070005: Access denied.].
In both cases the query analyzer session is logged in with dbo access. I've
checked the file system catalog and "Everyone" has read/write access to the
index files.
The development sql instance is running under a local system account. My
local instance of sql server is running under a domain user account. Would
this account for the 'access denied'?
My ultimate goal is to add a linked server to a production SQL box, but it's
looking rather grim right now.
Thanks for the help,
Barry
The correct linked server command should be this
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''')
Try the above and see if this solves your problem.
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:ACAF9D1C-A2A1-42CE-8A34-57E6DE116A29@.microsoft.com...
> Not sure if I should post here of inetserver.indexserver, so I apologize
> ahead of time.
> In Sql2000, I've added a linked server to a development box.
> EXEC sp_addlinkedserver Web,
> 'Index Server',
> 'MSIDXS',
> 'stjoedev01.CarenetWeb'
> I ran the same command on my local Sql box.
> When I execute this query
> Select * from OpenQuery(web, 'Select DocTitle, vpath, Filename, Size,
> Contents, characterization, PATH, URL, A_Href, rank, write
> from stjoedev01.CarenetWeb..Scope(''"d:\Inetpub\"'')
> where FREETEXT(Contents, ''adoption'') AND Rank > 50 order by rank desc,
> write desc')
> on my local box I get a result set. When I excute the same query on the
> development sql box I get the following error:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSIDXS' reported an error. Access denied.
> [OLE/DB provider returned message: Invalid catalog name 'CARENETWEB'.
> SQLSTATE=42000 ]
> OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare
> returned 0x80070005: Access denied.].
> In both cases the query analyzer session is logged in with dbo access.
> I've
> checked the file system catalog and "Everyone" has read/write access to
> the
> index files.
> The development sql instance is running under a local system account. My
> local instance of sql server is running under a domain user account. Would
> this account for the 'access denied'?
> My ultimate goal is to add a linked server to a production SQL box, but
> it's
> looking rather grim right now.
> Thanks for the help,
> Barry
|||How do I need to configure the remote server? Is there anything special I
need to configure on the IS catalog that I'm trying to access remotely?
I've execute the sp_addlinkedserver as you suggest, created the IS catalog
on the SQL box and I'm now getting the dreaded
Invalid catalog name 'CARENETWEB'. SQLSTATE=42000
The biggest problem is I haven't a clue what I'm doing, so thanks again for
the help.
Barry
"Hilary Cotter" wrote:
> The correct linked server command should be this
> 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''')
> Try the above and see if this solves your problem.
>
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:ACAF9D1C-A2A1-42CE-8A34-57E6DE116A29@.microsoft.com...
>
>
|||can you query the local IS server?
Also what rights does the SQL Server agent have on the remote server? Is the
account the SQL Server agent runs under in the local admin group on the
remote server?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:B5846E6D-1855-4BE1-9C09-EDC214E7D409@.microsoft.com...
> How do I need to configure the remote server? Is there anything special I
> need to configure on the IS catalog that I'm trying to access remotely?
> I've execute the sp_addlinkedserver as you suggest, created the IS catalog
> on the SQL box and I'm now getting the dreaded
> Invalid catalog name 'CARENETWEB'. SQLSTATE=42000
> The biggest problem is I haven't a clue what I'm doing, so thanks again
for[vbcol=seagreen]
> the help.
> Barry
> "Hilary Cotter" wrote:
query it[vbcol=seagreen]
apologize[vbcol=seagreen]
Size,[vbcol=seagreen]
desc,[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
My[vbcol=seagreen]
Would[vbcol=seagreen]
but[vbcol=seagreen]
|||On the Sql Box I can use the query form of IS manager and get results. I can
execute the openquery against the local catalog without receiving the invalid
catalog error, but I don't get any results returned.
The Sql Server Agent runs under a domain account, which is a member of the
remote server's Administrators group.
"Hilary Cotter" wrote:
> can you query the local IS server?
> Also what rights does the SQL Server agent have on the remote server? Is the
> account the SQL Server agent runs under in the local admin group on the
> remote server?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:B5846E6D-1855-4BE1-9C09-EDC214E7D409@.microsoft.com...
> for
> query it
> apologize
> Size,
> desc,
> the
> to
> My
> Would
> but
>
>
|||Its puzzling that you don't get any hits from the local catalog. Can you
check 1) the IS server is running locally. 2) the catalog is indexing
diriectories, 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
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:8A815D4C-DED1-486F-9E20-ADDFF4A892A4@.microsoft.com...
> On the Sql Box I can use the query form of IS manager and get results. I
can
> execute the openquery against the local catalog without receiving the
invalid[vbcol=seagreen]
> catalog error, but I don't get any results returned.
> The Sql Server Agent runs under a domain account, which is a member of the
> remote server's Administrators group.
> "Hilary Cotter" wrote:
the[vbcol=seagreen]
special I[vbcol=seagreen]
remotely?[vbcol=seagreen]
catalog[vbcol=seagreen]
again[vbcol=seagreen]
where[vbcol=seagreen]
Filename,[vbcol=seagreen]
on[vbcol=seagreen]
'CARENETWEB'.[vbcol=seagreen]
ICommandPrepare::Prepare[vbcol=seagreen]
access.[vbcol=seagreen]
access[vbcol=seagreen]
account.[vbcol=seagreen]
account.[vbcol=seagreen]
box,[vbcol=seagreen]
|||The lack of hits from the local catalog was due to a bad keyboard driver. I
didn't change the scope of the query.
The permissions path you pointed me to seems to be the solution. I connected
to the Sql box with a Sql user account and was able to execute the query.
When I connect to the Sql box with windows credentials I get the invalid
catalog, access denied combination error.
Thank you so much for your assistance.
Barry
"Hilary Cotter" wrote:
> Its puzzling that you don't get any hits from the local catalog. Can you
> check 1) the IS server is running locally. 2) the catalog is indexing
> diriectories, 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
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:8A815D4C-DED1-486F-9E20-ADDFF4A892A4@.microsoft.com...
> can
> invalid
> the
> special I
> remotely?
> catalog
> again
> where
> Filename,
> on
> 'CARENETWEB'.
> ICommandPrepare::Prepare
> access.
> access
> account.
> account.
> box,
>
>
|||Hi Barry,
Could you please elaborate on how you got the openquery to work, and the
SQL user account credentials used.
I am currently struggling with the same problem you specified. However I
am already using SQL server authentication instead of windows
authentication.
I am stumped.
Many thanks in advance.
Cheers,
Luca
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Using the Management console on the Sql box I created a catalog with the same
name as the remote catalog that I wished to query.
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.
Hope that Helps,
Barry
"Luca Gnezda" wrote:
> Hi Barry,
> Could you please elaborate on how you got the openquery to work, and the
> SQL user account credentials used.
> I am currently struggling with the same problem you specified. However I
> am already using SQL server authentication instead of windows
> authentication.
> I am stumped.
> Many thanks in advance.
> Cheers,
> Luca
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Thanks very much Barry.
My environment reflects your setup, unfortunately it doesn't work.
However I have found that the excact same setup, using a pair of 2003
Servers instead of the 2000 servers works perfectly.
I suspect that I have incorrect versions of service packs, or some other
install/patch conflicting with the 2000 server setup.
I will continue playing with versions to find the root cause. Once again
thank you very much for your feedback.
Regards,
Luca
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
