unable to begin a distributed transaction

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??


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

Who is Participating?
J_CarterConnect With a Mentor Commented:
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.
PMH4514Author Commented:
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?
does your trigger fire as a before or a after?
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

PMH4514Author Commented:
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:

ALTER TRIGGER [dbo].[t_service_request_to_switchboard]

Open in new window

PMH4514Author Commented:
I changed FOR INSERT to AFTER INSERT and still have the same problem..
PMH4514Author Commented:
I reworked my strategy based on your first comment which is now working for me. Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.