Many a night have I bashed my head on the keyboard when seeing “Incorrect syntax near ‘GO'” come back from a powershell script trying to execute a batch SQL script.

After learning that “GO” is not actually SQL, and more of a SQL Server Management Studio “batch helper”, I quickly knocked together this powershell script to execute SQL batch scripts remotely. Fits nicely into an environment creation pipeline, so it does.

Param(
    [string]$Server,
    [string]$DB,
    [string]$user,
    [string]$Pwd,
    [string]$Script
)

$batches = $Script -split "GO\r\n"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$DB;User ID=$user;Password=$Pwd;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
$SqlConnection.Open()

foreach($batch in $batches)
{
    if ($batch.Trim() -ne ""){

        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $batch
        $SqlCmd.Connection = $SqlConnection
        $SqlCmd.ExecuteNonQuery()
    }
}
$SqlConnection.Close()