Monday, 8 September 2014

Tool to Execute Multiple SQL Scripts – VS 2013 Release Management – Part 1

Is it possible to execute set of SQL scripts downloaded to Deployment Agent in VS 2013 Release Management? Yes. But this requires a custom tool and an action.
Let’s see how we can do this step by step.
As the first step write a PowerShell script capable of executing batch of scripts in a transaction.
 Param([string]$ServerInstance, [string]$DatabaseName, [string]$ScriptPath)   
  $ErrorOccured = $false   
  #Executing following snapins to Invoke-SqlCmd    
  Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue   
  Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue   
  Write-Host "Executing patch scripts of path: $PatchScriptsPath"   
  Start-Transaction -RollbackPreference Error   
  Use-Transaction -TransactedScript {    
   foreach ($file in Get-ChildItem -path $ScriptPath -Filter "*.sql")   
   {    
    Write-Host Executing: $file.name ...   
    $ScriptPath = $ScriptPath + "\" + $file.name   
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -InputFile $ScriptPath -ErrorAction SilentlyContinue -ErrorVariable errors   
    foreach($error in $errors)   
    {   
     if ($error.Exception -ne $null)   
     {   
      $ErrorOccured = $true   
      Write-Host -ForegroundColor Red "Exception: $($error.Exception)"   
     }   
    }   
   }   
  } -UseTransaction    
  if ($ErrorOccured)   
  {   
   Undo-Transaction   
   throw "Error occured while Executing SQL Scripts."   
  }   
  else   
  {   
   Complete-Transaction   
   Write-Host Successfully executed all SQL scripts.   
  }  

Next create a tool in Release Management Client as shown below.

a1

Parameters are

NameTypeDescription
ServerInstanceStandardSQL Server Name with Instance Name
DatabaseNameStandardName of the database the scripts should run
ScriptPathStandardLocation of the scripts to be executed


Create an action as shown below using the tool created above.

a2

This action can be used in a release template as shown below to execute SQL scripts in a transaction.

a3



On failure scripts actions will not be committed and release management action will fail. In Part 2, I will show this tool in action.

4 comments:

s tameem ansari said...

Good Post..

ScriptPath is not updated after executing first query.First query File name and second query file name is appended to ScriptPath before executing second query..resulting in Path not found Exception...

use this way

Write-Host Executing:$file.name ...
$Path = $ScriptPath
$FullPath =$Path+ "\"+$file.name
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -InputFile $FullPath -ErrorAction SilentlyContinue -ErrorVariable errors

Mike Lintro said...

Being a DBA seems like it would be a lot of work. I am going to school to be a network admin, so I think I will end up doing a lot of database administration at times of my life. I don't know too much about SQL, but I still have a lot of time to learn. I think I have a class on it next semester. I will make sure to have everything backed up. It would be terrible to accidentally delete an entire database. http://www.datasparc.com

Prakash Mishra said...

Thank you,
It's really a good post. I have some queries regarding the Scope of Release Management Tool.
1. How and Where can we better utilize RM for our Deployment Process ?
2. Is it Good for the Deployment of Normal Installers (.exe and .msi file extensions), so far I can only find tutorials for deploying Website ? I wants to use to deploy more than 20 Installers at once using RM.
3. Are there any ways to improve the Deployment and Configuration of the (.msi and .exe files) ?
4. Where can I find good tutorials for RM.

Piyush G said...

Thank you! This works for me after small change as below:

If you are getting Error: The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, s
cript file, or operable program.

Please add command as:

Import-Module "sqlps" -DisableNameChecking

This will solve your problem..