[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Trigger and MSDTC issue

Posted on 2006-11-16
10
Medium Priority
?
837 Views
Last Modified: 2012-06-27
I have a very simple Trigger on a high traffic indexed table on a SQL Server 2000 Enterprise server.
The trigger goes like this:

CREATE TRIGGER tr_AnswerBankOrdersInserted
on AnswerBank
FOR INSERT
AS
BEGIN

INSERT INTO AnswerBankOrdersInserted (fkUserOrder, iRevision)
Select Distinct fkUserOrder, iRevision from Inserted

END


All it's supposed to do is to grab 2 of the 6 columns in the "AnswerBank" table and "ON INSERT" insert them into a
new table called AnswerBankOrdersInserted.


This trigger works perfectly when I try to insert data with a Select Into query or any other simple direct query,
however it fails when I try to Insert data from a stored procedure.

I get the following error:
[SQL Server]MSDTC on server 'XXXXXX' is unavailable.

I get this error when I try to execute the stored procedure from a web page.

I'm still not 100% sure that the issue is from the trigger, however thats the only thing I can point to at this moment.

Thanks for any help.
 

0
Comment
Question by:apirnia
[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
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17962645
On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking
START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES.
 Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.

0
 
LVL 9

Author Comment

by:apirnia
ID: 17962660
I tried that, but it tells me that it is unable to start it and gives me this number:

1073737712
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!

 
LVL 9

Author Comment

by:apirnia
ID: 17962851
That was the same error I was getting. I tried both methodes and non of them worked. Basicly after the steps I could not get that service started.

0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17962903
check the msdtc. log file in the system32 folder

and rt click the properties and c what all permissions the logon account name which u gave in msdtc service property(log on tab) has.

it shudve the following permissions.
Read & Execute
 Read
Write
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17962932
Did you try the two methods given on the site
0
 
LVL 9

Author Comment

by:apirnia
ID: 17966562
Yes I tried both methodes and non of them worked. The user on that service is Network Service and it has Full permissions on the msdtc.log file .
0
 
LVL 9

Author Comment

by:apirnia
ID: 17969184
I figured it out:

CAUSE
This behavior occurs if the Distributed Transaction Coordinator (DTS) service is disabled or if network DTC access is disabled. By default, network DTC access is disabled in Windows Server 2003.

WORKAROUND
To work around this behavior, install network DTC access on both servers:
1. Click Start, and then click Control Panel.
2. Click Add or Remove Programs, and then click Add/Remove Windows Components.
3. In the Components box, click Application Server, and then click Details.
4. Click to select the Enable network DTC access check box, and then click OK.
5. Click Next, and then follow the instructions that appear on the screen to complete the installation process.
6. Stop and then restart the Distributed Transaction Coordinator service.
7. Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).


http://support.microsoft.com/kb/816701
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18298375
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

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!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

650 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