The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB_PROD_04" was unable to begin a distributed transaction

0
votes

I have two servers:

SQL_UAT_01

DB_PROD_04

Both of these servers have the same database name and same tables:

SQL_UAT_01.Database_01.TestTable

DB_PROD_04.Database_01.TestTable

There is a trigger on each of these two tables.

When the trigger fires, it does a simple UPDATE on the table of the OTHER server.

Example

Trigger is fired here:

SQL_UAT_01.Database_01.Test

It does an update here:

DB_PROD_04.Database_01.Test

And vice-versa.

I'm running into an error which I have no clue how to fix.

Again, the error is this:

CallableStatementCallback; bad SQL grammar [{call spGetAndIncrementIndex(?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB_PROD_04" was unable to begin a distributed transaction.

I have already linked the servers on both sides.

Does anyone have ANY idea how I should go about fixing this?

2
sqltsqllinked-serverdistributed-transactions

2 Answers

0
votes

The answer was to install the DT on the server. It's a windows feature. I installed it and wahllah!

0
votes

If a server runs out of local ports, you also get MSDTC issues

This is also fixed by correcting ephemeral TCP port saturation:

http://msdn.microsoft.com/en-us/library/aa560610%28v=bts.20%29.aspx