Execute Script from VB?

Question

Post reply

Execute Script from VB?

nickyvb

SSC Veteran

Points: 223
More actions
May 9, 2003 at 2:40 pm

#80719

Hi.. I'd like to be able to execute .sql scripts from VB to drop and create stored procedures. All my scripts are written in the following format:

USE dbname

GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'usp_testnicky' AND type = 'P')

DROP PROCEDURE usp_testnicky

GO

CREATE PROCEDURE usp_testnicky

BEGIN

--Code goes here

END

GO

GRANT EXECUTE...

GO

This works perfectly fine in QA, however, I've tried to execute the script via the SQLDMO method ExecuteImmediate (after reading the contents into a string with StreamReader), or via an ADO.net command object's ExecuteNonQuery method.

Both spit out the same error: System.Data.SqlClient.SqlException: 'CREATE PROCEDURE' must be the first statement in a query batch.

I've used A.Warran's scriptrunner as a reference but had to modify it because I'm using .net.

Is it the connection that can't support the GO's in my script? Any suggestions would be greatly appreciated!!

Thanks!! -nicky

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 1

GO is a batch separator used by Query Analyzer. Your best bet would be to parse the script, using the GO as a separator as well. For instance, execute all this together:

USE dbname

Then execute next in a separate call:

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'usp_testnicky' AND type = 'P')

DROP PROCEDURE usp_testnicky

Then:

CREATE PROCEDURE usp_testnicky

BEGIN

--Code goes here

END

Finally:

GRANT EXECUTE...

K. Brian Kelley