I am trying to update the remote database through linked server. If I ran the simple "Update lnkSrv.DB.owner.table.. " it works. But when I execute through the store procedure it raising below error: OLE DB provider "SQLNCLI10" for linked server "LnkSrv1" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 5 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LnkSrv1" was unable to begin a distributed transaction. I tried every thing, MSDTC is enabled - "No Authentication Required is checked" and DTC is running on both server. I am able to ping the servers from both server. Enabled DTC through fire walls on one server and remote server doesnt have even firewall turned on. Also one thing I noticed is IF I ran the below query it works: BEGIN TRANSACTION select * FROM lnkSrv.DB.dbo.table commit TRANSACTION But not with this query: BEGIN Distributed TRANSACTION select * FROM lnkSrv.DB.dbo.table commit TRANSACTION it gives me the same above error. What I am missing here? Why its not working with "Distributed"? I checked everything on MSDTC. PLEASE any input or help is greatly appreciated.
Right click the SQL server, select properties then click on connections. You will see "Require distributed transactions for server-to-server communication" My guess is that the box must be checked for your query to work.
I figure it out!! Besides all the things checked, you need to make sure that you are able to ping the server with the server name not only with the IP address. I hope below information will be helpful to someone. Unable to Connect to a Specific Host or NETBios Name If you are able to connect to the other computer using its IP address, but you are not able to connect to the other computer using its host or NetBIOS name, there may be a name resolution problem. There are many methods that can be used to accomplish name resolution on a network including the following: • HOSTS files • Domain Name Service (DNS) • LMHOSTS files • Windows Internet Name Service (WINS)
I was able to resolve the error when the servers are not in clustered. Now I have one of the server is in cluster and running with the same error message again. Any one have any idea on how to resolve this issue in the cluster enviroment.