Transactional inserts to access dB from SQL stored procedure

• Kev Pickering

  SSC Enthusiast

  Points: 129

  More actions

  June 3, 2004 at 2:24 am

  #161450

  Hi,

  I can insert rows into an access mdb from within a SQL 2000 stored procedure using the OPENROWSET command no problem.

  INSERT INTO OPENROWSET(........

  SELECT FROM ............

  However, if I wrap the command with  BEGIN TRAN (also tried BEGIN DISTRIBUTED TRAN) and COMMIT TRAN, I get error message:-

  BEGIN TRAN

  INSERT INTO OPENROWSET(.........

  SELECT FROM ...............

  -- error checking omitted here for conciseness

  COMMIT TRAN

  Returned Message:-

  The requested operation could not be performed because the OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not support the required transaction interface.

  OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IUnknown::QueryInterface returned 0x80004002].

  Reason for using a stored procedure call to insert into an access mdb is legacy in that I have to write a method which utilises their existing routine of receiving data as an access snapshot file, with data residing in SQL server. This is only a small part of the procedure which has further processing before and after the insert to access mdb but where the whole is wrapped inside a transaction.

  Is it possible to have an insert to access mdb as part of a transaction within the stored procedure?

  Thanks for any assistance.

• June 7, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Kev Pickering

  SSC Enthusiast

  Points: 129

  More actions

  June 8, 2004 at 2:08 am

  #509402

  Gee,

  Got excited for a moment. Thought I'd got a reply.

  Any help is appreciated.