sql server odbc: 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 Hampson

If 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 Server, 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 ODBC 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 sequence. Very inefficent, but it gets the job done.
The reason for my error was 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 when 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.



--
RyanPicanco
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message120340.html