Deployment of Stored Procedure with linked server by using DLM Automation
Gapy
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:
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
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
