Solved

unable to begin a distributed transaction

Posted on 2008-10-02
6
2,131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

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…
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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