CreateSQLTableEmailReporting.ps1

#Set Defaults (Optional) which allows you to skip defining instance, user, and password each time
$AzureDefaultInstanceName = "ServerName"
$AzureDefaultDatabaseName = "DatabaseName"
$username = Get-AutomationVariable -Name 'Administrator'
$password = Get-AutomationVariable -Name 'Password'


#The actual function
Function get-azureSQL (
[string]$DatabaseName = $AzureDefaultDatabaseName,   
[string]$InstanceName = $AzureDefaultInstanceName,
[string]$Query){
$connectionString = "Server=tcp:$($InstanceName).database.windows.net,1433;"
$connectionString = $connectionString + "Database=$($DatabaseName);"
$connectionString = $connectionString + "User ID=$($username)@$($InstanceName);"
$connectionString = $connectionString + "Password=$($password);"
$connectionString = $connectionString + "Encrypt=True;"
$connectionString = $connectionString + "TrustServerCertificate=False;"
$connectionString = $connectionString + "Connection Timeout=30;"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = $connectionString

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $Query

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$SqlConnection.Close()
return $DataSet.Tables[0]
}





$sqlquery3 = @'
CREATE TABLE InternalEmailActivity (
    Date datetime,
    NumberOfInternalEmails int,
);
'@


get-azureSQL -Query $sqlquery3