Handling "GO" Separators in SQL Scripts - the easy way

If you've ever had to execute one or more SQL scripts from ADO.NET, you've likely run into the GO batch terminator issue. Any SQL script that does anything worthwhile has more than one batch, separated by a GO terminator. The problem is that "GO" isn't valid T-SQL, it's just a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window. If you try to execute T-SQL scripts with GO commands in them via ADO.NET SqlCommand.ExecuteNonQuery, you'll get an error that says something like:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch

Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and "gets" the GO statement as a batch separator. And the crowd goes wild!!!

I'm telling you, if you're doing anything with SQL Server from .NET code, you really have to look at SMO. 

Here's a simple sample app that iterates SQL scripts in a directory and executes them with that fancy ConnectionContext.ExecuteNonQuery - the one that rocks a house party at the drop of a hat, while retaining the ability to beat a biter down with an aluminum bat:

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public class RunAllSqlSriptsInDirectory
public static void Main()
string scriptDirectory = "c:\\temp\\sqltest\\";
string sqlConnectionString = "Integrated Security=SSPI;" +
"Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
DirectoryInfo di
= new DirectoryInfo(scriptDirectory);
FileInfo[] rgFiles
= di.GetFiles("*.sql");
foreach (FileInfo fi in rgFiles)
FileInfo fileInfo
= new FileInfo(fi.FullName);
string script = fileInfo.OpenText().ReadToEnd();
SqlConnection connection
= new SqlConnection(sqlConnectionString);
Server server
= new Server(new ServerConnection(connection));


  • Nice :)

    But SMO is SQL 2005 only right ?

  • Great tip Jon! Thanks for posting this. On a very low priority thread, I've needed a tip like this for a while now...

    BTW, It looks like the DMO (SQL7,2k) equivalent is SQLServer/SQLDatabase ExecuteImmediate, ExecuteWithResults, ExecuteWithResultsAndMessages,ect...

    I've tested SQLDatabase.ExecuteImmediate with a script containing GO's and it seemed to work as just fine...

    You da man for pointing me in the right direction on this... thanks again.

  • Nice one Jon! We use a similar technique for installing the Commerce Starter Kit. In our install directory is a scripts directory - these files are read in by the install page and parsed on the "GO" statement so each is read and executed in-turn.

  • Parse no more, Rob! Let SMO handle the heavy lifting.

  • Thanks for the information, this is exactly what I was looking for.

  • This was wonderful. I was facing one major problem because of this error.

    Thanks a lot!

  • I was shelling the scripts to OSQL but this is more sophasticated. thanks

  • it is working very well in .Net....
    But is there any Solution
    to solve this problem in VB6

    Thanks in advance,

  • thanx for this code!!

    just wondering...
    if .sql file is large ( 300ko or more), can we put all these characters into the string type variable without getting troncate or such kind of problem ?


  • can we make sure that these scripts should be executed within same transaction ?

  • Is there any way of getting back the messages from SQL wether the script was successful or had an error?

  • Im running into a problem here...I am using the actual server

    Dim SMOServer As New Smo.Server("(local)\sql05")

    for the most part this works, but when another app has the connection open, this just hangs without throwing an exception, and it is not pumping so my entire app hangs.

    Any sugestions?

    Email me at pablo-islas@hotmail.com if you have any sugestions.

  • yoink. have slurped up this code and using it now.


  • This was a life saver. Muchos Thank-You's.

  • I have to execute a batch of Queries (Crerate VIEW) that can work on SQL Server, Oralce and Sybase for this I have used "Go" Batch Seperator but it is working only for SQL Server. Please tell me which Seperator i will use that can work on almost all databases

  • Excellent, just what I needed. One small point though, the following statements

    Server server = new Server(new ServerConnection(connection));

    Can be replaced with

    ServerConnection server = New ServerConnection(connection);

    saves creating an extra object which is not used.

  • With no results is great. What about if I've a script with 'GO' an it returns results?


    set nocount on

    update table1 set fiel1 = 'XXX' where fieldkey > 100

    select * from table1

  • Hi

    Thanks for the tip. Worked great yesterday, rebooted machine this morning and get the following error while executing


    System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

  • using Microsoft.SqlServer.Management.Smo = Brilliant!

  • Awesome. I wrote a parser to split the files up a few years ago. I'm going to pretend to myself that SMO didn't exist back then.

  • Thanks a lot. I owe you a beer. The fact that SQL Server merrily generates GO statements that old-school ADO SqlCommand can't handle makes me mad as hell. Now, I don't have to take it anymore.

  • Strong. Just before going the parsing route I found your comment on Matt Berther's blog. Thanks for not only posting to your own blog but getting the right answer out to other blogs as well.

  • Excellent article. Thanks for the 411. This helped me get around this problem.

  • "And the crowd goes wild" :-) :-) I had to giggle. Thanks for that.

  • I have no error, but it doesn't work to me! I use Sql Server 2005 Express, and returns a -128 code :( and nothing works

  • This was working great for me on my development machine. But my client is running 64 bit Windows. Apparently when he installs SQL Server Express on 64-bit Windows, SMO is not installed. There are a bunch of confusing links for this on the MS site. By confusing, I mean, there are 2 links to download SMO for 64-bit systems. Which one is the right one? And supposedly it depends on a lot of other MS libraries, each with their own set of double links. Ended up being quite a confusing mess, and I'm thinking about going back to just parsing the GO statements myself to avoid dependence on SMO.

  • Base on your tip, I have developed a function that create the DB and update the Schema of DB in fullScriptFile. I would like to share with you all. Hope it help someone.

    class CreateDatabase
    public bool CreateDB(string dbserver, string user, string pw, string dbname,string fullScriptFile)
    bool res = false;
    String str;
    SqlConnection myConn = new SqlConnection("Server=" + dbserver + ";Integrated security=SSPI;database=master;User ID=" + user + ";Password=" +pw+ ";");
    str = "IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + dbname + "') CREATE DATABASE [" + dbname+ "];";
    //string fullScriptFile = @"D:\...\datafull.sql";
    SqlCommand myCommand = new SqlCommand(str, myConn);
    myCommand.CommandType = CommandType.Text;
    //create DB if not exist
    //Load file
    String sqlScript = System.IO.File.ReadAllText(fullScriptFile, Encoding.Unicode);
    Server server = new Server(new ServerConnection(myConn));
    res = true;
    MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    catch (System.Exception ex)
    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    if (myConn.State == ConnectionState.Open)
    return res;

  • What about splitting the whole script at every GO and go? This way I get a string array, and the items are easily executed one by one without using anything else than SqlClient:
    string[] sqlscript;
    string[] separator=new string[] {"GO", "go"};
    sqlscript = Properties.Resources.script.Split(separator,StringSplitOptions.RemoveEmptyEntries);
    foreach (string sql in sqlscript)
    cmdCreate.CommandText = sql;

  • Perrier: you can not simply split by "GO", because longer words (i.e. identifiers) could contain GO inside.

  • Excellent. Thanks for the information

  • I don't think involve SMO is a good idea, hope we have another easy solution for the 'GO' statement.

  • Hi All,
    If am going to execute the following line,somtimes i am getting error because of the errors in the Script.

    How can we handle this? or
    So how can i get error code ?

  • Sadly I need to do it in VB6. Is there any solution for VB6'rs like us?

    thanks in advance...

  • Excellent post. I was having exact problem and it worked for me. Thanks...

  • That's rlleay thinking at an impressive level

  • Asking questions are really good thing if you are not understanding
    something entirely, however this article offers fastidious understanding yet.

Comments have been disabled for this content.