how to execute sql script via powershell in azure pipelines yml

Hi amitaiemd,

to execute an SQL script in a non-Azure SQL Server using PowerShell in Azure Pipelines YAML, you can use the Invoke-SqlCmd cmdlet and this example PowerShell script:

- task: PowerShell@2
displayName: 'Execute SQL Script'
inputs:
targetType: 'inline'
script: |
$serverName = 'your_server_name'
$databaseName = 'your_database_name'
$userName = 'your_username'
$password = 'your_password'
$sqlScriptPath = 'path_to_your_sql_script'

# Connect to the SQL Server
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$userName;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

try {
# Read the SQL script file
$scriptContent = Get-Content -Path $sqlScriptPath -Raw

# Execute the SQL script
$command = New-Object System.Data.SqlClient.SqlCommand($scriptContent, $connection)
$result = $command.ExecuteNonQuery()

Write-Host "SQL script executed successfully."
}
catch {
Write-Host "An error occurred while executing the SQL script: $_"
throw
}
finally {
# Close the connection
$connection.Close()
}

Replace the values (your_server_name, your_database_name, your_username, your_password, and path_to_your_sql_script) with the actual values specific to your environment and SQL script.

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

Kindest regards

Leon Pavesic