Adding LinkedServer to AS400/DB2

I had trouble getting the OLEDB Provider from IBM Client Access to work. It was pulling data, but due to CCSID issues, many columns didn’t translate properly. To resolve this, I had to use a T-SQL query so I could set Force Translate=0, when adding the LinkedServer.

EXEC master.dbo.sp_dropserver @server = N’MyLinkedServer

EXEC master.dbo.sp_addlinkedserver @server = N’MyLinkedServer‘, @srvproduct=N’AS400′, @provider=N’IBMDASQL’, @datasrc=N’N’MyAS400Name‘,
@provstr=N’User ID=<user>;Password=<password>;Transport Product=Client Access;SSL=DEFAULT;Force Translate=0;Default Collection=zzzzzzzz;Convert Date Time To Char=TRUE;Catalog Library List=””;Use SQL Packages=False;SQL Package Library Name=””;SQL Package Name=””;Add Statements To SQL Package=True;Unusable SQL Package Action=1;Block Fetch=True;Data Compression=True;Sort Sequence=0;Sort Language ID=””;Query Options File Library=””;Trace=0;Hex Parser Option=1;Maximum Decimal Precision=31;Maximum Decimal Scale=31;Minimum Divide Scale=0′

Note: I am using Client Access 7.1

Leave a Reply

Your email address will not be published. Required fields are marked *