Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    US - Boston
    Posts
    3

    Question Unanswered: Writing to Access 97 from SQL Server 2000

    Hi,

    I am using SQL Server 2000 and I am trying to do inserts and updates into an Access 97 database.

    I linked the Access db to SQL and the link works fine, I can do selects from SQL on the Access tables.

    However, everytime I try an insert or update into an Access table I am getting:

    Server: Msg 8524, Level 16, State 1, Procedure UpdateStoreListingsAccess, Line 19
    The current transaction could not be exported to the remote provider.
    It has been rolled back.

    Has anybody encountered this problem before?

    Thanks a lot,
    Bogdan

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I just tested this out on SQL Server 2000 and SQL Server 7.0, works fine on both. However it fails on both if you do the INSERT within a transaction or have implicit_transactions on. Also I defined my Link Server to MSAccess with the OLE DB Provider for Jet, I believe you could use OLE DB Provider for ODBC Drivers.

    Technet Q221071

    The provider must support ITransactionJoin to work with MTS or distributed queries, Jet 4.0 does not, some ODBC drivers do, which I haven't gone into to investigate which ones.

  3. #3
    Join Date
    Sep 2002
    Posts
    7

    Post The operation could not be performed because the OLE DB provider 'MSDASQL' was unable

    Hi!

    I have a SQL server 2000 where I created a linked server with the ODBC provider to an access 2000 database. then in the SQL server I associated an trigger to a table, which at any insert, inserts some data into the access table.

    But when the trigger is executed the following error is fired :
    "The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction."

    If instead of the ODBC provider I use a linked server through jet 4.0
    when the trigger is executed I get the following message:

    "The current transaction could not be exported to the remote provider. It has been rolled back."

Posting Permissions

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