Azure Devops - How to use a powershell command to run SQL Scripts

Question

So, I am creating a build / deploy pipeline in azure Devops.

One issue I ran into is that I need to run a few SQL scripts every time a deploy is made. I am trying to do that through a Inline Powershell task.

The issue is that natively powershell does not recognize Invoke-Sqlcmd. So I had to install the module on the same script

I am doing that by doing

Install-Module -Name SqlServer -AllowClobber -Scope CurrentUser -Force

But any time it tries to run it is throwing me this error:

##[error]Exception calling "ShouldContinue" with "2" argument(s): "Windows PowerShell is in NonInteractive mode. Read and Prompt functionality is not available."

Krzysztof Madej · Accepted Answer · 2020-10-02 15:43:14Z

2

I tried this:

steps:
- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: 'Install-Module -Name SqlServer -Scope CurrentUser'

- task: PowerShell@2
  inputs:
    targetType: 'inline'
    script: 'Install-Module -Name SqlServer -AllowClobber -Scope CurrentUser -Force'

And for first I got WARNING: User declined to install module (SqlServer), but second succeded. Can you show how exactly you call it in your pipeline?

answered Oct 2, 2020 at 15:43

Krzysztof Madej

42.1k10 gold badges117 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Remo Turchetti Over a year ago

steps: - task: petergroenewegen.PeterGroenewegen-Xpirit-Vsts-Build-InlinePowershell.Xpirit-Vsts-Build-InlinePowershell.InlinePowershell@1   displayName: 'Inline Powershell'   inputs:     Script: |      #Get Scripts Location      Write-Host $(Agent.BuildDirectory)      Set-Location $(Agent.BuildDirectory)            Install-Module -Name SqlServer -AllowClobber -Scope CurrentUser -Force

After that the script does its sql run thing

Levi Lu-MSFT · Accepted Answer · 2020-10-06 02:55:10Z

0

Above error is because your self-hosted agent is running in service mode. I can reproduce the same error on my self-hosted agent which is configured Run as a service.

When i run my pipeline with above script on the self-hosted agent which is configured run interactively. It worked all fine.

You might need to reinstall your self-hosted agent and configure it to run interactively. Check here for more information.

Or, You can pre-install the SqlServer module on your agent machine. Then you will be able to use the invoke-sqlcmd command in your pipeline.

You can also use microsoft-hosted agent to run your pipeline. invoke-sqlcmd command is available in agent windows-2019 and vs2017-win2016

edited Oct 6, 2020 at 2:55

answered Oct 5, 2020 at 9:30

Levi Lu-MSFT

30.8k3 gold badges42 silver badges55 bronze badges

Collectives™ on Stack Overflow

Azure Devops - How to use a powershell command to run SQL Scripts

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related