Linked Server to IBM AS400

I need to create a linked server to communicate with an AS400 server by using
Client Access ODBC and then launch some query on particular files (inside
libraries).
How can I solve this issue?
I haven't founded any documentation about this subject.
Many thanks for your suggestsInstall Client Access on the SQL Server box and make sure
you have the client updated with whatever service pack level
based on updates to your AS400. Then configure the linked
server as described in books online under Linked Servers,
configuring.
For data source, use the IP address of the AS400.
For provider string, you need to include the library you are
using, connect timeout setting and code page. There is some
documentation for the settings in the Client Access help
files.
You'd set the provider string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
<Pasquale@.discussions.microsoft.com> wrote:
>I need to create a linked server to communicate with an AS400 server by using
>Client Access ODBC and then launch some query on particular files (inside
>libraries).
>How can I solve this issue?
>I haven't founded any documentation about this subject.
>Many thanks for your suggests|||Oh thanks!
I have tried to create a Client Access ODBC link, then I create a linked
server by setting only the data source with the Client Access ODBC data
source and user id + pwd. Then I have executed a SELECT by indicating the
linkedserver.linkedserver.libraryname.filename and I have the correct results.
Many thanks
"Sue Hoegemeier" wrote:
> Install Client Access on the SQL Server box and make sure
> you have the client updated with whatever service pack level
> based on updates to your AS400. Then configure the linked
> server as described in books online under Linked Servers,
> configuring.
> For data source, use the IP address of the AS400.
> For provider string, you need to include the library you are
> using, connect timeout setting and code page. There is some
> documentation for the settings in the Client Access help
> files.
> You'd set the provider string somewhat like:
> InitCat=YourLibrary;CCSID=37;PCCodePage=1252;
> Data Source=xxx.xxx.xxx.xxx
> Settings will depend on how your AS400 is configured. Again,
> the Client Access help files have information on the
> necessary connection string settings.
> -Sue
> On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
> <Pasquale@.discussions.microsoft.com> wrote:
> >I need to create a linked server to communicate with an AS400 server by using
> >Client Access ODBC and then launch some query on particular files (inside
> >libraries).
> >How can I solve this issue?
> >I haven't founded any documentation about this subject.
> >
> >Many thanks for your suggests
>

Linked Server to IBM AS400

I need to create a linked server to communicate with an AS400 server by usin
g
Client Access ODBC and then launch some query on particular files (inside
libraries).
How can I solve this issue?
I haven't founded any documentation about this subject.
Many thanks for your suggestsInstall Client Access on the SQL Server box and make sure
you have the client updated with whatever service pack level
based on updates to your AS400. Then configure the linked
server as described in books online under Linked Servers,
configuring.
For data source, use the IP address of the AS400.
For provider string, you need to include the library you are
using, connect timeout setting and code page. There is some
documentation for the settings in the Client Access help
files.
You'd set the provider string somewhat like:
InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
Data Source=xxx.xxx.xxx.xxx
Settings will depend on how your AS400 is configured. Again,
the Client Access help files have information on the
necessary connection string settings.
-Sue
On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
<Pasquale@.discussions.microsoft.com> wrote:

>I need to create a linked server to communicate with an AS400 server by usi
ng
>Client Access ODBC and then launch some query on particular files (inside
>libraries).
>How can I solve this issue?
>I haven't founded any documentation about this subject.
>Many thanks for your suggests|||Oh thanks!
I have tried to create a Client Access ODBC link, then I create a linked
server by setting only the data source with the Client Access ODBC data
source and user id + pwd. Then I have executed a SELECT by indicating the
linkedserver.linkedserver.libraryname.filename and I have the correct result
s.
Many thanks
"Sue Hoegemeier" wrote:

> Install Client Access on the SQL Server box and make sure
> you have the client updated with whatever service pack level
> based on updates to your AS400. Then configure the linked
> server as described in books online under Linked Servers,
> configuring.
> For data source, use the IP address of the AS400.
> For provider string, you need to include the library you are
> using, connect timeout setting and code page. There is some
> documentation for the settings in the Client Access help
> files.
> You'd set the provider string somewhat like:
> InitCat=YourLibrary;CCSID=37;PCCodePage=
1252;
> Data Source=xxx.xxx.xxx.xxx
> Settings will depend on how your AS400 is configured. Again,
> the Client Access help files have information on the
> necessary connection string settings.
> -Sue
> On Thu, 6 Jul 2006 06:56:01 -0700, Pasquale
> <Pasquale@.discussions.microsoft.com> wrote:
>
>

