Solved

Sql Server and iSeries linked server

Posted on 2012-03-26
7
601 Views
Last Modified: 2012-04-02
I tried this a few years back and was very frustrated, but I'm going to try again.  I have a server 2003 box (clean, all updates)  with client access 6.0 installed.  Our AS400 runs 7.1.  My question is can I do this with sql server 2012 express or should I stick with 2008 developer edition.  I'm just doing this for testing to see if I can get it to work.  Also, with a linked server, is it read only or will I be able to update the tables on the 400?  Thanks
0
Comment
Question by:bergertime
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 200 total points
ID: 37767165
Talk about good timing.  :)

There was a discussion just a few days ago about connecting SQL Server to DB2 on an AS400.

Take a look at the thread below, particularly the last entry (by the question's author).  He found a great article that got him right to the solution.

  http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27627456.html

And when you link to DB2, you should have read/write access from SQL Server.


Good Luck,
Kent
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 200 total points
ID: 37768131
...when you link to DB2, you should have read/write access from SQL Server.

I'd rephrase that slightly -- you should be able to create a connection that has read/write access. There can be requirements that must be met before write access is enabled. That is, you won't necessarily have it just because a connection works.

However, once a working connection exists, any seeming obstacles for write access become connection configuration or server setup elements that can be handled.

Tom
0
 
LVL 16

Assisted Solution

by:theo kouwenhoven
theo kouwenhoven earned 100 total points
ID: 37774138
Hi Kent, Tom,

Sme questions ago, I had problems with updating of DB2 tables and The solution was that you need to activate Journaling on the 400 if you like update or insert records in DB2 tables?
Isn't this the same issue?

Regards,
Murph
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 27

Expert Comment

by:tliotta
ID: 37784422
Enabling journals should be the preferred resolution. However, I'm pretty sure I've seen connection options that bypass that. I try always to have it set up, so I'm not familiar with what to do when it isn't.

Different clients, different releases, different options, different servers... all can affect exactly which buttons or menu options to click or which connection string parameters to use or...?

For the AS/400 line, this usually comes up when the "tables" are really older style DDS physical or logical files rather than actual SQL objects in a SQL schema. Without knowing details, guesses are tricky.

Tom
0
 
LVL 2

Author Comment

by:bergertime
ID: 37796886
Thanks, I'll award points today, but I do need one more thing made clear.  I've got it linked, can I see the tables from Visual Studio?  Can I use the query designer in Mgmt Studio?  All I've been able to do is open the linked the linked server, I see the tables, then run a Script table as.  I'm just not sure what a linked table offers me.  I was hoping to be able to write my programs against the SQL server since I can't always install client access on all the machines that need to query data from our 400.  Thanks again for all the insight.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37797165
The tables are linked to the database (or the server, if that visualization is easier for you).

Visual Studio creates a connection to the database (instance, etc.) an interacts with the objects that are accessible in that database (schema).  If the objects are properly linked to the database that you connect to, you should be able to access them from Visual Studio.



Kent
0
 
LVL 2

Author Closing Comment

by:bergertime
ID: 37797225
Thanks.  I'm going to post a follow up question.  Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question