MS DTC required for executing triggers and stored procedures on same server - 【StackMirror】|sql-server|sql-server-2005|stored-procedures

This problem arises when integrating two applications using two databases in same SQL Server 2005. Both applications have their own database on the same server but we have to transfer some data from one database to another. We are using triggers and stored procedures to do that.

If we insert data into the databases with SQL Server Management Studio there is no need for msdtc but if we use the application to insert data we get the error saying DTC is not enabled. We have no control over the application inserting data.

My question in short is why is msdtc required when using the application to insert data?

note: triggers are not making calls between the databases only the stored procedures are

2012-04-04 23:21
by Kevin Peris

post your code.... - Mitch Wheat 2012-04-04 23:24

sounds like the auto-escalation on SQL Server 2005 problem: http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machine - Mitch Wheat 2012-04-04 23:25

it is likely the issue,i read that post before posting here. we are deploying in a client environment where this application is running and they don't have msdtc enabled. isn't there a way to work without enabling msdtc - Kevin Peris 2012-04-04 23:55

probably. like I said: post some code.. - Mitch Wheat 2012-04-05 00:14

though my first problem still stands as an alternative i've tried to make a call to a stored procedure from a trigger to insert some data to a database in a linked server. msdtc on both servers are stared and allowed network access. error i get is

'sqlncli for linked server returned message no transaction is active'

what is required to fix this. client is windows xp sp2 server is windows 2003 serve - Kevin Peris 2012-04-05 02:49

There are quite a few situations where one may be using MSDTC indirectly: clustered server, configured replication, most usages of COM+, and so on.

However, as already pointed by @MitchWheat in a comment, SQL Server 2005 (as well as the Oracle resource manager) specifically uses MSDTC whenever you have more than one database connection in a single transaction scope. This behavior has been fixed in SQL Server 2008 (if you consider it an inconvenience).

In your case, this is a client side, not a server side problem, but it can generally be either.

2012-04-05 07:07
by Jirka Hanika