Danimal
asked on
Insert and Update AS-400 Table, From SQL Server
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.MYTAB
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.
What I CANNOT DO:
Insert into AS400.MYLIBRARY.MYTABLE
Select * from SQLSERVER.MYDATABASE.MYTAB
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.
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.
ASKER
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.
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.
Post your exact error....
ASKER
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).
The ODBC driver from IBM doesn't work (unless you pay $BM a lot of money I guess).
SELECT * from OPENQUERY(YourLinkedServer