The operation could not be performed because OLE DB provider ... for linked server ... was unable to begin a distributed transaction (SCA)

This error occurs because of the Transaction Isolation Level.

By default, SQL Change Automation for Powershell uses the Serializable Transaction Isolation Level, however this deployment requires a Transaction Isolation Level of Read Committed.

There are two alternate solutions to this issue:
1. Use the -TransactionIsolationLevel parameter on the cmdlet (or equivalent option on the add-on) and set it to ReadCommitted.

E.g. Powershell:

Invoke-DatabaseBuild -InputObject "C:\MyScriptsFolder" -TransactionIsolationLevel ReadCommitted

Azure Devops YAML:

steps:
- task: redgatesoftware.redgateDlmAutomationBuild.DlmAutomationBuildTask.RedgateSqlChangeAutomationBuild@4
displayName: 'Build WidgetShop1'
inputs:
operation: Build
subFolderPath: '$/Widgetshop/DBScripts'
packageName: WidgetShop
buildAdvanced: true
compareOptionsBuild: NoTransactions

2. Use the NoTransactions SQL Compare option
https://documentation.red-gate.com/sc14/using-the-command-line/options-used-in-the-command-line#Optionsusedinthecommandline-NoTransactions

Please note that when using NoTransactions the deployment will not be rolled back in case of error, so if you use this option it's advisable to backup first.

E.g.

Invoke-DatabaseBuild -InputObject "C:\MyScriptsFolder" -SQLCompareOptions "NoTransactions"

Azure DevOps YAML:

steps:
- task: redgatesoftware.redgateDlmAutomationBuild.DlmAutomationBuildTask.RedgateSqlChangeAutomationBuild@4
displayName: 'Build WidgetShop1'
inputs:
operation: Build
subFolderPath: '$/Widgetshop/DBScripts'
packageName: WidgetShop
buildAdvanced: true
transactionIsolationLevelBuild: readCommitted

Azure DevOps GUI (the example shows both options, you only need to use one of them):

Screenshot_2.png

Was this article helpful?

2 out of 5 found this helpful
Have more questions? Submit a request