How to execute SQL in a PowerShell script as part of a Visual Studio Team Services build task?

Question

How to execute SQL against an Azure SQL database as part of a PowerShell script in a VSTS build task in a hosted agent?

There are many tasks available for build definitions: we can execute a PowerShell script

or deploy SQL using DACPAC or SQLCMD

or even execute PowerShell on remote machines

but how to simply execute SQL as part of a PowerShell script? Invoke-Sqlcmd isn't available as part of the 'Azure PowerShell' task.

I guess it would be possible to remote desktop to an app service, install the SQL Server related bits there and use 'PowerShell on Target Machines' but I feel there has to be a simpler way - perhaps I'm just missing something obvious

Are you using the hosted agent or a private agent? Is your SQL server an on-prem SQL instance or an Azure SQL instance? — Daniel Mann, Sep 10 '17 at 20:40
Hosted agent and Azure SQL Database. Updated the question as well — thomius, Sep 10 '17 at 21:02

starian chen-MSFT · Accepted Answer · 2017-09-13T01:43:21.413

3

You can use Azure SQL Database Deployment task, there are SQL Script File and Inline SQL Script types.

Note: You can use Hosted agent, but not Hosted VS 2017 agent.

edited Sep 13 '17 at 01:43

answered Sep 12 '17 at 01:52

starian chen-MSFT

33,174
2
29
53

Our SQL batch scripts must be executed from a custom PowerShell script; This is used for versioning the database. It should be also able to exit on error which is possible at least via Invoke-Sqlcmd – thomius Sep 13 '17 at 12:49
@thomius Using Hosted agent instead (not hosted vs2017), then you can call Invoke-SqlCmd in PowerShell script. – starian chen-MSFT Sep 14 '17 at 01:30

score 1 · Answer 2 · answered Sep 11 '17 at 06:01

1

but how to simply execute SQL as part of a PowerShell script? Invoke-Sqlcmd isn't available as part of the 'Azure PowerShell' task.

I think we can use this PowerShell script to run SQL with Azure Powershell for Azure SQL database:

$connectionString = "Data Source=jasontest321.database.windows.net;Initial Catalog=jasonsql;User ID=jason;Password='password';Connection Timeout=90"
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "insert into test1 values (3) ; ;" 
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

Here is my test:

Also we can use Azure portal Query editor to query the table, like this:

answered Sep 11 '17 at 06:01

Jason Ye

13,710
2
16
25

Would it be possible to stop on error? We are doing patching which includes schema updates separated by GO statements (and logic like "if current version not supported then error / exit) – thomius Sep 11 '17 at 15:30
Do you mean use powershell to achieve the logic? yes, we can use PowerShell to check the version and run that script. – Jason Ye Sep 12 '17 at 01:26
@thomius Just checking in to see if the information provided was helpful. Please let me know if you would like further assistance. – Jason Ye Sep 13 '17 at 01:02
Yea - thanks, you described how best to execute SQL via PowerShell - starain-MSFT also mentioned the way to tweak Invoke-Sqlcmd into the process (using Hosted instead of Hosted VS2017). Now I'm running two separate processes (one for SQL patching, another for code building and deployment) - anyways, with that said things seem to work like a charm :) – thomius Sep 14 '17 at 17:38

score 0 · Answer 3 · answered Feb 06 '20 at 16:43

0

Invoke-SqlCmd becomes available after you run:

Install-Module -Name SqlServer -Force

Tested on windows-2019 and ubuntu-18.04 (latest at this time).

answered Feb 06 '20 at 16:43

Monsignor

2,671
1
36
34

How to execute SQL in a PowerShell script as part of a Visual Studio Team Services build task?

3 Answers3