Solved

Trigger and MSDTC issue

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

Expert Comment

by:Aneesh Retnakaran
ID: 17962746
0
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.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 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