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.