Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Wednesday, March 21, 2012
oledb to ibm db2 - no tables?
Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql
Friday, March 9, 2012
OLE DB provider 'MSIDXS' reported an error. Access denied.
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!
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!
Monday, February 20, 2012
Old Excel files after SQL2000 > SQL2005 migration
Hello there!
We are having some problems over here after our SQL2005 upgrade.
If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..
An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.
Please note that if we try to make a new external database query it works fine!
Any suggestions?
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.
So our hopes are still left to you!
|||Looks like a change in SQL 2005.
Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).
SQL 2000 allows this, SQL 2005 requires you to be sysadmin.
I can't see any other way to work around this.
|||Thank you for your answer!
Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?
We have not at any time used qouted identifiers, so that would not be a problem.
|||We're talking this over internally now, I'll see what I can find out.|||I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.
Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
|||I talked to one of my Office gurus and he said:
Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:
'sample VBA macro
Sub Test()
Dim q as QueryTable
Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet
q.Connection = "<your new connection>"
q.Refresh
End Sub
So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
|||Also note I filed a bug for this internally so we will get this fixed.|||Thank you very much!
That actually worked well
Have a nice weekend!
|||How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.
|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?
|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||Any news on a fix for this issue
The drivers with sp1 do they have a fix or is there a hotfix available
|||SP1 did not change anything for us..
But the script that someone provided earlier fixes the problem client side..
Old Excel files after SQL2000 > SQL2005 migration
Hello there!
We are having some problems over here after our SQL2005 upgrade.
If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..
An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.
Please note that if we try to make a new external database query it works fine!
Any suggestions?
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.
So our hopes are still left to you!
|||Looks like a change in SQL 2005.
Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).
SQL 2000 allows this, SQL 2005 requires you to be sysadmin.
I can't see any other way to work around this.
|||Thank you for your answer!
Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?
We have not at any time used qouted identifiers, so that would not be a problem.
|||We're talking this over internally now, I'll see what I can find out.|||I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.
Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
|||I talked to one of my Office gurus and he said:
Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:
'sample VBA macro
Sub Test()
Dim q as QueryTable
Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet
q.Connection = "<your new connection>"
q.Refresh
End Sub
So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
|||Also note I filed a bug for this internally so we will get this fixed.|||Thank you very much!
That actually worked well
Have a nice weekend!
|||How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.
|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?
|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||Any news on a fix for this issue
The drivers with sp1 do they have a fix or is there a hotfix available
|||SP1 did not change anything for us..
But the script that someone provided earlier fixes the problem client side..
Old Excel files after SQL2000 > SQL2005 migration
Hello there!
We are having some problems over here after our SQL2005 upgrade.
If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..
An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.
Please note that if we try to make a new external database query it works fine!
Any suggestions?
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.
So our hopes are still left to you!
|||Looks like a change in SQL 2005.
Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).
SQL 2000 allows this, SQL 2005 requires you to be sysadmin.
I can't see any other way to work around this.
|||Thank you for your answer!
Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?
We have not at any time used qouted identifiers, so that would not be a problem.
|||We're talking this over internally now, I'll see what I can find out.|||I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.
Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
|||I talked to one of my Office gurus and he said:
Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:
'sample VBA macro
Sub Test()
Dim q as QueryTable
Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet
q.Connection = "<your new connection>"
q.Refresh
End Sub
So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
|||Also note I filed a bug for this internally so we will get this fixed.|||Thank you very much!
That actually worked well
Have a nice weekend!
|||How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.
|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?
|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||Any news on a fix for this issue
The drivers with sp1 do they have a fix or is there a hotfix available
|||SP1 did not change anything for us..
But the script that someone provided earlier fixes the problem client side..
Old Excel files after SQL2000 > SQL2005 migration
Hello there!
We are having some problems over here after our SQL2005 upgrade.
If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..
An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.
Please note that if we try to make a new external database query it works fine!
Any suggestions?
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.
So our hopes are still left to you!
|||Looks like a change in SQL 2005.
Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).
SQL 2000 allows this, SQL 2005 requires you to be sysadmin.
I can't see any other way to work around this.
|||Thank you for your answer!
Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?
We have not at any time used qouted identifiers, so that would not be a problem.
|||We're talking this over internally now, I'll see what I can find out.|||I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.
Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
|||I talked to one of my Office gurus and he said:
Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:
'sample VBA macro
Sub Test()
Dim q as QueryTable
Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet
q.Connection = "<your new connection>"
q.Refresh
End Sub
So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
|||Also note I filed a bug for this internally so we will get this fixed.|||Thank you very much!
That actually worked well
Have a nice weekend!
|||How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.
|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?
|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||Any news on a fix for this issue
The drivers with sp1 do they have a fix or is there a hotfix available
|||SP1 did not change anything for us..
But the script that someone provided earlier fixes the problem client side..
Old Excel files after SQL2000 > SQL2005 migration
Hello there!
We are having some problems over here after our SQL2005 upgrade.
If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."
We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..
An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.
Please note that if we try to make a new external database query it works fine!
Any suggestions?
We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.
So our hopes are still left to you!
|||Looks like a change in SQL 2005.
Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).
SQL 2000 allows this, SQL 2005 requires you to be sysadmin.
I can't see any other way to work around this.
|||Thank you for your answer!
Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?
We have not at any time used qouted identifiers, so that would not be a problem.
|||We're talking this over internally now, I'll see what I can find out.|||I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.
Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.
|||I talked to one of my Office gurus and he said:
Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:
'sample VBA macro
Sub Test()
Dim q as QueryTable
Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet
q.Connection = "<your new connection>"
q.Refresh
End Sub
So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.
|||Also note I filed a bug for this internally so we will get this fixed.|||Thank you very much!
That actually worked well
Have a nice weekend!
|||How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.
|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?
|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||Any news on a fix for this issue
The drivers with sp1 do they have a fix or is there a hotfix available
|||SP1 did not change anything for us..
But the script that someone provided earlier fixes the problem client side..
Subscribe to:
Posts (Atom)