Problems executing DML via linked server

Hey folks..
I have a linked server that connects to a DB2 database through the OLE DB
for ODBC Providers driver. I have a DSN created and can, through the linked
server run SELECT statements to my heart's content. When I want to run a
simple DELETE, for example (DELETE HCEDB.APPLQUE2), I get the following
error.
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I have gone to a colleague's machine to use a DB2 Client and authenticated
using the same user specified in both the DSN and Linked Server and was able
to execute the DELETE. Any ideas as to cause and resolution?
Thanks..
Peace,
Gary HampsonIf you are using a DELETE FROM (Table Name), beware.
I was doing the same thing on a DB2 Database with some test data given to me
, and I would get the same error when I tried to delete using the Linked Ser
ver, where if I tried to do it straight in the DB2 Console, I could execute
without any problems.
So I ran an Event Monitor. Happens out, the Microsoft OLE DB Provider for OD
BC Drivers turns it into count(*) many "DELETE FROM TABLE WHERE field = ?".
So if your table has 1000 rows, you'll have 1000 Delete Statements run in se
quence. Very inefficent, but it gets the job done. The reason for my error w
as that there were 3 rows that were identical to each other, and so it had 3
identical "DELETE FROM" statements.
My solution: Deleted the replicated rows. It apparantly was an oversight whe
n creating the test data.
I still have an issue with it producing all those Delete Statements though.
I'm currently looking into other ways of doing it.