|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ADO.NET 1.x, How to mimick Query Analyzer Batch Execute??..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:
Show quote > Hi, "GO" is not a T-SQL keyword. It is a user-defined batch separator that > > .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? 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  |
|||||||||||||||||||||||
Other interesting topics