Unable to deploy sql objects with DB Links using openquery

sam_alexander

I'm trying to use SQL Compare to deploy stored procedures with Openquery which links to a DB2 server.  I can create the stored procedure without issue in SSMS on the Target database, but when I try to deploy it through SQL Compare I get this:

"The requested operation could not be performed because OLE DB provider "DB2OLEDB" for linked server "*******" does not support the requested transaction interface"

The DB Link exists on the server and I have access to it, and the DB2 OLE DB provider  does exist on the Target Server.  As I said I can deploy the stored procedure to the server manually and it runs without issue.

How can I get around this?  Many of our SQL components use OPENQUERY to cross to DB2 or other SQL Servers, but in all cases I've tried I get this when trying to deploy in SQL Compare.  Thanks.

Pete_Ruiz

Hello @sam_alexander ,

This is actually an issue with Transaction Handling and you can work around this by changing the Transaction Isolation Level or using the NoTransactions error option, please see the following Help Center article:

https://productsupport.red-gate.com/hc/en-us/articles/360007807893-The-operation-could-not-be-performed-because-OLE-DB-provider-for-linked-server-was-unable-to-begin-a-distributed-transaction

I hope this helps. Please let me know if you have any questions.

Kind regards,

Pete Ruiz

Redgate Product Support