Linked Server and unable to begin a distributed transaction

I have a database containing my own tables and data and I wanted to
be able to query this against an accountancy program which has an ODBC
driver. This was never a problem with MS Access and Jet but I hit Jet's
limitations and have moved to SQL.
Creating my own SQL database was no problem, but I was unsure of the
best way to be able to be able to have my SQL tables and my accountancy
software tables appearing in the same Access front end.
I created a linked server to the accountancy program. This was
successful in that I could see all the tables below the linked server in
enterprise manager.
My problem occurs when I try to bring the data from these tables
into my SQL database.
I create a new view in my database:-

SELECT *
FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')

(My linked server is called 'SAGE_SERVER' and I am trying to retrieve
all columns from the STOCK table.)

I then try to save this view and get the following errors.

ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].

Thanks in advance,

Marcus Thornton."Marcus" <marcus@.automint.co.uk> wrote in message
news:MPG.19748ac86a8335b3989681@.192.168.1.50...
> I have a database containing my own tables and data and I wanted to
> be able to query this against an accountancy program which has an ODBC
> driver. This was never a problem with MS Access and Jet but I hit Jet's
> limitations and have moved to SQL.
> Creating my own SQL database was no problem, but I was unsure of the
> best way to be able to be able to have my SQL tables and my accountancy
> software tables appearing in the same Access front end.
> I created a linked server to the accountancy program. This was
> successful in that I could see all the tables below the linked server in
> enterprise manager.
> My problem occurs when I try to bring the data from these tables
> into my SQL database.
> I create a new view in my database:-
> SELECT *
> FROM OPENQUERY(SAGE_SERVER, 'SELECT * FROM STOCK')
> (My linked server is called 'SAGE_SERVER' and I am trying to retrieve
> all columns from the STOCK table.)
> I then try to save this view and get the following errors.
> ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
> Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].
> Thanks in advance,
> Marcus Thornton.

You might find this post (and the rest of the thread) useful - it has some
quite detailed information on troubleshooting distributed transaction
errors:

http://groups.google.com/groups?hl=...%40cpmsftngxa07

Simon