Running a text file as if it was being run in SQL Server Management Studio.

Thanks for the quick answer.

I want to say that SQLCMD as an answer to this question will not be accepted as I just found that.

I need this because my boss wants it and he determines how much I get paid. :)

Looking good. But one question back, why do I get an error after a GO? In the past, I would make scripts where a GO is needed.

You don't need the GO statement if you're running this in your program. You'd do something like this:

YourCommandObject.SQL = "DROP TABLE YourTable"
YourCommandObject.ExecuteNonquery

That should drop the table, assuming you have permissions to do so as Pawan mentioned.

Some additional info on "GO": this is not part of the T-SQL language, it's just a command that Microsoft has implemented in its SQL Server toolset.  That explains the error behaviour in C#.

Ref. SQL Server Utilities Statements - GO

Thanks. Since the original question did not ask about the GO, I'm assigning all points to this answer.

PS, there is a way to run scripts with GO statements

var connectionString = "connection string";
            var pathToScriptFile = fileToExec;
            var sqlScript = File.ReadAllText(pathToScriptFile);

            using (var connection = new SqlConnection(connectionString))
            {
                var server = new Server(new ServerConnection(connection));
                server.ConnectionContext.ExecuteNonQuery(sqlScript);
            }

Select allOpen in new window

You have to add references to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo.

I also had to add the following to my CONFIG file:

<startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
    <requiredRuntime version="v4.0.20506" />
  </startup>

Select allOpen in new window