CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008
user516322
user516322

Reputation: 338

Executing Entire .sql file on SQL Server 2008

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?

Upvotes: 0

Views: 442

Answers (2)

Steve
Steve

Reputation: 216263

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

Upvotes: 0

marc_s
marc_s

Reputation: 754268

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.

Upvotes: 2

Related Questions