In many IBM i shops that are developing Microsoft .NET applications, you’ll find one or more instances of Microsoft SQL Server in use. It is especially easy to start developing with SQL Server, given that Microsoft provides the no-charge Express Edition. If you want to explore all of the features of SQL Server, you can download a no-charge 180 day evaluation version of the Enterprise Edition.

No matter what edition you use, there is bound to be a requirement, at some point, to get data from your IBM i into SQL Server. Programmers who are familiar with .NET programming can easily come up with programs that use ODBC, OLE DB or .NET Providers to access both the IBM i and the SQL Server database.

An alternative to developing programs in Visual Basic or C# is to use the linked server feature of Microsoft SQL Server. Basically, a linked server is a persistent object that describes a connection to a database located on another server. Because the connection to the linked server is created using an OLE DB Provider, the other server can potentially be any type of data source.

In this article, you’ll see how to define and work with a linked server to your IBM i, using the System i Access for Windows OLE DB Provider. You'll see how you can use the linked server database from within the SQL Server Management Studio.

Define a linked server

To get started, open the SQL Server Management Studio. Once in the Management Studio, locate the Server Objects item and expand it. You can right-click either the Server Objects item (Figure 1) or right-click the Linked Servers item (Figure 2). Select the item to create a New Linked Server.

The New Linked Server dialog is displayed (Figure 3). Be sure that General is the selected page and that Other data source is selected.

Now comes the tricky part. To get the linked server to work, you need to specify the correct values for a few of the options on the General page. The trick is that the values that you specify are also dependent on the version of IBM’s System i Access for Windows program that you’re using. You need to know exactly what you have installed on your PC (or on the server where SQL Server is located), you can’t guess at this. You can find out what version of System i Access for Windows you’re using by going to the System i Access for Windows program group and running the System i Access for Windows Properties program. Right there, on the General tab of that program, you can clearly see which version you are running. More importantly, you can see which Service Pack you have installed and compare it against the currently available service pack from IBM. Get current with your Service Pack! A lot of fixes go into the service packs that directly affect the database access components. It is foolish to spend any time at all debugging database problems using ODBC, OLE DB or the .NET Provider if you don’t have the current service pack installed for your version.

Once you know what you have, you can use the information shown in Table 1 to help set values for the Linked Server’s General tab. When done, the General tab might look like Figure 3 (with values that are appropriate for your system, of course). Don’t click the OK button yet, you need to go to another page to finish the configuration of your linked server.

Click the Security item in the upper-left corner of the New Linked Server dialog. You’ll see the Local server login page as shown in Figure 4. Select the Be made using this security context item and enter your IBM i user ID in the Remote login text box and your IBM i password in the With password textbox.

Now you can click the OK button. The linked server is created.

You should now see the linked server that you defined in the list of Linked Servers, as shown in Figure 5. Expand the linked server, then expand the Catalogs item. You should see an entry for System Catalogs and another catalog name, which is the name of the local database entry on your IBM i. The catalog name that you see will not be the same as the catalog entry shown in the Figure (S105HMNM). It will be the name that was assigned to your *LOCAL database in the Work with Relational Database Directory Entry (WRKRDBDIRE) command that was run sometime in the distant past on your IBM i.

When you expand the catalog name, you will see entries for Tables and Views. You can expand both of those items to see the lists of database file objects that are accessible on your IBM i via the linked server definition.

• The Tables list includes physical files.
• The Views list includes logical files and SQL views.

The Tables and Views lists may include objects that you are not authorized to. When you attempt to access data in an unauthorized object, an error message is sent from the IBM i to the database provider that you used to create the linked server. You are not allowed to access data in IBM i objects that you are not authorized to, even if the object appears in the list of objects for the linked server.

Work with Tables in the Linked Server

Now that you can see tables that are on your IBM i in the linked server Tables list, you can work with those tables in SQL Server. For example, I created a test file ADVWORKS/QCUSTCDT on my IBM i, by copying file QIWS/QCUSTCDT to library ADVWORKS (just use CRTLIB to create the library). In the Tables list for the linked server, locate the ADVWORKS.QCUSTCDT item, right-click it and select the Script Table as, SELECT to, New Query Editor Window menu items, as shown in Figure 6.

SQL Server generates a SELECT statement for the table, as shown in Figure 7. Note that the SELECT statement that is generated includes only the columns in the table that contain character data. Also note that if you hover the mouse over the 4-part table name that the "invalid" message shown in the figure is displayed.

You can click the Execute button in the SQL Server Management Studio to run the SELECT statement. The results are displayed in the Results panel, as shown in Figure 8.

Populate a table in the SQL Server from a table on your IBM i

Now that you can access data on your IBM i, you can use that data to “automatically” create and fill a table in a SQL Server database. In this example, I created a SQL Server database named SQL400.

In the SQL Server Management Studio, click the New Query button to open a new query window and enter the following statements into the new query window:

use SQL400;
go

select cusnum, lstnam, init, street, city, state, zipcod
into SQL400.dbo.QCUST
from linked_server_name.catalog_name.ADVWORKS.QCUSTCDT;

where linked_server_name is the name of your linked server and catalog_name is the name of the catalog that is displayed under the linked server. After entering the statements, click the Execute button in SQL Server Management Studio to run the query. Now expand the SQL400 database and expand Tables. You should see the new table dbo.QCUST. Expand the dbo.QCUST table and expand Columns. You should see the list of columns in the table, with the same names that you specified on the SELECT statement.

Query the data in the SQL400.dbo.QCUST table

The data is there, too. Right-click the dbo.QCUST table and click the Select Top 1000 Rows item, as shown in Figure 9. SQL Server generates the SELECT statement shown in the query panel and runs the query, displaying the results in the Results panel.

Linked In

Now that you’ve seen the basics of how to define a linked server, you can continue to work with the data from your IBM i. In a future article, I’ll show some techniques you can use to transfer data from SQL Server back over to your IBM i, again using the linked server.