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

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

vchennavchenna Posts: 1 New member
edited June 8, 2018 5:13PM in DLM Automation
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.




Answers

  • Options
    AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
Sign In or Register to comment.