Linked Server Failure with Invoke-DlmDatabaseSchemaValidation

vfrank66

$scriptsFolder = "C:\Repo\Database"
$options = "IgnoreComments, ObjectExistenceChecks, IgnoreUsersPermissionsAndRoleMemberships"
$temporaryDatabase = New-DlmDatabaseConnection -ServerInstance <server name> -Database <database name>
$validationSchema = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation -TemporaryDatabase $temporaryDatabase -SQLCompareOptions $options -QueryBatchTimeout 240

Throws an error:
New-DlmDatabaseConnection, DLM Automation 2.0.9.292, Copyright © Red Gate Software Ltd 2014-2017
Invoke-DlmDatabaseSchemaValidation, DLM Automation 2.0.9.292, Copyright © Red Gate Software Ltd 2014-2017
Validating schema in scripts folder 'C:\TfsOnline\PlayGround\Database'.
Cleaning database '<database>' on server '<server>'.

Invoke-DlmDatabaseSchemaValidation : Schema validation failed: The operation could not be performed because OLE DB provider "SQLNCLI11" for
linked server "<linked server name>" was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI11" for linked server "<linked server name>" returned message "The partner transaction manager has disabled its support for
remote/network transactions.".
Local parameters [connectionString = Data Source=<server>;Initial Catalog=<db>;Integrated Security=True;Application Name="Redgate DLM Automation"]
At C:\DBA\Posh\TestInvokeDLMAutomation.ps1:7 char:38
+ ... ptsFolder | Invoke-DlmDatabaseSchemaValidation -TemporaryDatabase $te ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (database '<db>...ver '<server>':DatabaseConnection) [Invoke-DlmDatabaseSchemaValidation], Termi
natingException
+ FullyQualifiedErrorId : SchemaValidationError,RedGate.DLMAutomation.PowerShell.Commands.InvokeDlmDatabaseSchemaValidationCommand

The linked server works through SSMS and is valid on the same server using 'sa' connection properties.

It appears going to the server opening Administrative Tools -> Component Services -> Component Services -> Computers -> My Computer -> Local Dtc Properties -> Security -> Check Network DTC Acess and Allow Inbound and Allow Outbound. Let this functionality work but it is not in the documentation nor has it been a question yet.

Is this expected behavior with the error to correct or is this a bug? What functionality would cause this issue DLM Automation 2 or Powershell?

Sergio R

Hi,

This error occurs because of the Transaction Isolation Level.

By default DLM Automation uses the Serializable Transaction Isolation Level, however this deployment script requires a Transaction Isolation Level of Read Committed.

We don't consider this to be a bug.

To avoid this you can do one of two things:

1 - Use the -TransactionIsolationLevel parameter on the cmdlet and set it to ReadCommitted

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

Thank you,

PatIrvin

I have a pipeline in azure devops which uses Redgate change automation and receives the following error with linked server when release runs -

System.Management.Automation.CmdletInvocationException: Applying update script failed: The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "XXXXXXXXXX" was unable to begin a distributed transaction.

LegacyVSTSPowerShellHost.exe completed with return code: -1.

I have tried changing the transaction isolation level to Read Uncommitted in the build but same error.

How do I set it to no transactions from the gui?