Linked Server to Foxpro

I am trying to setup a linked server from server1 (SQL Server) to Foxpro DBC
files on server2 and I get the following error:
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
I have checked that the account that SQL Server runs as has full permissions
on the folder where the DBC file lives.
I can also setup an ODBC connection and use MS Query to access the data but
SQL will not see it or give me any meaningful error!
If I copy the Foxpro files locally onto the SQL server then it is fine.
Once I have this working then I need to figure out how to create the
triggers to populate the Foxpro DBs!!
Any advice would be much appreciated.
Regards
JamesHi James,
When there are problems on a remote machine that don't show on the SQL
Server it's usually a problem of permissions. You said below that the SQL
Server has permissions on the remote machine, but maybe it's not working as
you expect.
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden
"JamesA" <JamesA@.discussions.microsoft.com> wrote in message
news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> I have checked that the account that SQL Server runs as has full
> permissions
> on the folder where the DBC file lives.
> I can also setup an ODBC connection and use MS Query to access the data
> but
> SQL will not see it or give me any meaningful error!
> If I copy the Foxpro files locally onto the SQL server then it is fine.|||Hi Cindy,
I looked at the SQL Server service on my server and it is running as
DOMAIN\SQLAdmin and on the folder where the Foxpro files live the account
DOMAIN\SQLAdmin has full permissions and even looking at the effective
permisions on the DBC file, this account has full permissions. Any
suggestions how I can test to see if this is a permissions problem?
Regards
James
"Cindy Winegarden" wrote:
> Hi James,
> When there are problems on a remote machine that don't show on the SQL
> Server it's usually a problem of permissions. You said below that the SQL
> Server has permissions on the remote machine, but maybe it's not working as
> you expect.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
> "JamesA" <JamesA@.discussions.microsoft.com> wrote in message
> news:B2472332-38A7-45D3-B2B8-F1F49DB277AB@.microsoft.com...
> > I have checked that the account that SQL Server runs as has full
> > permissions
> > on the folder where the DBC file lives.
> >
> > I can also setup an ODBC connection and use MS Query to access the data
> > but
> > SQL will not see it or give me any meaningful error!
> >
> > If I copy the Foxpro files locally onto the SQL server then it is fine.
>
>

Linked Server to DBC Data

Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
TravisSorry, for clarification, a DBC file is a FoxPro Data file.
"REM7600" <rem7600@.hotmail.com> wrote in message news:%23mkG6JLiGHA.4044@.TK2
MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset operati
on from SQL Server against once of the DBC files from one of our vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following e
rror...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************
Can someone advise?
Thanks!
Travis|||Hi Travis,
You haven't posted which version of SQL Server you are using or the code
you're using in the OpenRowset statement.
First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
I've successfully set up linked servers with code like this:
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_Northwind',
@.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
@.provider=N'VFPOLEDB',
@.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc',
@.provstr=N'VFPOLEDB.1'
After that you can access data with the four-part naming convention:
Select * From VFP_Northwind...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"REM7600" <rem7600@.hotmail.com> wrote in message
news:%23mkG6JLiGHA.4044@.TK2MSFTNGP03.phx.gbl...
Hi All,
I'm having trouble setting up a linked server OR using an OpenRowset
operation from SQL Server against once of the DBC files from one of our
vendors.
I can get connected through a DSN using Microsoft Access without trouble but
when trying to set up a linked server on the same DSN I get the following
error...
****************************************
**************************
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPr
o
Driver]Cannot open file c:\data\MyDB.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
****************************************
**************************|||Cindy,
Thanks for the reply... I've definitely seen your name a time or two around
the web in my search. As it turns out, I believe it to be related to
security. And how our network (Linux/Samba based shares) handles
authentication. I could be wrong but I've given up... Locally I can make
it work fine, on a share NO GO...
Regardless, I've taken a different direction on the solution and it looks
like I'm going to be able to come out with my "goaled for" end result.
Thanks for the reply.
TR

> you're using in the OpenRowset statement.
> First, be sure you have the latest FoxPro and Visual FoxPro OLE DB data
> provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
> I've successfully set up linked servers with code like this:
> EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_Northwind',
> @.srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',
> @.provider=N'VFPOLEDB',
> @.datasrc=N'C:\Program Files\Microsoft Visual FoxPro
> 9\Samples\Northwind\Northwind.dbc',
> @.provstr=N'VFPOLEDB.1'
>