Delete statement run on query analyzer getting errors

  • I am trying to delete (clear) a table on AS400 in query analyzer. delete from FHLB8.FHLB8.DATAWARE2.MPFFUND

    The table on the AS400 is journaled and has keys. The linked server is FHLB8.

    After run the delete statement a recieve the following message:

    Server: Msg 7345, Level 16, State 1, Line 1

    OLE DB provider 'MSDASQL' could not delete from table 'SELECT * FROM DATAWARE2.MPFFUND'. 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 am not sure if the problem is ODBC connection or the DB2 code. I can delete from WinSQL with no problem.

    Thanks for any help, Dave

  • I bet it's the driver. This is through a linked server, correct? There are restrictions and I've seen issues with the drivers to DB2. You might try rebooting. We used to have driver memory leaks that would cause strange issues.

  • Yes, I am using a linked server. I rebooted yesterday. This error occurs on the test and production servers. The delete works in the DTS package that I am converting to a stored procedure.

    Any other ideas?

  • That message usually appears when no primary key is present or isn't detected (used to happen with Access a lot when you didn't "introduce" it to the SQL server primary key).

    If it works fine from the native interface - perhaps take a look at how to do it using an OPENQUERY statement (which should pass it on to DB2 to do the deletion).

    The syntax from BOL looks like this:

    DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 4 posts - 1 through 4 (of 4 total)

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