question

Leo avatar image
Leo asked

SQL Red Gate Compare

Hi, I tried to synchronize the Database between 2 Servers using Red Gate Compare, I am getting the following error.
[7391] The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "PRODUCT_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI10" for linked server "PRODUCT_SERVER" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Anybody got that error before? Do you know how to resolve that issue? Thanks.
sql-server-2008red-gate
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered
This came up once before here on ASK. You might want to [look here][1] and see if you're dealing with a related issue. [1]: http://ask.sqlservercentral.com/questions/6109/cant-use-linked-servers-in-view
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Leo avatar image Leo commented ·
Hi Grant, Thank you very much. I have changed SET TRANSACTION ISOLATION LEVEL SERIALIZABLE => READ COMMITTED in Script and run it under the Development Server and it works perfectly.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Excellent. And that's a good blog post. I'll add it to the stack.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
It sounds like the Distributed Transaction Coordinator (DTC) must be offline on the server. Or, DTC is set to not allow remote connections. Either way, you'll need to go to the server to do this. Is is possible you're running SQL Compare across different domains? I only ask because I wouldn't have expected it to need to do a distributed transaction... unless there are also linked servers involved, maybe... Not sure about that.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Leo avatar image Leo commented ·
Hi Grant, You are right, that is error on Linked Server. We used the linked server in stored procedure. I already check the main server to make sure DTC is running and I want through the Comm Conponent -> "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound", "Enable TIP" are CHECKED and restarted the Server already. But problem still exists.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Then I'd check the scripts generated from SQL Compare as noted in the link below. It's possible that the transaction setting is causing the problem.
0 Likes 0 ·

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.