April 15, 2003 at 7:44 am
I am trying to Delete records from JDE on AS400 using Linked servers.
I am using the following code:
DELETE FROM JDSPWPDTA.MWRA.JDSPWPDTA.F3460
WHERE MFUSER = 'FRCURFLOW' AND MFJOBN = 'FRCURFLOW' AND MFPID = 'FRCURFLOW'
and the error I am getting back is:
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"MWRA"."JDSPWPDTA"."F3460"'. Updating did not meet the schema requirements.
[OLE/DB provider returned message: Key column information is insufficient or incorrect. Too many rows were affected by update.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_SCHEMAVIOLATION].
I have the correct permissions on the AS400
Can anyone help??
PLEEEEAAASE
April 15, 2003 at 8:34 am
I suspect that the problem in on the AS400.
Two things come to mind:
1. Updating did not meet the schema requirements - could this be that the rows you are deleting are a foreign key for another table?
2. Too many rows were affected by update - is there something which limits the number of rows that can be affected by an update or delete query?
I think you should talk to your AS400 DBA and see what they have to say.
Jeremy
April 15, 2003 at 9:04 am
Thanks for your help Jeremy
However I have spoken to our AS400 DBA and he said that the table has no foreign keys and I have tried deleting only one record and I still get the same message.
April 15, 2003 at 9:20 am
Can you select those records with statement like
select * FROM JDSPWPDTA.MWRA.JDSPWPDTA.F3460
WHERE MFUSER = 'FRCURFLOW' AND MFJOBN = 'FRCURFLOW' AND MFPID = 'FRCURFLOW'
Do you setup linked server with Microsoft Host Integration Server? Can you try to delete record from another table with similar statement?
April 15, 2003 at 10:04 am
I CAN PERFORM THE SELECT AND IT COMES BACK WITH 17000 RECORDS. i HAVE RETRIED JUST DELETING ONE RECORD AND IT HAS WORKED i MUST OF DONE SOMTHING WRONG. IT STILL WILL NOT DELETE 17000 RECORDS BUT HAS DELETED 1500 RECORDS
i HAVE CHECKED WITH THE AS400 DBA AND HE HAS TOLD ME THAT THERE IS NO LIMIT ON THE AMOUNT OF RECORDS THAT YOU CAN DELETE. BUT ON THE AS400 IF YOU PERFORM A DELETE STATEMENT IT WILL DELETE 10,000 RECORDS AT A TIME. HOWEVER WILL DELETE ALL THE RECORDS SELECTED i AM THINKING THAT IT COULD BE SETTING ON THE DRIVER.
April 15, 2003 at 10:05 am
aLSO i HAVE ONLY SETUP LINKED SERVERS IN ENTERPRISE MANAGER NOTHING ELSE
April 16, 2003 at 9:53 am
The issue will be found in not having a unique identifier for the table. This unique identifier must exist in order to delete records properly.
Edited by - scorpion_66 on 04/16/2003 10:03:39 AM
April 17, 2003 at 5:20 am
Thanks Scorpion_66
I am looking into unique identifier's but it has deleted 1500 records but comes back with that error when I try to delete 17000 records
April 17, 2003 at 8:30 am
The reason that the 1500 were able to be deleted, was because there were no duplicates within those 1500. So long as that is the situation, there is no issue. Your problems come in when a duplicate record is included in the delete criteria's selection. This will be the case so long as a unique identifier is not declared.
An alternate way to go about it would be to use a passthrough query to handle the deletes. In that case, you would not have to have the unique identifier. You simply execute the query remotely.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply