Tag Archives: AS400

SQL- Creating linked server to AS400

Posted on by
10

Have you ever tried to connect to AS400. It’s been a hassle for me. But finally I got it to work, so let me share little of my wisdom with you.

First create linked server, then change options to it.

EXEC master.dbo.sp_addlinkedserver @server = N’AS400′, @srvproduct=N’i520′, @provider=N’IBMDA400′, @datasrc=N’AS400′, @provstr=N’User Id=USER;Password=PWD;Default Collection=DAT;’

Change option:

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’NonTransactedUpdates’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’IBMDA400′, N’SqlServerLIKE’, 1
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’collation compatible’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ASUHRP12′, @optname=N’rpc out’, @optvalue=N’true’
GO

So, you should now have functional linked server. Only, in my case its very slow, so it could be in your case to.

Good Luck

The Love between IBM iSeries Access and SQL 2012

Posted on by
Reply

Ok, so let’s start with my first blog.
Couple of days ago, I’ve been given a task to migrate some tables from AS400 to SQL.
So first of all, you would need a connector (provider) to IBM AS400 structure. So that connector comes with IBM iSeries Access software. After quick and “select All option” installation, I thought this is it.
I started SQL Import and Export Wizard to test things out. But the error appeared:
image

After couple of minutes, I found that the .NET config file had been corrupted. Searching through .NET config, I found the problem. In file “C:WindowsMicrosoft.NETFrameworkv4.0.30319Configmachine.config” there was a unclosed line
<system.data>
<DbProviderFactories>
<add name=”IBM DB2 for i5/OS .NET Provider” invariant=”IBM.Data.DB2.iSeries” description=”.NET Framework Data Provider for i5/OS” type=”IBM.Data.DB2.iSeries.iDB2Factory, IBM.Data.DB2.iSeries, Version=12.0.0.0, Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26″ />
</DbProviderFactories>
   <DbProviderFactories/>  —-delete  this line
</system.data>
Of course you’ll probably have problems of saving that protected file. So, my advice, save that file on desktop, edit it, and then copy it back to original location.

After that, the Import Export worked as a charm. Now, next step is to create a DTX package, and schedule it to transfer data. SQL 2012 does not have Business Intelligence, but SQL Server Data Tools (further in text SSDT).

Start SSDT, create a project, create Control Flow, add Connection Managers to SQL server and to AS400.

But, I’ve stumbled on a problem here.
When working and testing DTX package from SSDT, everything works, but for some reason when you import that package into Integration Services, and start it, the password for IBM AS400 connector is missing.
Solution to this problem is:

Start SSDT with “Run as Administrator” option, design package, enter username and password into AS400 connection Manager , and select “Allow saving password”.
image
Save package. When importing to Integration Services you must select option “Rely on server storage and roles for access control”!

image

And that about covers the problem I’ve encountered working with these two products.

Enjoy migrating!
Smile