Entity Framework and Linked Servers

Question

I have an access database which is a linked server in SQL Server. For each table in the access database I have created a view which is mapped to my entites. Querying works perfectly, but inserting results in the following error:
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" does not support the required transaction interface.

I understand that this because of the distributed transactions. But is there any possibility to get this working?

Wolfgang Roessler

Answer 1

Probably not.

The only thing I can think of is to write CLR instead of insert / update and delete triggers for the view and write the code to update the Access Database manually in C# or something

Probably not a great solution because you would be necessarily opting out of transaction support etc.

But you might want to consider giving this a try?

Hope this helps
Alex

---

Alex James (Microsoft)

Answer 2

Whenever you access a linked table in SQL Server it promotes your transaction to a distributed transaction.

It then required DTS (Distributed Transaction Server) service to process the distributed transaction.

First, check that the service DTS is installed and running.

Next, the DTS expects a ODBC driver instead of a OLE DB driver.

I'm tracking down how to configure the OLE DB driver to work with DTS, but its slow going.

If you find out how to do it, let me know.