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