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.