CODEWITHSUNDEEP
sqlpowershellazure-devops
Remo Turchetti
Remo Turchetti

Reputation: 43

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

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."

Upvotes: 1

Views: 4763

Answers (2)

Levi Lu-MSFT
Levi Lu-MSFT

Reputation: 30383

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

Upvotes: 0

Krzysztof Madej
Krzysztof Madej

Reputation: 40919

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?

Upvotes: 2

Related Questions