Distributed transaction error

We have a server, for various reasons has link to a loop back linked server.
We have dozens of stored procedures that refer to the linked server in their
code. They have worked fine for several years until a developer added an on
update/insert trigger to a table. Now, when these stored procedures execute
,
there is a distritbuted transaction error. Distributed Transaction
Coordinator is verified as being on.
Does anyone know of a way to fix this or what is going on?
Thank you.For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
option XACT_ABORT should also be on to support modifications in a
distributed transaction.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Distributed transactions are not supported on loopback linked servers! If
itrigger is requred on a table and references another database on the same
server, hardcode the full 3 part name, ommiting server name.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Thanks for the advice, however, we have tested it with XACT_ABORT set to ON.
One of our database guru's mentioned:
--start quote--
“SQL Server still sees the all the involved statements as a single
transaction and because it have a call to the linked server, it tries to
initiates it as distributed transaction.”
I read in the sql server docs that whenever two databases are involved, sql
server treats the transaction as a distributed transaction even if the
databases are on the same box
--end quote--
Example: our stored procedure has a query similar to the following:
sql1 is the loop back linked server, the database resides ON the same box,
however, for various reasons we have left the sp to reference it as a linked
server.
update mytable
set mytable.column1 = 1
where NOT EXISTS
(select id from sql1.dbo.mytable2 p where p.column = mytable.column)
The following error message is generated whenever the trigger on
update/insert is active on the table mytable. We remove the trigger and the
SP works as always:
error msg: The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
Thanks for any help you can provide.
"Itzik Ben-Gan" wrote:

> For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
> option XACT_ABORT should also be on to support modifications in a
> distributed transaction.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||I appreciate your response Farmer,
however, the trigger itself has no reference at all to the loopback linked
server. It is trying to update a simple field in a table on the same server
as the trigger. The error happens in the SP, which has worked for several
years. If you remove the trigger, the SP works again.
"Farmer" wrote:

> Distributed transactions are not supported on loopback linked servers! If
> itrigger is requred on a table and references another database on the same
> server, hardcode the full 3 part name, ommiting server name.
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||Any data modification statements BEGIN an implisit transactions, that is why
selects worked and update fails now. Because it is involving linked server,
it is considered distributed.
BOL
Starting Transactions
You can start transactions in Microsoft SQL ServerT as explicit,
autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL
statement is committed when it completes. You do not have to specify any
statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the
Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement
automatically starts a new transaction. When that transaction is completed,
the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection
changes from one transaction mode to another it has no effect on the
transaction modes of any other connection.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:A8695506-E70A-4412-8417-97E91699C2A0@.microsoft.com...
> Thanks for the advice, however, we have tested it with XACT_ABORT set to
> ON.
> One of our database guru's mentioned:
> --start quote--
> "SQL Server still sees the all the involved statements as a single
> transaction and because it have a call to the linked server, it tries to
> initiates it as distributed transaction."
> I read in the sql server docs that whenever two databases are involved,
> sql
> server treats the transaction as a distributed transaction even if the
> databases are on the same box
> --end quote--
> Example: our stored procedure has a query similar to the following:
> sql1 is the loop back linked server, the database resides ON the same box,
> however, for various reasons we have left the sp to reference it as a
> linked
> server.
> update mytable
> set mytable.column1 = 1
> where NOT EXISTS
> (select id from sql1.dbo.mytable2 p where p.column = mytable.column)
> The following error message is generated whenever the trigger on
> update/insert is active on the table mytable. We remove the trigger and
> the
> SP works as always:
> error msg: The operation could not be performed because the OLE DB
> provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
>
> Thanks for any help you can provide.
>
> "Itzik Ben-Gan" wrote:
>