March 24, 2009 at 6:29 am
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
March 24, 2009 at 7:43 am
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.
March 24, 2009 at 9:07 am
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
March 24, 2009 at 12:06 pm
nigel.fairbairn (3/24/2009)
UPDATE A.ICSUDOSET 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
March 26, 2009 at 7:27 am
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.
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
March 26, 2009 at 5:34 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply