Unable to deploy sql objects with DB Links using openquery

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.

Sam Alex March 05, 2020 19:57

Comments

1 comment

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

Pete_Ruiz March 06, 2020 22:50

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Unable to deploy sql objects with DB Links using openquery

Comments

Add comment