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 ?
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.
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
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.
With that, you can configure it to execute an SQL script on a given database under one or your subscriptions or service connections.
