Trying to update an Iseries (as400) from SQL Server.

dibanezb
dibanezb used Ask the Experts™
on
Trying to update an Iseries (as400) from SQL Server.

If I type at SQL Management Studio:
   update OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR WHERE UUAKNB=71162') SET UUGGNB=104
I receive this error:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "KRICOF8K" could not UPDATE table "[MSDASQL]".

Typing:
 UPDATE KRICOF8K.S10AF99T.AMMOBJ78KF.PFUUIDR SET UUGGNB=104 WHERE UUAKNB=71162
I receive:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "UPDATE "S10AF99T"."AMMOBJ78KF"."PFUUIDR" set "UUGGNB" = (104.)  WHERE "UUAKNB"=(71162.)" against OLE DB provider "MSDASQL" for linked server "KRICOF8K".

Of course, my query is more complex than that two examples, but I can't update my linked server with this two simple queries.

KRICOF8K es the linked server name and Works fine when I select rows, for example:
SELECT * FROM OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR  WHERE UUAKNB=71162')
display a row with several columns with values.

What is the correct syntax and why am I receiveing that error?
Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gary PattersonVP Technology / Senior Consultant

Commented:
The most common cause is trying to update an IBM i db2 table that isnt journaled, with an isolation level other than *NONE.

Change the Commit Mode on the ODBC data source to *NONE.  If you need a different commit mode than *NONE, you will have to journal the table on the IBM i.

Author

Commented:
Thanks for your answers Gary, but problema persists. By the way, if I run a .NET program outside SQL Server, I am able to update data as I want but I would like to do that inside SQL Server.
Any other idea?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

VP Technology / Senior Consultant
Commented:
SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".

Well, SQL7008 is an IBM i DB2 error message.  IBM i DB2 is telling us that the table PFUUIDR in library AMMOBJ78KF is not valid for the operation (assume that means the UPDATE).  Might help to see the low level message text for SQL7008.  It contains a reason code that may help identify the specific cause.

Also, what provider are you using in your Linked Server in SQL Server?  Looks like you're using an OLEDB-ODBC bridge driver to connect your linked server to the IBM i ODBC driver.  Probably better to eliminate this extra layer and use the IBM-provided OLEdb driver directly:

http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514
Gary PattersonVP Technology / Senior Consultant

Commented:
Did switching drivers resolve the problem?

Author

Commented:
Gary:
Switching drivers and selecting "allow in process" in some section. I don't have Access to the server at this time to see what section is.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial