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.