[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Insert and Update AS-400 Table, From SQL Server

Posted on 2005-05-02
8
Medium Priority
?
482 Views
Last Modified: 2012-05-05
I gather this is a common problem.  I setup an ODBC connector on my SQL Server machine, pointing to a library
on our AS-400.  I can import tables from the AS-400 to SQL Server.  I can execute the following SQL, to update
a table on the AS400:

update OLAIDCDATE
set OLDATE = 20050101



Unfortunately, however, when I try reading a value from a SQL Svr, then use it to update the AS400, I keep
getting an error that says there is no searchable column or something like that.  Searching via Google, I see
lots of people have run into this problem, but so far, have found no answers.

Anyone know how to update an AS400 table with data from SQL Server?
0
Comment
Question by:Danimal
  • 4
  • 3
8 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13912896
What about:
SELECT * from OPENQUERY(YourLinkedServer,'update OLAIDCDATE set OLDATE = 20050101')
0
 
LVL 1

Author Comment

by:Danimal
ID: 13913061
I don't have any linked servers.  I just made an ODBC connection to the AS400 from my SQL Server machine.  When I use that connection in DTS, then try firing SQL against it, I get the error.  It is as if the AS400/ODBC driver will allow me to read from the target table.. it allows me to update the table too, but not if the update value is read from a DB.
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 225 total points
ID: 13913113
It is more about ODBC driver than SQL Server. You run parametrised ODBC command and it is not able to identify a unique key.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Danimal
ID: 13913138
I dunno if my ODBC command is parameterized.  All I know is I can pull tables from AS400 into SQL Server, and update a record on the AS400 with literal values (strings in sql statements).

What I CANNOT DO:
Insert into AS400.MYLIBRARY.MYTABLE
Select * from SQLSERVER.MYDATABASE.MYTABLE


I can believe there is some tweakie flag I gotta flip on my ODBC connection to the AS400.
I also can believe that some flag in the OS400 has to be flipped to allow this external
access to its tables.

But I am in the dark on the fix.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13913287
I think table OLAIDCDATE doesn't have a unique key. ODBC tries to open a server side cursor and update data sequentially. But there isn't any unique key, so the updatable cursor is not open and an error occurs.
0
 
LVL 1

Author Comment

by:Danimal
ID: 13913317
We got the error attempting to insert into a table that definitely had a PK defined.  We were inserting only one row, so obviously no PK-conflict.

HAS ANYONE HEARD THIS IDEA?  
You have to make alias on db2/400 to link file member as a table then query against that table.

I got that from another site.

Gosh, it is crazy this is so hard.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13915103
Post your exact error....
0
 
LVL 1

Author Comment

by:Danimal
ID: 13929756
I ended up getting a new ODBC driver from HitSoft (http://www.hitsw.com/products_services/sql400/odbc400/odbc400.html).

The ODBC driver from IBM doesn't work (unless you pay $BM a lot of money I guess).
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

612 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