Solved

Trigger and MSDTC issue

Posted on 2006-11-16
10
803 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now