Execute T-SQL Scripts (with GO's) Programmatically with SMO (or DMO)

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....

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!!!

..."

This is a very cool tip from Jon. And it smells much better than OSQL or GO parsing based solutions...

And for those of us in SQL2k land, it looks like SQL DMO has similar functionality in its SQLServer/SQLDatabase .ExecuteImmediate, .ExecuteWithResults, .ExecuteWithResultsAndMessages methods.

I tested ExecuteImmediate with a test T-SQL script with a number of GO's (creating a table, SP, dropping the SP, creating it again, all in one script with GO's separating the batches) and it seemed to work just as expected...

Related Past Post XRef:
Call SQLCMD from .Net with Output Capture