How can I run sql script for Azure Sql Server

[mwang]

Dear Support,
I am quite new to Octopus. And try to deploy our application and database to Azure and Azure Database with octopus.

I tried to use “Run an Azure PowerShell Script” tempalte to update azure database. But I got error as:
“Invoke-Expression : The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file”. Do you have any idea about this? Or do you have a better solution to run sql script for a Azure Sql Server database?

Thanks.

Regards,
Max

[Bob_Walker]

Hi Max,

Under the covers, Azure SQL Server is no different than the self-hosted SQL Server you have used in the past. What I typically use to call out to SQL is regular PowerShell and ADO.NET. The example script below will create a user on an Azure SQL Database. I had to add some try/catch logic because of how I am running the script (I want it to run multiple times, but only create the user once).

Write-Host "Username found, using SQL Authentication"
$connectionString = "Server=$createSqlServer;Database=master;User ID=$createSqlLoginUserWhoHasCreateUserRights;Password=$createSqlLoginPasswordWhoHasRights;"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString

$command = $sqlConnection.CreateCommand()
$command.CommandType = [System.Data.CommandType]'Text'

Write-Host "Opening the connection to $createSqlServer"
$sqlConnection.Open()

$escapedLogin = $createSqlLogin.Replace("'", "''")
Write-Host "Running the if not exists then create user command on the server for $escapedLogin"

$success = $true
Try{
	Write-Host "A password was sent in, creating account as SQL Login"
	$escapedPassword = $createSqlPassword.Replace("'", "''")
	$command.CommandText = "CREATE LOGIN [$createSqlLogin] with Password='$escapedPassword'"   
	$command.ExecuteNonQuery()
	Write-Host "Successfully created the account $createSqlLogin"
}
catch {
	if ($_.Exception.Message -like "*The server principal '$createSqlLogin' already exists*")
    {
    	$success = $true
    }
    else{
    	$success = $false
    }
}
finally{
	Write-Host "Closing the connection to $createSqlServer"
	$sqlConnection.Close()
}

if ($success -eq $true)
{
	exit 0
}
else {
	exit 1
}

In the Azure Portal it will give you a connection string you can use.

[mwang]

Hi Bob,

Let me try your solution. Thanks for help.

Regards,
Max