May 9, 2003 at 2:40 pm
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
May 9, 2003 at 3:03 pm
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
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 12, 2003 at 9:25 am
or call OSQL in VB.....simply write the sproc to disk somewhere and start OSQL with the filename as parameter.... (See osql -?)
en then simply let OSQL execute your script......
should be easy to do......
May 12, 2003 at 9:53 am
I had tried isql, didn't work... was just going to experiment with osql.. and it works! Thanks! I think that's what I'll use.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply