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
>
Showing posts with label odbc. Show all posts
Showing posts with label odbc. Show all posts
Monday, February 20, 2012
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:
>
>
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 DB2
Hi,
I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.
I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.
Currently when i Insert the following error is encountered :
OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSITIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.
When i try to select a CLOB column in DB2 the following error is encountered:
OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABILITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".
Thanks in advance for any answer.
AshwinHi Ashwin, I am trying to setup a Linked Server to DB2 using the same driver. Can you please email me the screen shot of your settings or the script to create it?
Thanks,
Matt
Quote:
Originally Posted by ashwingawande
Hi,
I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.
I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.
Currently when i Insert the following error is encountered :
OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSITIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.
When i try to select a CLOB column in DB2 the following error is encountered:
OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABILITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".
Thanks in advance for any answer.
Ashwin
Linked Server to AS400 via ODBC Codepage Problem
Hi!
I am connecting to an as400 server (v5r2) from my sql 2000 server
using the oledb provider for odbc. In the provider string i use:
Driver={iSeries Access ODBC Driver};System=myServer;DefaultLibraries
=QG
PL;QUERYTIMEOUT=0;CCSID=1253;UNICODESQL=
1;TRANSLATE=0;
which works fine with english data i try to retrieve. However
some of the data is in greek. All the greek data comes back intelligible.
the codepage on the as400 system is 37.
Could any one please help me?
Thank you all in advance.Well,
In America it's common that when something is illegible, we say "It looks li
ke Greek to me". So maybe they figured if you want it back in Greek, they w
ould just give you junk
I am connecting to an as400 server (v5r2) from my sql 2000 server
using the oledb provider for odbc. In the provider string i use:
Driver={iSeries Access ODBC Driver};System=myServer;DefaultLibraries
=QG
PL;QUERYTIMEOUT=0;CCSID=1253;UNICODESQL=
1;TRANSLATE=0;
which works fine with english data i try to retrieve. However
some of the data is in greek. All the greek data comes back intelligible.
the codepage on the as400 system is 37.
Could any one please help me?
Thank you all in advance.Well,
In America it's common that when something is illegible, we say "It looks li
ke Greek to me". So maybe they figured if you want it back in Greek, they w
ould just give you junk
Linked server to AS400 using iSeries ODBC
Hello all,
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Linked server to AS400 using iSeries ODBC
Hello all,
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!
I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!
I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Subscribe to:
Posts (Atom)