Delete statement run on query analyzer getting errors

• dchristensen-746468

  SSC Veteran

  Points: 252

  More actions

  May 14, 2008 at 10:51 am

  #71950

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738170

  More actions

  May 14, 2008 at 11:03 am

  #815404

  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.

• dchristensen-746468

  SSC Veteran

  Points: 252

  More actions

  May 14, 2008 at 11:10 am

  #815415

  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?

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  May 14, 2008 at 11:42 am

  #815447

  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?