What are the challenges you face when working across database platforms? Take the survey
Options

Deployment of Stored Procedure with linked server by using DLM Automation

GapyGapy Posts: 1 New member
Dear all,

as we try to deploy some stored procedure to SQL Server 2016, which has linked server (ODBC) to other server, we are getting the following error:

"WARNING: The error 'The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server  does not support the required transaction interface.' occurred when executing the following SQL:
"

We have tried with TransactionIsolationLevel ReadCommited and also with SQLCompareOptions (NoTransactions), and nothing helped (see below):

Invoke-DlmDatabaseSchemaValidation -TemporaryDatabase $temporaryDatabase -SQLCompareOptions "-IgnoreUsersPermissionsAndRoleMemberships, -IgnorePermissions, -NoTransactions" -FilterPath "C:\Scripts\deployment.scpf" -TransactionIsolationLevel ReadCommitted

We have also tried by disabling "Enable Promotion of Distributed Transaction for RPC", did not help also.

The Stored Procedure in SSMS with linked server is working normally.

What could be the problem?  

Thanks & regards,
Gasper
Sign In or Register to comment.