24

I have SQL script which I want to execute using azure DevOps pipeline

I found multiple tasks for SQL but can not find any required task where I can pass sql server , database name and login details. Is there any task available for this ?

If there is not any task available and only way to execute is powershell script any sample available script for this ?

Improve this question

3 Answers 3

Reset to default
20

You can use PowerShell to execute sql scripts. Example:

Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(sqlserver) -Database $(dbname) -Username "$(username)" -Password "$(pwd)" -QueryTimeout 36000 -Verbose

Add custom variables (scriptfilepath, sqlserver, ...) and set values to them.

  1. PowerShell task
  2. Define variables
  3. Invoke-Sqlcmd
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

How to run multiple sql files using the above script ?
@SurajTripathi running multiple sql files may not be supported, per learn.microsoft.com/en-us/powershell/module/sqlserver/…
one possible way is to wrap the Invoke-Sqlcmd statements into a main.ps1 and pass in the parameters when calling main.ps1
7

You can use Invoke-Sql command like this

$SQLServer = "TestServerOne"
$db3 = "TestDB3"
$qcd = "PRINT 'This is output'"
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Username "User" -Password "Password" -Verbose

Make sure SqlServer module is installed. It works also with Powershell Core

You can also try to use Run SQL Server Scripts Task extension

Improve this answer

Comments

5

If you want to do this in Azure Release Pipeline (classic), you can use the ' Azure SQL Database deployment ' block which uses Invoke-Sqlcmd under the hood.

enter image description here

With that, you can configure it to execute an SQL script on a given database under one or your subscriptions or service connections.

enter image description here

Improve this answer

3 Comments

how can we run multiple .sql files in one task?
@Schatak I don't know a proper way but if there's only a few I'll just duplicate the task. If you have so many of them, I'd say you do a file merge to create a single file during your build steps. Any text file merging technique would be fine for SQL too, but I haven't done this on a pipeline, so I don't really know what is available.
Does this also works if the SQL servicer is in private vnet in other subscription and no public endpoint?

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.