5,005 pts.
 Using iSeries as linked server in SQL Server 2012 Express
I downloaded SQL Server 2012 Express in order to attempt to create a database on a PC which has links to data living on our iSeries. I can't use the OLE DB2 extension from Microsoft since it doesn't work on SQL Server Express. So, I figured out (with Google's help) that I could run this script to get a linked table: (redacted)
USE main_db
GO
EXEC sp_addlinkedserver 
   @server = 'iSeries', 
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={iSeries Access ODBC Driver};SYSTEM=275.96.445.92;DBQ=mydefaultlib'
GO
This got it to show up as a linked server. Then, I go into the properties for that linked server and set the security to use my iSeries username/password. I can drill down into the list of files in the library I specified, but when I try to execute a select statement (select * from [ISeries].[serial#].[ELEMENTB].[file];), I get:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSDASQL" for linked server "iSeries" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "iSeries".
What do I need to do? Am I missing a step?

Software/Hardware used:
Windows 7 Pro, SQL Server 2012 Express RC0, iSeries, AS400
ASKED: January 13, 2012  6:12 PM
UPDATED: March 17, 2012  6:02 AM

Answer Wiki:
Last Wiki Answer Submitted:  Be the first to answer this question.
All Answer Wiki Contributors:  Be the first to answer this question. Bgmfhghfa9fa   135 pts. , Ben Rubenstein   2,060 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _