Connect SQL Server to AS400 with a Linked Server

18 July,2018 by Jack Vamvas

Question: I need to set up a Linked Server between SQL Server 2016 and AS400 to extract data from a file and import the data onto a table on a SQL Server database.

How do I set up the Linked Server?

Answer:Setting up a Linked Server from SQL Server to AS400 can be tricky , as there a number of variables. But the method I've outlined below has repeatedly worked for me.

Thy trickiest part - and normally most fails occur is in the Provider String . the details below have worked for me

1)Step 1 : Obtain the Microsoft OLE DB Provider for DB2 . You'll need this installed before you attempt to set up the Linked Server. To get the Microsoft OLE DB Provider for DB2   , you'll need to extract from the SQL Server 2016 feature pack. To get the feature pack , go to :https://www.microsoft.com/en-us/download/details.aspx?id=52676

2)Step 2: Obtain some details about the target AS400 server. You will need to work with your AS400 DB2 administrator to get this information. Once you have the details , use them to configure the code in the code on Step 3

Server name

Password

User name 

DB2Database 

Schema

Library

3)Step 3: Set up Linked Server

 

EXEC sp_addlinkedserver
@server='[Add_LinkedServerName]'
@srvproduct='Microsoft OLE DB Provider for DB2',
@catalog='[Add_DB2DatabaseName],
@provider='DB2OLEDB',
@provstr='NetLib=TCPIP;NetAddr=[Add_Server_Name];NetPort=[Add-Port];RemoteLU=[Add_DB2DatabaseName];LocalLU=LOCAL;ModeName=QPCSUPP;InitCat=[Add_DB2DatabaseName];Default Schema=[Add_Library];PkgCol=[Add_Library];Commit=YES;IsoLvl=NC;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=[Add_DB2DatabaseName]_[Add_Library]'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'[Add_LinkedServerName]',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'[Add_USERNAME]',
@rmtpassword='[Add_PASSWORD]'

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Connect SQL Server to AS400 with a Linked Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer