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):
Was this article helpful?
Articles in this section
- Method not found: 'System.Security.CodeAccessPermission
- Couldn't connect to the database specified by the Target parameter: A connection was successfully established with the server, but then an error occurred during the login process
- Could not install SqlChangeAutomation from any registered PSRepository
- System.Exception: No match was found for the specified search criteria and module name 'SQLChangeAutomation'. Try Get-PSRepository to see all available registered module repositories
- No match was found for the specified search criteria and module name 'SqlChangeAutomation'.
- Online index operations can only be performed in Enterprise edition of SQL Server
- Unable to resolve package source
- error 5(Access is denied.) while attempting to open or create the physical file
- SQL Change Automation Installation freezes at 99%
- SQL Change Automation Powershell permissions