[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

unable to begin a distributed transaction

Posted on 2008-10-02
6
Medium Priority
?
2,146 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 500 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
Independent Software Vendors: 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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