Executing Entire .sql file on SQL Server 2008

Question

I am trying to execute an entire file of SQL commands on SQL Server from a vb.net app. The issue is that if the SQL file contains any CREATE PROCEDURE commands, I get the following error:

A critical error has occurred. 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near the keyword 'PROC'

Dim sql = sqlFile.ReadToEnd()

Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ToString), cmd As New SqlCommand
With cmd
    .Connection = conn
    .CommandType = CommandType.Text
    .CommandText = sql
    .CommandTimeout = 300    
End With

conn.Open()
cmd.ExecuteNonQuery  
conn.Close()

How can I execute sql files over a SqlConnection containing CREATE PROCEDURE commands?

possible duplicate of [sql error:'CREATE/ALTER PROCEDURE' must be the first statement in a query batch?](http://stackoverflow.com/questions/9051006/sql-errorcreate-alter-procedure-must-be-the-first-statement-in-a-query-batch) — RedGreenCode, Aug 14 '14 at 18:10
make sure you have a batch separator `GO` between your Sql Statements. — M.Ali, Aug 14 '14 at 18:15
Seperating commands with "GO" results in the following error: A critical error has occurred. Incorrect syntax near 'GO'. — user516322, Aug 14 '14 at 18:17
@M.Ali: that would work if you'd execute the script in SQL Server **Management Studio** - but from a .NET application, the `GO` won't help .... — marc_s, Aug 14 '14 at 18:18
hmm true @marc_s , is there any ANSI batch separator? I know `GO` is SSMS specific. — M.Ali, Aug 14 '14 at 18:20
@M.Ali: no, not really. What I usually did was having a `.sql` with the `GO` in them to use in SSMS, and if I wanted to execute this `.sql` from C#, I need to split it up on the `GO` separators and execute each fragment separately against SQL Server — marc_s, Aug 14 '14 at 18:23
@marc_s, I second that, you should give this as answer to this question. — sallushan, Aug 14 '14 at 18:24

score 2 · Accepted Answer · answered Aug 14 '14 at 18:27

The issue is that each CREATE PROCEDURE needs to be the first statement in a batch (as the error message clearly says).

If you'd be executing your .sql in SQL Server Management Studio, you could use GO as separators:

....
GO 

CREATE PROCEDURE dbo.SomethingOrAnother......

but this GO is a SSMS-specific separator - not a general SQL statement.

There's really no "magic" way to execute a complete .sql script file against SQL Server from VB.NET - what I usually do are these steps:

read in the whole .sql file into a string
split the string containing the whole script into separate sub-strings on the GO separator
execute each fragment that results from this splitting up separately, against SQL Server

That way, any CREATE PROCEDURE .... will be the first statement in a separate batch, and then your script should work just fine from VB.NET.

score 0 · Answer 2 · edited Aug 14 '14 at 18:43

You could leave the GO separator in your script, but you should execute the script using the Microsoft.SqlServer.Smo objects that you can find in the

C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies (or the appropriate dir for your version)

Dim cmd = File.ReadAllText("d:\temp\create.sql")
Using con = new SqlConnection("....")
    Dim svrConnection = new ServerConnection(con)
    Dim server = new Server(svrConnection)
    Dim col = new StringCollection()
    col.Add(cmd)
    server.ConnectionContext.ExecuteNonQuery(col)
End Using

You need to have a reference (and imports) to the following assemblies

Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc

Executing Entire .sql file on SQL Server 2008

2 Answers2