471,310 Members | 1,077 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,310 software developers and data experts.

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?

Feb 17 '06 #1
1 3626
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.

Similar topics

2 posts views Thread by Paul Sieloff | last post: by
3 posts views Thread by Matthew Wells | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.