Tips From The Field: Solving A SQL Server Connectivity Issue With An AS/400 Linked Server
In our first Tip From The Field, I’m sharing something completely off topic for the site, but that I hope will help someone else avoid an issue that plagued me for a while. Here’s the skinny: I’m working at a client site and need to work with both Microsoft SQL Server 2012 as well as DB2 on an IBM AS/400. Microsoft makes that pretty easy through the use of Linked Servers in SQL Server. In order to move forward, we simply installed to the SQL Server the IBM iSeries data access tools, which installed the drivers necessary to create a linked server to the AS/400.
For my purposes, I used the IBMDA400 OLEDB driver that was installed along with the iSeries data access tools and successfully created a linked server. To make sure it was working, I browsed the DB2 data store from the SQL Server Management Studio.
Of course, the purpose of the linked server is to run queries, so I wrote a simple query to make sure that I really had access to the data. At first, everything appeared to be working well, but I was then faced with the following error message:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
Moreover, every time I ran a query against the linked server, the MSSQLSERVER service crashed. As you can imagine, this is not a desirable situation.
I spent quite a bit of quality time with Google and there were lots of people out there facing the same situation. This error seems to imply that there is a network connectivity issue somewhere along the path, but I quickly ruled this out because there are other systems at the client site connecting just fine.
I’m going to cut right to the chase. Here’s – for me –the eventual resolution: When I created the linked server, I failed to configure one of the critical parameters on the IBMDA400 driver.
If you’re having the same issue, the fix is easy. Open up the SQL Server Management Studio and browse to your Linked Server providers, as shown in Figure 1. Right-click the IBMDA400 driver (or whatever driver you’re using) and choose Properties.
On the Properties page, select the checkbox next to Allow Inprocess and click OK.
For me, this solved my issue and I no longer ran into issues running even complex queries against DB2 databases. It seems that this particular driver doesn’t allow execution out of process. The upside to running queries in process is that I’m able to access all data types on the DB2 server. If the driver supported running out of process, I wouldn’t be able to access certain data types, although that wouldn’t be the end of the world for this project.