Friday, July 29, 2011

SQL Server Error: The partner transaction manager has disabled its support for remote/network transactions

I was getting foll. error when remotely executing a stored procedure over SQL Server Linked Server.


OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERNAME" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERNAME" was unable to begin a distributed transaction.

After much research I found the solutions using these links:
http://www.sqlwebpedia.com/content/msdtc-troubleshooting

http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/7172223f-acbe-4472-8cdf-feec80fd2e64/

Much thanks to Chuck Lathrope and Eric for these!

Create a linked server to itself in MS SQL Server 2008

On my testing environment we have transaction and reporting databases on same server. But in live environment, they are on different servers and connect to each other using Linked Servers feature. So to reproduce this environment on test server, I needed to create a Linked Server on test "transaction" DB server that would "Link" to the "reporting" DB server on the same physical DB server.

This proved impossible using Management Studio; but I was able to successfully do this using t-sql code:

EXEC sp_addlinkedserver @server = N'reportingserver',
    @srvproduct = N' ',
    @provider = N'SQLNCLI',
    @datasrc = N'SQL_Server_Instance',
    @catalog = N'DB_name'

Hope this helps someone.