Getting past Powershell & SQL’s “Incorrect syntax near ‘GO’ ” message

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()

7 thoughts on “Getting past Powershell & SQL’s “Incorrect syntax near ‘GO’ ” message

  1. It is awesome sometimes how a short article written years ago and probably at the other side of the world can save a developer’s day 🙂 Thanks !

    • It was in a github gist, which may not have been rendering for you correctly; I’ve inlined the script instead, hopefully you can see it ok now 🙂

Leave a Reply to Robin Osborne Cancel reply

Your email address will not be published. Required fields are marked *