All geek questions in one place

Failed to start distributed transaction: Linked Server & # 8594; Access database

Specifications:

  • Sql Server 2008
  • Server 2008 R2 64bit
  • MS Access Database

What works:

  • I configured the connection of the linked server to the Access database, installing the office access data components and creating a system DSN and much more. which is located in a directory on the same server.

  • I managed to query the data through openquery.

    SELECT * FROM OPENQUERY (LINKEDHELL, 'SELECT * FROM [D: \ path \ mordor.mdb] .Orcs')

  • I can insert / update / delete through openquery (from inside Sql Management Studio)

What does not work:

I created a database that provides a view that wraps an openquery expression. This allows my applications to remember that it is requesting a connection to a connected server and allows me to use ORM. Example: Entity Framework.

However, data extraction is updated / pasted / deleted. I spent quite a bit of time making sure that MSDTC is configured correctly, but not a cube.

What i tried

MSDTC

  • DTC Network Access
  • Allow Inbox / Outbox
  • Configurable firewall
  • XA Transactions Included
  • The account under which the DTC works with access to .mdb
  • Rebooted Sql server after completing these settings

Other

  • Configured related server protection: A mapping is created between the SQL user and the local administrator account, so when I log in with this sql user, it uses the local administrators account to connect to the access database.
  • Enable Distributed Transaction Promotion for RPC: False

Error receiving:

OLE DB provider "MSDASQL" for linked server "LINKEDHELL" returned message "[Microsoft][ODBC Microsoft Access Driver]Invalid attribute/option identifier ". Msg 7391, Level 16, State 2, Line 4 The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDHELL" was unable to begin a distributed transaction.

I can just recreate it by doing this from Sql Management Studio

 begin distributed transaction //anything that queries the linked server commit transaction

So what did I miss? I read that distributed transactions are supported by the ODBC driver, but I'm not sure if the ACCESS database. Therefore, if someone can confirm this. This will help.

+6
source share
1 answer

Access has no transactions because Access is not a database engine. Jet / ACE (db engine is available by default) commit / rollback while I used it (since Jet 2.x, for example, 1996). It never supported transaction logging and probably never will (I admit that it’s not what you are asking for, but many of those who come for Jet / ACE from the background data of the server database have a rather vague sense of the term “transactions” and with difficulty grabbing Jet / ACE supports one, not the other). - David-In-Fenton Jan 16 '10 at 23:12

- Do we have transactions in MS-Access?

David W Fenton, MVP, RIP

+1
source

Source: https://habr.com/ru/post/920945/

More articles:


All Articles