Solved

unable to begin a distributed transaction

Posted on 2008-10-02
6
2,125 Views
Last Modified: 2012-05-05
I get this error in SQL Server 2005 Express:

 unable to begin a distributed transaction

SW-05 is a linked server (2003 Server, SQL Server 2005 Standard)  that is accessible when running queries in the query window. It's when the code is run within triggers and stored procedures that I get this error. Locally, I am running XP Pro with SQL Server 2005 Express.

Here is what I have set so far:

I have enabled the MSDTC service on both computers and set them to 'No authorization Required', logging in as NT AUTHORITY\NetworkService.

I have the following checked:
Network DTC Access, Allow Remote Clients, Allow Remote Administration, All Inbound, Allow Outbound, Enable Transaction Internet Protocol , Enable XA Transactions

I have added msdtc.exe to each side's firewall and also opened port 135 on both sides.

I have stopped and restarted the MSDTC service on each side. Yet I still get that error in my triggers.

Any ideas??

Thanks!

OLE DB provider "SQLNCLI" for linked server "SW-05" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Procedure sp_switchboard_insert, Line 56

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SW-05" was unable to begin a distributed transaction.

Open in new window

0
Comment
Question by:PMH4514
  • 4
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
J_Carter earned 125 total points
ID: 22627654
it sounds like your stored proc begins a transaction and within that transaction it trys to modify data both locally and on the linked server. You need to limit your code in a transaction that involves a distributed query only to the remote server or local server, not both.
0
 

Author Comment

by:PMH4514
ID: 22627910
I don't explicitly start a transaction anywhere in the stored procedure or trigger code. Is there an "inherent" or default transaction? It inserts a row into a local table, and a trigger on insert in that local table calls a stored procedure on the remote server, which would be as you describe if that entire set of transactions is a transaction despite my not specifying to begin a transaction.

It's a pseudo and small scale replication I guess, a trigger on INSERT creates a copy of the insert statement and executes it on the remote server. Do I instead have to package those insert statements in a table and execute them on the remote server in a different process somehow?
0
 
LVL 6

Expert Comment

by:J_Carter
ID: 22628763
does your trigger fire as a before or a after?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:PMH4514
ID: 22628820
I'm sorta new at this, I didn't know one could specify before or after. Would that change the behavior I'm seeing? It's defined as follows:


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[t_service_request_to_switchboard]

	ON 

		[dbo].[service_request]

	FOR INSERT

AS

....

Open in new window

0
 

Author Comment

by:PMH4514
ID: 22629041
I changed FOR INSERT to AFTER INSERT and still have the same problem..
0
 

Author Comment

by:PMH4514
ID: 22634841
I reworked my strategy based on your first comment which is now working for me. Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now