SQL 2008r2 Linked Tables to AS400
-
I have been trying to get linked tables working in SQL 2008r2 to an AS400 with very limited success. Can anyone help point me in the right direction. Below is waht I have done so far.
I created a linked server using the Microsoft OLE DB provider for ODBC Drivers. I can browse all the tables on the AS400 but when I try to do a select on a table that has data in it I get the following error.
My select statement is select * from CCSDTA.S100CA8R.CCSDTA.CTRLFILE
The error is Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "CCSDTA" reported an error. Provider caused a server fault in an external process.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "CCSDTA".
If I try to use the either of the 3 IBM DB2 providers to create a linked table I can connect up and get most data with no issue, however, the data types are not being brought over correctly. Everything comes accross as a text field.
Any help with linked tables in 2008r2 will be appreciated.
-
dbarkley (3/6/2012)
I have been trying to get linked tables working in SQL 2008r2 to an AS400 with very limited success. Can anyone help point me in the right direction. Below is waht I have done so far.I created a linked server using the Microsoft OLE DB provider for ODBC Drivers. I can browse all the tables on the AS400 but when I try to do a select on a table that has data in it I get the following error.
My select statement is select * from CCSDTA.S100CA8R.CCSDTA.CTRLFILE
The error is Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "CCSDTA" reported an error. Provider caused a server fault in an external process.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "CCSDTA".
If I try to use the either of the 3 IBM DB2 providers to create a linked table I can connect up and get most data with no issue, however, the data types are not being brought over correctly. Everything comes accross as a text field.
Any help with linked tables in 2008r2 will be appreciated.
I may be wrong, it has been a long time since I worked with AS400 linked Server, but I thing you need to install the AS400 drivers on your SQL Server and make an ODBC connection to the AS400.
-
The error in the first part is from when I try using an ODBC connection using the IBM drivers that came with client access.
The second part is when I try to use the providers in SQL to make a direct connection without ODBC, the mapping dont come across correctly.
-
dbarkley (3/12/2012)
The error in the first part is from when I try using an ODBC connection using the IBM drivers that came with client access.The second part is when I try to use the providers in SQL to make a direct connection without ODBC, the mapping dont come across correctly.
Maybe you can try following the steps in this link.
-
you me need to use the openquery
select * from OPENQUERY(<DB2 server>, <select statement within single quotes>)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply