question

Bab avatar image
Bab asked

Distributed transation through linked server

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.
distributed
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bab avatar image Bab commented ·
That didnt help either.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Check, that you have enabled network access for the DTC. [Enable Network DTC Access][1] [1]: http://technet.microsoft.com/en-us/library/cc753510(v=WS.10).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bab avatar image Bab commented ·
Yes, its enabled and still not working.
0 Likes 0 ·
Bab avatar image
Bab answered
One of the thing in my case is two servers are NOT on the same Network...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bab avatar image
Bab answered
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)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bab avatar image
Bab answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.