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

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;

//Microsoft.SqlServer.Smo.dll
using Microsoft.SqlServer.Management.Smo;
//Microsoft.SqlServer.ConnectionInfo.dll
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));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
Published Tuesday, November 7, 2006 1:32 AM by Jon Galloway

Comments

# re: Handling "GO" Separators in SQL Scripts - the easy way

Nice :)

But SMO is SQL 2005 only right ?

Tuesday, November 7, 2006 10:15 AM by Mischa Kroon

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Tuesday, November 7, 2006 11:03 AM by Greg

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Tuesday, November 7, 2006 1:25 PM by Rob Conery

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Tuesday, November 7, 2006 2:24 PM by Jon Galloway

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Thursday, November 9, 2006 12:28 PM by Dan

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Thanks a lot!

Friday, November 17, 2006 5:01 AM by Shubha

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Sunday, December 24, 2006 7:13 AM by Adel

# re: Handling "GO" Separators in SQL Scripts - the easy way

it is working very well in .Net....

But is there any Solution

to solve this problem in VB6

Thanks in advance,

eSiva

Thursday, April 5, 2007 1:24 AM by eSiva

# re: Handling "GO" Separators in SQL Scripts - the easy way

Hi Jon,

Thanks for the code.

A slight correction in lines

FileInfo fileInfo = new FileInfo(fi.FullName);            

string script = fileInfo.OpenText().ReadToEnd();

The FileInfo is already present it works as it is.

string script = fi.OpenText().ReadToEnd();

Thanks again

Anton

Wednesday, April 11, 2007 11:03 AM by Anton

# re: Handling "GO" Separators in SQL Scripts - the easy way

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 ?

capicapi

Friday, April 20, 2007 8:54 AM by capicapi

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Thursday, May 17, 2007 7:53 AM by vinay pugalia

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Monday, October 15, 2007 10:08 AM by JBach

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

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

           SMOServer.ConnectionContext.ExecuteNonQuery(script)

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.

Friday, October 19, 2007 2:17 PM by Jose

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

cheers!

lb

Tuesday, November 6, 2007 9:41 AM by secretGeek

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Thursday, January 10, 2008 11:20 PM by Lee

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Thursday, January 17, 2008 12:09 AM by Shariq Qamar

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

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

server.ConnectionContext.ExecuteNonQuery(script);

Can be replaced with

ServerConnection server = New ServerConnection(connection);

server.ExecuteNonQuery(script);

saves creating an extra object which is not used.

Tuesday, April 15, 2008 4:30 AM by SM_ALLEN

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Example:

Use <BD>

go

set nocount on

go

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

select * from table1

go

Wednesday, July 23, 2008 4:53 PM by Carlos Lobo

# re: Handling "GO" Separators in SQL Scripts - the easy way

Hi

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

theServer.ConnectionContext.ExecuteNonQuery(Script);

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)

Friday, August 1, 2008 8:04 AM by Aidan

# re: Handling "GO" Separators in SQL Scripts - the easy way

using Microsoft.SqlServer.Management.Smo = Brilliant!

Sunday, August 17, 2008 7:41 AM by wisecarver

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Monday, August 18, 2008 9:44 AM by Harry McIntyre

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Wednesday, September 10, 2008 11:47 AM by Gavin

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Tuesday, November 25, 2008 10:58 PM by Dan

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Monday, May 11, 2009 9:04 AM by John

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Tuesday, May 26, 2009 3:50 AM by Frank

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Monday, June 1, 2009 12:47 PM by spirit1988

# re: Handling "GO" Separators in SQL Scripts - the easy way

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.

Tuesday, August 11, 2009 4:45 AM by Jim

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

           try

           {

               myConn.Open();

               myCommand.CommandType = CommandType.Text;

               //create DB if not exist

               myCommand.ExecuteNonQuery();

               //Load file

               myConn.ChangeDatabase(dbname);

               String sqlScript = System.IO.File.ReadAllText(fullScriptFile, Encoding.Unicode);                

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

               server.ConnectionContext.ExecuteNonQuery(sqlScript);                

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

           }

           finally

           {

               if (myConn.State == ConnectionState.Open)

               {

                   myConn.Close();

               }

           }

           return res;

       }

   }

Thursday, November 26, 2009 5:25 AM by DoanHuynh

# re: Handling "GO" Separators in SQL Scripts - the easy way

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;

cmdCreate.ExecuteNonQuery();

}

Friday, December 11, 2009 6:49 AM by Perrier

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Wednesday, December 23, 2009 7:24 AM by Sergio

# re: Handling "GO" Separators in SQL Scripts - the easy way

Excellent.  Thanks for the information

Tuesday, April 13, 2010 6:03 AM by Chris

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Sunday, November 28, 2010 9:42 PM by Jack

# re: Handling "GO" Separators in SQL Scripts - the easy way

Hi All,

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

server.ConnectionContext.ExecuteNonQuery(sqlScript);  

How can we handle this? or    

So how can i get error code ?

Friday, October 14, 2011 12:12 AM by roshil

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

thanks in advance...

Wednesday, May 16, 2012 5:37 AM by James Nicolas

# re: Handling "GO" Separators in SQL Scripts - the easy way

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

Saturday, August 4, 2012 5:34 AM by Mayank

# re: Handling "GO" Separators in SQL Scripts - the easy way

That's rlleay thinking at an impressive level

Saturday, March 9, 2013 4:19 AM by Amberlee

# re: Handling "GO" Separators in SQL Scripts - the easy way

Asking questions are really good thing if you are not understanding

something entirely, however this article offers fastidious understanding yet.

Tuesday, April 23, 2013 10:33 AM by Jones