Specs:
- Sql Server 2008
- Server 2008 R2 64bit
- MS Access database
What works:
- I have configured a Linked server connection to the Access database by installing office access data components and creating a system DSN and whatnot. which resides in a directory on the same server.
I have succeeded in quering data through openquery.
SELECT * FROM OPENQUERY(LINKEDHELL, 'SELECT * FROM [D:\path\mordor.mdb].Orcs')
I can insert/update/delete through openquery (from inside the Sql Management Studio)
What does not work:
I have created a database which exposes a view that wraps the openquery expression. This allows my applications to remain oblivious to the fact thats its quering through an linked server connection and allows me to use an ORM. Case in point: Entity Framework.
However whilst retrieving data works, updating/inserting/delete wont. I have spend quite some time making sure that the MSDTC is configured correctly however no dice.
What i tried
MSDTC
- Enabled Network DTC Access
- Allow Inbound/outbound
- Configured Firewall
- Enabled XA Transactions
- Gave account under which DTC works access to the .mdb
- Restarted Sql Server after making these settings
Other
- Configured linked server security: Created mapping between sql user and local admin account, so that when i login with that sql user, it uses the local administrators account to connect to the access database.
- Enable Promotion of Distributed Transactions for RPC : False
The error im getting:
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 simply recreate this by doing this from the Sql Management Studio
begin distributed transaction
//anything that queries the linked server
commit transaction
So what have I missed? I have read that distributed transactions are supported by the ODBC driver, but im unsure if the ACCESS database does. So if someone could at the very least confirm that. That would help.