Delete using a linked server

Hi,

I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.

Thanks!On the properties page for the linked server, are the RPC and RPC OUT check boxes checked?

That said, I never tried manipulating data on a linked server; only selects. Also, there is a different driver for AS/400s, I' racking my brain for the name, but it's not provided by IBM.

Regards,

hmscott|||Thanks for the reply.

I tried checking the RPC and RPC out boxes and I get the same error message.

As for the other driver, you're probably thinking of OLE DB provider for DB2.
I've have also tried using this driver without success.

My hunch is that there is an ODBC provider property(such as DBPROPSET_PROVIDERROWSET) that's not configured to handle updates or deletes, but I don't know how to display this information.

Are you familiar with OLE DB providers for ODBC or can you direct me to a
resource?|||Can you post the delete and update statements.|||Here's the statement I'm using in query analyzer.

DELETE FROM AS400LINKEDSERVER.SYSTEMNAME.LIBRARYNAME.FILENAME

The four part names have been changed here for privacy.

I have also tried OPENQUERY. Again, same error message.

Thanks.