DB2 Update problem

Question

DB2 Update problem

Pack_Star

Mr or Mrs. 500

Points: 547
More actions
March 24, 2009 at 6:29 am

#136028

Dear All,
Can somebody shed some light on my problem.
I am having problems updating a single column in an SS25K5 linked AS400 Table from a single column in a local SS2K5 Table.
The MS SQL Source column is GoodsIn.dbo.POIN
The AS400 destination update column is MOVEX.MVXBDTA200.MPLIND.ICSUDO
When I run the following SELECT in query analyzer:-
SELECT DISTINCT A.ICSUDO, A.ICPUNO, A.ICPNLI,A.ICPNLX
FROM MOVEX.DANIELS.MVXBDTA200.MPLIND A
INNER JOIN GoodsIn.dbo.POIN B
ON A.ICPUNO = B.PUNO
WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = B.PUNO
ORDER BY A.ICPUNO, A.ICPNLX
it returns all records:-
When I run this UPDATE in the query analyzer:-
UPDATE A.ICSUDO
SET A.ICSUDO = B.DELNO
FROM MOVEX.MVXBDTA200.MPLIND A
INNER JOIN GoodsIn.dbo.POIN B
ON A.ICPUNO = B.PUNO
WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = '2218144' AND A.ICPNLI = '10'
It fails with the following message:-
Msg 208, Level 16, State 1, Line 1
Invalid object name 'MOVEX.MVXBDTA200.MPLIND'.
Does anyone have any ideas as to what I am doing wrong? Linked Server Name is MOVEX & Default Library in DSN is MVXBDTA200.
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Maxim Picard SSCrazy Points: 2052 More actions · Answer 1

nigel.fairbairn (3/24/2009)
SELECT DISTINCT A.ICSUDO, A.ICPUNO, A.ICPNLI,A.ICPNLX
FROM MOVEX.DANIELS.MVXBDTA200.MPLIND A
INNER JOIN GoodsIn.dbo.POIN B
ON A.ICPUNO = B.PUNO
WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = B.PUNO
ORDER BY A.ICPUNO, A.ICPNLX
UPDATE A.ICSUDO
SET A.ICSUDO = B.DELNO
FROM MOVEX.MVXBDTA200.MPLIND A
INNER JOIN GoodsIn.dbo.POIN B
ON A.ICPUNO = B.PUNO
WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = '2218144' AND A.ICPNLI = '10'

Hi,

In your select statement you have:

MOVEX.DANIELS.MVXBDTA200.MPLIND

I assume server.database.user.table

In your update you have:

MOVEX.MVXBDTA200.MPLIND

You have left out the database.

Hope this helps.

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 2

Hi Maxim,

Thanks for finding the typo. I am now using the following statement:-

UPDATE A.ICSUDO

SET A.ICSUDO = B.DELNO

FROM MOVEX.DANIELS.MVXBDTA200.MPLIND A

INNER JOIN GoodsIn.dbo.POIN B

ON A.ICPUNO = B.PUNO

WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = '2218144' AND A.ICPNLI = '10'

and getting the following error:-

Msg 208, Level 16, State 1, Line 1

Invalid object name 'A.ICSUDO'.

This time I have checked for typo errors & I can't find any?

Regards

Maxim Picard SSCrazy Points: 2052 More actions · Answer 3

nigel.fairbairn (3/24/2009)
UPDATE A.ICSUDO
SET A.ICSUDO = B.DELNO
FROM MOVEX.DANIELS.MVXBDTA200.MPLIND A
INNER JOIN GoodsIn.dbo.POIN B
ON A.ICPUNO = B.PUNO
WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = '2218144' AND A.ICPNLI = '10'

Hi Nigel,

I am sorry I saw the missing database and totally missed the error in the update.

The syntax for the update is

UPDATE TABLE

SET COLUMN = VALUE

Since you have already aliased your table in the FROM clause this should work for you.

UPDATE A

SET A.ICSUDO = B.DELNO

FROM MOVEX.DANIELS.MVXBDTA200.MPLIND A

INNER JOIN GoodsIn.dbo.POIN B

ON A.ICPUNO = B.PUNO

WHERE A.ICWHLO = 'CLO' AND A.ICPUNO = '2218144' AND A.ICPNLI = '10'

Maxim

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 4

Hi Maxim,

Thankyou for your assistance. I corrected the syntax and was able to update the single record.

My final statement is to updaye as follows:-

UPDATE Target

SET Target.ICSUDO = Source.DELNO

FROM Openquery (MOVEX, 'SELECT * FROM DANIELS.MVXBDTA200.MPLIND') Target

JOIN GoodsIn.dbo.POIN Source

ON Target.ICPUNO = Source.PUNO

WHERE Target.ICWHLO = 'CLO' AND Target.ICPUNO = Source.PUNO AND TARGET.ICPNLI = Source.PNLI

I now have the following error message:-

OLE DB provider "MSDASQL" for linked server "MOVEX" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - MPLIND in MVXBDTA200 not valid for operation.".

Msg 7343, Level 16, State 4, Line 1

The OLE DB provider "MSDASQL" for linked server "MOVEX" could not UPDATE table "[MSDASQL]".

I have done some research & found that there are alot of others with the same issues when connecting to DB2.

http://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/31821151773d0613862569b200550644?OpenDocument

Have tried using "Autocommit On" and changing the "Commit mode" in the DSN. Still no joy.

I have also looked at trying alternatives i.e SSIS but the "Microsoft OLE DB Provider for DB2" driver is not compatible with SQL Server 2005 STD Edition.

Running out of ideas!

Regards

Maxim Picard SSCrazy Points: 2052 More actions · Answer 5

Hi Nigel

I dont have much experience with DB2, I was curious when I checked in on this thread and it ended up being a TSQL syntax problem. I cannot help you with the DB2 driver but please update this thread if you do find something.

Désolé 🙁

Maxim