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

Linked server & transaction connection

Hi all,
I've a local server and a linked server, which I need to update both
servers' tables.
But I encountered the error "Can't start more transaction in a session'.
Following is the structure of the code in the program:
----
Begin Transaction
Insert local_server_A.table
Update linked_server_B.table
Commit Transaction
If Error
Rollback Transaction
I know the problem is the transaction control can only exist in one
connection.
The linked server is treated as another connection.
Is there any way to workaround for multi connection in a transaction control
?
Thanks in advance,
KristeHi
Read about distribution transactions in the BOL.
"whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> Hi all,
> I've a local server and a linked server, which I need to update both
> servers' tables.
> But I encountered the error "Can't start more transaction in a session'.
> Following is the structure of the code in the program:
> ----
> Begin Transaction
> Insert local_server_A.table
> Update linked_server_B.table
> Commit Transaction
> If Error
> Rollback Transaction
> I know the problem is the transaction control can only exist in one
> connection.
> The linked server is treated as another connection.
> Is there any way to workaround for multi connection in a transaction
control?
> Thanks in advance,
> Kriste|||Sorry, I read thr' BOL on MS DTC and replication etc. but I still can't find
solution how can this be applied just between linked servers. Can you provid
e
more info. on this?
"Uri Dimant" wrote:

> Hi
> Read about distribution transactions in the BOL.
>
> "whiteegg" <whiteegg@.discussions.microsoft.com> wrote in message
> news:AA6E6C4B-5C3F-476E-AACE-D8AE2EC86388@.microsoft.com...
> control?
>
>