Solved

Trigger and MSDTC issue

Posted on 2006-11-16
10
808 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
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17962746
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 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