Menu
I'm building a web app that attempts to install/upgrade the database on App_Start. Part of the installation procedure is to ensure that the database has the asp.net features installed. For this I am using the System.Web.Management.SqlServices object.
My intention is to do all the database work within an SQL transaction and if any of it fails, roll back the transaction and leave the database untouched.
the SqlServices object has a method "Install" that takes a ConnectionString but not a transaction. So instead I use SqlServices.GenerateApplicationServicesScripts like so:
string script = SqlServices.GenerateApplicationServicesScripts(true, SqlFeatures.All, _connection.Database); SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, script, ...); and then I use the SqlHelper from the Enterprise Library.
but this throws an Exception with a butt-load of errors, a few are below
Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near the keyword 'USE'. Incorrect syntax near the keyword 'CREATE'. Incorrect syntax near 'GO'. The variable name '@cmd' has already been declared. Variable names must be unique within a query batch or stored procedure. I'm presuming it's some issue with using the GO statement within an SQL Transaction.
How can I get this generated script to work when executed in this way.
672
GO is just a command to the Client SQL program (Query Analyzer, SSMS, etc.) to terminate the current batch and execute it. Go does not terminate the current session or process and transactions are session-scoped entities. So, transactions are not COMMITted when a GO is encountered, as the Previous script demonstrates.
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.
Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.
1 Answer. Show activity on this post. That's why transactions are unnecessary for sql-server functions. However, you can change transaction isolation level, for example, you may use NOLOCK hint to reach "read uncommitted" transaction isolation level and read uncommitted data from other transactions.
GO is not a SQL keyword.
It's a batch separator used by client tools (like SSMS) to break the entire script up into batches
You'll have to break up the script into batches yourself or use something like sqlcmd with "-c GO" or osql to deal with "GO"
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
