Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005

    Unanswered: Delete using a linked server


    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:eleteRows 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.


  2. #2
    Join Date
    Dec 2002
    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.


    Have you hugged your backup today?

  3. #3
    Join Date
    Dec 2005

    Delete using a linked server

    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

  4. #4
    Join Date
    Sep 2003
    Can you post the delete and update statements.

  5. #5
    Join Date
    Dec 2005

    Delete using a linked server

    Here's the statement I'm using in query analyzer.


    The four part names have been changed here for privacy.

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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts