IBM Support

Configuring an OLE DB Provider on an SQL Server

Troubleshooting


Problem

This document shows the basic steps to configure a linked server by using the IBM i OLE DB data providers on an SQL server.

Resolving The Problem

To configure a linked server by using the IBM OLE DB data providers on an SQL server, do the following:
  1. Open the SQL Server Enterprise Manager(SQL Server 2000) or SQL Server Management Studio(SQL Server 2005).
  2. Under the Console Root, expand Microsoft SQL Servers > SQL Server Group > Local > Security > Linked Servers for SQL Server 2000.
    With later versions, expand Server Objects > Linked Servers.
  3. Right-click Linked Servers and select New Linked Server.
    Select New Linked Server from the pop-up menu.
  4. Give your new Linked Server a title in the Linked server: text box.
  5. For the Server type, select Other data source and select an IBM OLE DB data provider.
    To decide which one of our providers would be best to use, you can refer to the following table from the IBM i Access Programmer's Toolkit:
    Database Provider Framework Consideration
    IBMDA400 OLE DB Provider Existing applications, applications that want the most flexible OLE DB support, including support for SQL, RLA, Data Queues, and Commands.

    Note: IBMDA400 does not support SQL commitment control or MTS; it supports only SELECT statements.
    IBMDASQL OLE DB Provider SQL-only support, SQL commitment control, and MTS.
    IBMDARLA OLE DB Provider RLA-only support, forward-only cursors, and block fetches with RLA.

    Note: IBMDA400 does not support forward-only cursors or block fetches with RLA.
    IBM.Data.DB2.iSeries .NET Provider Offers the best performance when you are using the .NET framework.
    MSDASQL OLE DB Provider Microsoft's OLEDB-ODBC bridge, which uses our ODBC driver
    Note: Microsoft SQL Server is SQL based. The IBMDASQL OLE DB provider is the provider to be used with Microsoft SQL Server linked servers.
    In the following example, I am using the IBMDASQL provider:
    Select one of the IBM OLEDB Providers from the Provider list.
  6. Type your data source; the name of your IBM i system.
  7. Optional:
    Provider string. The Provider string can contain any valid connection properties the provider supports. Connection properties are separated by a semi-colon.
    Valid connection properties for the IBM i OLE DB provider can be found in the Programmer's Toolkit and in technote Access Client Solutions OLE DB Custom Connection Properties

    In the following example, the 'Default Collection' property is used. The 'default collection' is the default library that you connect to. If nothing is specified, the 'default collection' defaults to the name of the user profile making the connection. If you wanted to browse the objects in QIWS, MYLIB, and YOURLIB, set the provider string to:
    Default Collection=QIWS;Library List=MYLIB,YOURLIB;
    Catalog. In Microsoft's terminology, 'catalog' is the relational database name. Typically you do not need to specify any value in this field. If you are accessing the libraries in an iASP, use the iASP RDB name in this field; otherwise, leave it blank.
    Optional:  Add a Provider string of Default Collection=XXXX where XXXX is the default library you want to use.
    Note: For SQL Server 2005, the 'PRODUCT NAME' must have a value. It seems that almost any value works, aside from special characters. In the prior example, 'Db2 for i' is used.
  8. Click the Security tab.
  9. Select the 'Be made using this security context' radio button.
    Select 'Be made using this security context' and type your login credentials.
     
  10. Click OK. The linked server is displayed in the list.
  11. The OLE DB provider must be configured to allow inprocess. This step has to be done only once per provider but if it is not done, you get an error 7399 from SQL Server when you attempt to use the linked server. Under server Objects -> Linked Servers -> Providers right-click the provider being used (IBMDASQL, IBMDA400, or IBMDARLA) and select properties.
     Under server Objects -> Linked Servers -> Providers right click on the provider being used (IBMDASQL, IBMDA400, or IBMDARLA) and select properties.
    Make sure "Allow Inprocess" is checked.
    Check the option to enable <code>'Allow inprocess'</code>
  12. In SQL Server 2000, test the connection by expanding the linked server and double-clicking Tables. You now see the list of tables in the right pane.For SQL Server 2000, click on Tables, under your new Linked Server, and you will see a list of the tables in your default library.

Note: For SQL Server 2005, this test might not show a list of tables. This function seems dependent on what version of SQL Server 2005 is being used. For instance, this feature doesn't work with Express versions but seems to work with Developer or Enterprise editions.

In SQL Server 2005, you can test your linked server by running a simple query:
  1. Click New Query.
  2. Type the syntax of the query.
  3. Click Execute.

For SQL Server 2005, execute a select using OpenQuery to verify that your new Linked Server is functional.
Note: Some additional configuration steps are often needed to get this working with SQL Server 2005. Particularly when this linked server is the first OLEDB provider the SQL Server user has ever configuring.


If you get error "Msg 7399, Level 16, State 1, Line 1" type errors when you attempt to run the sample select statement, it is a problem with the SQL Server service and the rights of the user under which it is running. There are two things that can cause this error. Note these problems are all Microsoft code-related our advice is as-is at best. We recommend getting assistance with these issues from Microsoft service and support.
  • The SQL Server process is likely set to run under something other than the "Local System" account. You can check this setting in the SQL Server Configuration Manager tool (in the "Configuration Tools" folder in the start menu). This service might be set to run under the built-in "Network Service" account or some other account. Change it to the "Local System" account and let Windows restart the service.
  • In the SQL Server 2005 Surface Area Configuration tool (also in the "Configuration Tools" folder), select "Surface Area Configuration for Features". On the new panel, select OLE Automation and make sure the "Enable OLE Automation" option is checked. This change is no longer required from V6R1 System i Access and beyond; however, up through V5R4 iSeries Access, there are still some OLE automation objects involved.

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CY1AAM","label":"Data Access-\u003EOLE DB"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Historical Number

442197276

Document Information

Modified date:
02 December 2022

UID

nas8N1014514