6

I'm porting our deployment infrastructure to Azure DevOps (formerly Visual Studio Team Services), but I've run into an issue I can't find any good solutions to.

We're using EF Core Migrations, and to work around some unrelated issues we need to dynamically wrap SQL scripts in SQLCmd, which is easy using PowerShell.

However, when executing our script as a regular PowerShell step in the release pipeline, it fails because SQLCMD.exe is not available. Not so surprising - but I also can't find any documented way of installing it.

Is there a tool installer or some similar ready-made component that will let me execute a PowerShell script that calls out to SQLCMD.exe (via Invoke-SqlCmd) as part of an Azure Devops Pipeline? If not, what's the easiest way to accomplish this anyway?

Improve this question
3
  • Are you using the hosted agent or a private agent? Commented Oct 16, 2018 at 0:57
  • Hosted agents (Windows with VS2017). Commented Oct 16, 2018 at 7:06
  • @DanielMann: ☝️ Commented Oct 16, 2018 at 8:46

3 Answers 3

Reset to default
6

Haven't tried this myself, but are you allowed to install Powershell modules for the current user on a hosted agent? Because Invoke-SqlCmd is part of the SqlServer module, which can be installed from the Powershell gallery with:

Install-Module -Name SqlServer -Scope CurrentUser
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is what I eventually ended up doing. It turns out the sqlcmd.exe is actually already on the machine (but not in $env:PATH) so all I had to do was to install the PS module (and port my script to use it everywhere; I had one direct reference sqlcmd.exe that I replaced with Invoke-SqlCmd...). Thanks!
@TomasAschan I want to add something that might help someone. In my case the system variable was already set and I used Invoke-Sqlcmd from the beginning in my script. Nevertheless, the task that uses this cmdlet in Azure Pipeline told me that Invoke-Sqlcmd cannot be found. The reason for this was: After installing cmdlet, I did not restart the computer (especially BuildAgent), which is why BuildAgent did not notice that there was a new module for powershell. After the restart everything worked fine. I used Install-Module -Name SqlServer -Scope AllUsers in order to install SqlServer.
0

You could try to create a package to install the needed tools on the agent, since that is now possible. You could use a Chocolatey task to run a package from it

There is an (old) chocolatey package available that you could try: https://chocolatey.org/packages?q=SQLCMD

Can't you use normal sql scripts via one of the available extensions? See here or here

Ofcourse, using a self-hosted agent could be an option, then you can install anything you want.

Improve this answer

Comments

0

This one worked for me

Install-Module -Name SqlServer -Force -AllowClobber

Not sure about -AllowClobber though, on my local PC the command complained about something without it and I didn't check on Azure tbh.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.