Executing an SQL file on a remote database using DLM Automation in TeamCity

vchenna

Hi,

I have an .sql file (Update.sql) and I want to execute this on a database on a remote server using SQL server authentication. I want to achieve this using Teamcity.

$dbRelease = "C:\Users\vchenna\Desktop\SQLReleaseUpdates\Update.sql"

$production = New-DlmDatabaseConnection -ServerInstance "192.xx.xx.xx" -Database "MyDB" -Username "UserId" -Password "p@ssw0rd"

Use-DlmDatabaseRelease $dbRelease -DeployTo $production

But I am getting the below error:

[22:28:31][Step 2/2] New-DlmDatabaseConnection, DLM Automation 2.0.14.412, Copyright c Red Gate Software Ltd 2014-2017

[22:28:32][Step 2/2] Use-DlmDatabaseRelease : Cannot bind parameter 'InputObject'. Cannot convert

[22:28:32][Step 2/2] the "C:\Users\vchenna\Desktop\SQLReleaseUpdates\Update.sql" value of type

[22:28:32][Step 2/2] "System.String" to type "RedGate.DLMAutomation.Compare.Domain.DatabaseRelease".

[22:28:32][Step 2/2] At C:\TeamCity\buildAgent\temp\buildTmp\powershell1376217184470596571.ps1:3

[22:28:32][Step 2/2] char:28

[22:28:32][Step 2/2] + Use-DlmDatabaseRelease $dbRelease -DeployTo $production

[22:28:32][Step 2/2] + ~~~~~~~~~~

[22:28:32][Step 2/2] + CategoryInfo : InvalidArgument: (:) [Use-DlmDatabaseRelease], P

[22:28:32][Step 2/2] arameterBindingException

[22:28:32][Step 2/2] + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,RedGate.DLMAutoma

[22:28:32][Step 2/2] tion.PowerShell.Commands.UseDlmDatabaseReleaseCommand

Is this the right approach I am following or are there any other ways to achieve this?

If anyone had issues like this I would appreciate any advice. Thanks.

AlexYates

The release isn't just the update.sql file. It's a collection of objects including a copy of the source and target states, a diff report and an xml report of the warnings.

These other objects are used for various purposes, such as drift detection and reporting for approval processes.

Drop the "\update.sql" from the end and it will probably start working.