SQL- Creating linked server to AS400

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

About: admin


10 thoughts on “SQL- Creating linked server to AS400”

  1. Thanks for the info. I was really struggling getting linked server working on SQL 2012 SP1. BTW, If you change your provider to IBMDASQL and add Block Size=8192 to the provider string you will see much improved performance

    1. I’m glad I helped. I’ll try with block size. Does this means it is Data block size?

  2. I like the helpful information you supply on your articles.
    I will bookmark your blog and take a look at again here frequently.
    I’m reasonably sure I will learn many new stuff proper here! Best of luck for the following!

  3. It is in reality a nice and useful piece of info.
    I am glad that you shared this helpful information with us.
    Please keep us up to date like this. Thanks for sharing.

  4. I waznt to to thank you for this veryy good read!! I absoluutely enjoyed every bit of it.
    I have you saved as a favorite to check out new things you post…

    Here is mmy blog … интернет магазин ua
    (Dusty)

  5. Please let me know if you’re looking for a author for your site.
    You have some really great articles and I believe I would be
    a good asset. If you ever want to take some of the load off,
    I’d really like to write some articles for your blog in exchange for
    a link back to mine. Please send me an e-mail if interested.
    Thank you!

  6. Hmmm it looks like your bkog ate mmy first commenjt (it was extremely long) so I guess I’ll just sum
    itt up what I wrote and say, I’m thoroughly enjoying your blog.
    I as well amm an aspiring blog writer but I’m still new to the whole
    thing. Do you have aany points for beginner blog writers?
    I’d genuinely appreciate it.

    Also visit my web-site – мегафон безлимитный интернет (Ramon)

Leave a Reply to Ramon Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.