The OLE DB provider "SQLNCLI" of the linked server cannot start the Distributed transaction plus the SQL transaction deadlock problem

"　　 This SQL job has been running every day well, I suddenly did not take effect? "

Encounter this sudden problem, my heart is calm, things can not be inexplicable, because it is a SQL job problem, first need to view the job history

sure enough a big x obvious don't want to continue to look at the wrong content:

　　executed as user NT Authority\Network SERVICE. The transaction (Process ID 51) is deadlocked with another process in the lock | The communication buffer resource and has been selected as the deadlock victim. Please rerun the transaction. [SQLSTATE 40001]  (Error 1205). The step failed .

A deadlock? , good business will be dead lock, check the previous day all staff work records, found no one on the job inside the stored procedures have made changes, insurance, or manually shouted: "Yesterday there is no one to change xxx ah?" "The Wood has Oh" "Change it to have money to take" "XXX is what?" "Well, you win, since this pro (stored procedure) No one moved, that is the other business hanging interference in the operation of this, resulting in a deadlock, wood method, dead way, look at the stored procedure, an open, is worthy of someone else to write code, thousands of lines ah, as a long time did not read the code of non-mainstream programmers, Pressure is infinite ah, suppress the mind to vomit, look for all the changes to the table, filter out the table that may be affected, and then find the employee work record (the staff work record is very important under the spit groove), sure enough, yesterday someone made a change to the table, so find the corresponding person, OK, things almost clear, The problem is that the code of the two goods, the other side performance is very innocent, "I tested Ah, the code is working properly, why the automatic implementation of the error, Balabala ..." Do not spit slot programmer error, continue to locate the problem:

Problem occurs:

Operation of SQL database for other servers in SQL stored procedures, with the assumption that transactions are used

Error message:

The operation could not be performed because the OLE DB provider "SQLNCLI" of the linked server "xxxxx" could not start the distributed transaction

Find the next information, http://www.cnblogs.com/qanholas/archive/2013/05/15/3080013.html the gods are such a standard answer, the mark should be worthy of the standard answer, write too detailed , considering a variety of situations, but spit groove ah, too much content, find useful information is very difficult, I here to filter the content, the problem is basically server configuration problems, the solution is as follows, to have a picture of the truth:

Step one: Go to the server (the wrong linked server), find the Control Panel-management tool-Component Services (know the command line to open the Component Services directly)

Step Two: Component Services-Computer---My Computer---Right-click---properties

Step Three: Properties window: Select msdtc--Security Configuration

Step four: Check those circles, and then confirm (to this step if you find no tick, then congratulations, hook up the problem on the solution, if found already hooked up, then sorry, you can only follow the above great God's link, one after another to troubleshoot, good luck! ）

Confirm, go to perform the error of the pro, sure enough, the error has not, and then test, no problem, logout server, finish, problem solving!

The OLE DB provider "SQLNCLI" of the linked server cannot start the Distributed transaction plus the SQL transaction deadlock problem