0

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?

Improve this question
7
  • 2
    possible duplicate of sql error:'CREATE/ALTER PROCEDURE' must be the first statement in a query batch? Commented Aug 14, 2014 at 18:10
  • 1
    make sure you have a batch separator GO between your Sql Statements. Commented Aug 14, 2014 at 18:15
  • Seperating commands with "GO" results in the following error: A critical error has occurred. Incorrect syntax near 'GO'. Commented Aug 14, 2014 at 18:17
  • 1
    @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 .... Commented Aug 14, 2014 at 18:18
  • 1
    @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 Commented Aug 14, 2014 at 18:23

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.