Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008r2 Linked Tables to AS400 Expand / Collapse
Author
Message
Posted Tuesday, March 6, 2012 7:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 6:33 AM
Points: 2, Visits: 3
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.
Post #1262167
Posted Sunday, March 11, 2012 3:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:47 PM
Points: 3,087, Visits: 1,437
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.





My blog
Post #1264924
Posted Monday, March 12, 2012 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 6:33 AM
Points: 2, Visits: 3
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.
Post #1265127
Posted Monday, March 12, 2012 12:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:47 PM
Points: 3,087, Visits: 1,437
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.





My blog
Post #1265473
Posted Tuesday, March 13, 2012 3:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 5, 2013 8:21 AM
Points: 2, Visits: 10
you me need to use the openquery
select * from OPENQUERY(<DB2 server>, <select statement within single quotes>)
Post #1265779
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse