AS/400 Linked Server - Need Expert Help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

AS/400 Linked Server - Need Expert Help

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-11 : 10:08:37

OK, I've googled and experimented and I really can't figure this out. I have an AS/400 running OS/400 Version 5 Release 2 Mod. 0. I've been trying to use the IBM DB2 OLEDB Provider (IBMDA400) to set up a linked server and haven't had any luck, I get the following:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'IBMDA400'.
OLE DB error trace [Non-interface error: CoCreate of DSO for IBMDA400 returned 0x80040154].

That error occurs when using OPENQUERY or 4-part names. The driver is installed correctly, I can use it with DTS packages and it works fine. I have tried multiple variations on the provider string that I took from the DTS code, none of them work when trying to do a linked server. I'm running SQL Server 2000 SP3a Enterprise on Win2K Advanced SP4.

I did try the MS DB2 OLEDB provider that comes with Host Integration Server, but I've had no luck with that either. I can't remember the error message but I think it was the standard 7399 or some other error. I'm going to try again, but if anyone has any suggestions about what settings are important or not, or which are problematic, I'd really appreciate it.

JHeil
Starting Member

1 Post

Posted - 2005-05-19 : 19:21:01

Had the same problem,but came up with this after a lot of head-banging:
(Note: I set up an ODBC connection using the iSeries Access ODBC Driver, and named it the server dsn name, first.)

(based on post at http://dbforums.com/t477702.html)

Using UDL (Universal Data Link) to generate the connection string:

1. Open Notepad and save an empty file as *.udl. For example, "test.udl"
2. Double click this file, then the configuration window pops up
2.1. CLick the Connection tab
2.1.1. Enter server dsn name as source name (or IP address)
2.1.2. Enter user ID and password
2.1.3. Check the "Allow Saving Paswsword" checkbox
2.1.4. Enter the initial catalog (database)
2.2. Click the Provider tab
2.2.1. Click "Microsoft OLE DB Provider for ODBC Drivers"
3. Click 'OK' button to exit this window.
3.1. Ignore warning about exposed password for now(although you may want to delete "test.udl" later)
4. Now open this file in Notepad. You will find the connection string in this file.

Linking the server:

5. Open SQL Server Enterpise Mgr (you can do it using TSQL in Query, but I'm being lazy here - see sp_addlinkedserver in SQL Books Online)
6. Goto [ServerName]> Security> Linked Servers
6.1. Right click on Linked Servers and hit New Linked Server...
7. Server type is "Other data source"
7.1. Provider Name: "Microsoft OLE DB Provider for ODBC Drivers"
7.2. Product Name: whatever you want to label it (I used the server dsn)
7.3. Provider string: the string from step 4, above.
7.4. Leave 'Location' and 'Catalog' empty
8. Hit the "Server Options" tab
8.1. "Data Access" and "Use Remote Collation" should be checked
9. Hit "Save" button

To test:

10. Expand the new linked server name from step 7.2 and click on tables. You'll get an error message here if something goes wrong.

See http://www.windowsitpro.com/SQLServer/Article/ArticleID/8992/8992.html for a great article on querying the linked server (page 4)

For a good explanation of data types (especially date and time) http://www-1.ibm.com/servers/eserver/iseries/access/3tier/datatypes.htm

Hope this helps!

Jeffrey_D_York
Starting Member

2 Posts

Posted - 2005-06-02 : 14:38:02

Thanks for that post! I had been batting around trying to connect to an iseries off and on for a week or so. Works great!!

sqlmaster
Starting Member

1 Post

Posted - 2011-01-12 : 02:47:20

We are creating an SSIS Package using SQL Server 2005. In Package we had successfully created a connection to AS400 server using iSeries IBMDA400 OLEDB Provider.
In SSIS we had only two components
1) OLEDB Source to retrieve data from AS400
2) OLEDB Destination to store data in SQL Server DB.

We are able to see records in compile time, But while executing it throws exception as follows:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.

We had search in google and found the below link as might be the solution.

http://www912.ibm.com/n_dir/nas4apar.NSF/c79815e083182fec862564c00079d117/a802b6eb29d32cac8625726c0041efc9?OpenDocument

We applied the PTF SI30707 and still the problem persists.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources