ADO.NET 1.x, How to mimick Query Analyzer Batch Execute?

Hi,
.NET v1.x SP1
VS 2003
SQL Server 2000 SP3
Server 2000, XP, Server 2003
I would like to programmatically execute {possibly many} SQL Server
batch scripts. Aka I have many scripts that drop/add stored procedure
definitions, alter table definitions & constraints, etc... and I would
like to run them from within a C# program.
All of the batch scripts were generated by Visual Studio and they run
OK in Query Analyzer; however they do not work from a
SQLConnection/SQLCommand instance in C# code. I assume the problem is
because the script file is a command batch, here's the exception
message:
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 9: Incorrect syntax near 'GO'.
'CREATE PROCEDURE' must be the first statement in a query batch.
Must declare the variable '@.tiRequestStatusID'.
Must declare the variable '@.tiRequestStatusID'.
Must declare the variable '@.iWireAccessRequestID'.
Must declare the variable '@.iProcessedByID'.
A RETURN statement with a return value cannot be used in this context.
Line 123: Incorrect syntax near 'GO'.
Line 126: Incorrect syntax near 'GO'.
Line 128: Incorrect syntax near 'GO'.
Line 131: Incorrect syntax near 'GO'.
Is there a "best practice" for executing a batch script
programmatically in .NET 1.x' Should I spawn an OSQL command line for
each script file?Crash wrote:
> Hi,
> .NET v1.x SP1
> VS 2003
> SQL Server 2000 SP3
> Server 2000, XP, Server 2003
> I would like to programmatically execute {possibly many} SQL Server
> batch scripts. Aka I have many scripts that drop/add stored procedure
> definitions, alter table definitions & constraints, etc... and I would
> like to run them from within a C# program.
> All of the batch scripts were generated by Visual Studio and they run
> OK in Query Analyzer; however they do not work from a
> SQLConnection/SQLCommand instance in C# code. I assume the problem is
> because the script file is a command batch, here's the exception
> message:
> Line 2: Incorrect syntax near 'GO'.
> Line 4: Incorrect syntax near 'GO'.
> Line 9: Incorrect syntax near 'GO'.
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Must declare the variable '@.tiRequestStatusID'.
> Must declare the variable '@.tiRequestStatusID'.
> Must declare the variable '@.iWireAccessRequestID'.
> Must declare the variable '@.iProcessedByID'.
> A RETURN statement with a return value cannot be used in this context.
> Line 123: Incorrect syntax near 'GO'.
> Line 126: Incorrect syntax near 'GO'.
> Line 128: Incorrect syntax near 'GO'.
> Line 131: Incorrect syntax near 'GO'.
> Is there a "best practice" for executing a batch script
> programmatically in .NET 1.x' Should I spawn an OSQL command line
> for each script file?
"GO" is not a T-SQL keyword. It is a user-defined batch separator that
tools like Query Analyzer understand and use to parse a script into
individual batches. What I normally do when I write batch processing
programs is to leave the the "GO" statements in the file (you can use
any batch separator you want). You should read the file, line by line,
and every time you encouter a "GO" fire off the batch to SQL Server
(without the GO line) and continue through the file.
David Gugick - SQL Server MVP
Quest Software