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