Jun 18, 2011 at 1:43 PM
Edited Jun 18, 2011 at 1:57 PM
I have sql scripts residing in folders called Tables, Views, StoredProcs etc., and I want to automate their deployment. But I want to be able to rollback if anything fails. Is it possible to have a transaction block across multiple sqlexecute tasks?
I took a look at the Execute() function within SqlExecute, and it looks like you are opening a connection at the beginning, and closing it at the end... SO even if I do :
<SqlExecute Action="ExecuteScalar" Sql="BEGIN TRAN" .... >
<SqlExecute Action="Execute" Files="@(Tables)".... >
<SqlExecute Action="Execute" Files="@(Views)".... >
<SqlExecute Action="ExecuteScalar" Sql="COMMIT" .... >
it is not going to work. Is there any other way?
Edit : The order in which the folders are picked up is important, tables have to be created before triggers, and so on.