By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,536 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,536 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Feb 17 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Feb 17 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.