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

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.
dibanezbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
Gary PattersonVP Technology / Senior Consultant Commented:
0
dibanezbAuthor 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?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Gary PattersonVP 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
Did switching drivers resolve the problem?
0
dibanezbAuthor 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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.