Solved

Trigger and MSDTC issue

Posted on 2006-11-16
10
799 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

20 Experts available now in Live!

Get 1:1 Help Now