advice

hey all,
does sql batch files allow you to have transactions? i'm trying to update 3
tables in a sql batch file and was wondering if this was a good idea or
should i just use stored procedure?
thanks,
ariari wrote:
> hey all,
> does sql batch files allow you to have transactions? i'm trying to
> update 3 tables in a sql batch file and was wondering if this was a
> good idea or should i just use stored procedure?
> thanks,
> ari
Sure you can use transactions. Just add BEGIN TRAN / COMMIT TRAN /
ROLLBACK to the batch where needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||this helped, thank you.
"David Gugick" wrote:

> ari wrote:
> Sure you can use transactions. Just add BEGIN TRAN / COMMIT TRAN /
> ROLLBACK to the batch where needed.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

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