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

Linked Server Failure with Invoke-DlmDatabaseSchemaValidation

$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?

Best Answer

Answers

  • Options
    PatIrvinPatIrvin Posts: 4 New member
    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?
Sign In or Register to comment.