SqlServer.SqlExecute task doesn't close connection

Jan 14, 2014 at 4:56 PM
Hello,
I have such workflow: one target applies sql change scripts on database. If some script fails, I want to restore database in another target (Sql2008.Database Restore task).
But, if the script fails, the connection leaves and database cannot be restored
error : SmoException: System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on C:\Backups\1.0.22352.1\Database_new_20140114_1945.bak'.\r
I tried different properties from SqlServer.SqlExecute task, but nothing helped. I see the connection in SQL Activity Monitor till the full exit from script.

Call of SqlExecute looks like:
<MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute" Retry="true" Files="@(AllFiles)" ConnectionString="$(OpusDatabase)" Condition="'@(AllFiles)' != '' "/>
...
<OnError ExecuteTargets="RestoreDataBases" Condition="'$(BackupExists)' == 'true'"/>
And trial to restore is so:
<MSBuild.ExtensionPack.Sql2008.Database TaskAction="Restore" DatabaseItem="$(OPUSDataBaseMainName)" DataFilePath="$(BackupDirectoryPath)\$(OPUSDataBaseMainName)_$(BackupTime).bak"/>
I tried to alter database before restore with SqlExecute, but it also fails because of opened connection.
How can this be fixed?

Thanks, Roman
Coordinator
Jan 26, 2014 at 11:50 AM
Edited Jan 26, 2014 at 11:51 AM
I think this is more to do with the Restore task than any connection issues. I'm going to look at this but track it here
Marked as answer by mikeFourie on 1/26/2014 at 4:51 AM