Solved

unable to begin a distributed transaction

Posted on 2008-10-02
6
2,127 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Learn about cloud computing and its benefits for small business owners.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

810 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