Database Continuous Integration – DeployTest

by JBrooks 23. February 2017 15:44

Part 4 of 4 – Main PowerShell Script

You can download all of the code here. Below is the main PowerShell script called Process-InBasket.ps1.  It might be scheduled to run every few minutes. It will look for new commands and if any it will call OneScriptClient passing in the parameters found in the command.

Then it will run if it finds a new *.sql file in the In-Basket directory. It will restore a resent backup of production, apply the script and then email the developers with “Success” or “Failed” in the subject. The body of the email will be the log and attached will be a file with the results of the script running.

<# .SYNOPSIS Process new SQL Scripts files by applying them to a SQL database. .DESCRIPTION Process-InBasket loads a Config file that controls all variables in the process. It then looks to a directory to search for new *.SQL files. Once a new file is found, a backup of the database is restored to SQL Server. The script is then applied. The process then looks for an SQL errors. Then it sends an email of the log with "Success" or "Failure" in the subject. .PARAMETER ConfigFile The config file to use to process the deployment. Default: ./TestDeployConfig.confg .EXAMPLE PowerShell "C:\Work\Deploy\Process-InBasket.ps1" ./TestDeployConfig.xml #> [CmdletBinding()] param ( [string] $ConfigFile='./TestDeployConfig.xml' ) cls $VerbosePreference = 'Continue' Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass # get a temp file name so we can start logging $Private:logFileName = [IO.Path]::GetTempFileName() Set-Location $PSScriptRoot # load the support scripts . ".\DeployUtils\Load-DeployConfig.ps1" . ".\DeployUtils\SQLServer.ps1" . ".\DeployUtils\Utils.ps1" Log "Root: $PSScriptRoot" $logFileName Log ("Loading Config File $ConfigFile") -LogFileName $logFileName #This will also create and load C.FileSettings.LogFileName $Config = Load -ConfigFileName $ConfigFile -LogFileName $logFileName Log "" Log ("Processing Matches: "+ $Config.C.FileSettings.InFileMask.InnerText) $Private:processCount = 0 $Private:ret; foreach($Private:CmdFile in (Get-ChildItem $Config.C.DirSettings.InCmdDir.InnerText)) { $content = Get-Content $CmdFile.FullName $cmdArgs = $content[0] $author = $content[1] $cmd = '"C:\Program Files\DigitalTools\OneScript Client\OneScriptCMD.exe" ' + ` $cmdArgs + ' -do ' + $Config.C.DirSettings.InBasketDir.InnerText Invoke-Expression "& $cmd" Move-Item -Path "$($CmdFile.FullName)" -Destination ` $Config.C.DirSettings.ArchiveDir.InnerText -Force } foreach($Private:SQLFile in ` (Get-ChildItem $Config.C.FileSettings.InFileMask.InnerText)) { Log "" Log ("Processing: "+ $SQLFile) $Private:dt = Get-Date -Format yyyyMMdd_HHmmss $Private:temp = $Config.C.FileSettings.LogFileFormat.InnerText.Replace("{0}", $dt) if($processCount -eq 0) { Log 'Changing log file to $temp' Move-Item -Path $logFileName -Destination $temp $Config.C.FileSettings.LogFileName = $temp $Private:DBSourceFile = Get-ChildItem ` $Config.C.FileSettings.DBSourceFileMask.InnerText | ` Sort-Object Name -Descending | Select-Object $_.Name -First 1 Log ("SQL Backup File: " + $DBSourceFile) if(!$DBSourceFile) { Write-Error ("Searched: " + ` $Config.C.FileSettings.DBSourceFileMask.InnerText) Write-Error "No SQL Backup File Found." Log "No SQL Backup File Found." Send-ToEmail -Subject "Deploy Failed!, No SQL Backup File Found" ` -LogFileName $Config.C.FileSettings.LogFileName exit -1 } } else { $Config.C.FileSettings.LogFileName = $temp Log(("Processing file number: " + ($processCount + 1).ToString())) Log ("") } Log ("Log File: " + $Config.C.FileSettings.LogFileName) $Private:buildResultsFile = ` $Config.C.FileSettings.BuildResultsFileFormat.InnerText.Replace("{0}", $dt) Log ("Build Results File: " + $buildResultsFile) $ret = RestoreSQLServer $Config.C.DBSettings.Server $Config.C.DBSettings.Database ` $Config.C.DirSettings.DBFilesDir.InnerText $DBSourceFile if($ret -ne $true) { Write-Error "Restore Database Failed" Log "Restore Database Failed" Send-ToEmail -Subject "Deploy Failed!, Restore Database Failed" -LogFileName ` $Config.C.FileSettings.LogFileName exit -1 } Log ("Running: "+ $SQLFile) $ret = RunSQLFile $Config.C.DBSettings.Server $Config.C.DBSettings.Database ` $SQLFile $buildResultsFile if($ret -ne $true) { Write-Error "Running SQL Script Failed" Log "Running SQL Script Failed" Send-ToEmail -Subject "Deploy Failed!, Running SQL Script Failed" ` -LogFileName $Config.C.FileSettings.LogFileName exit -1 } Log "SQL Script Run Successful" $Private:hasError = CheckForErrors $buildResultsFile if($hasError -eq $true) { Write-Error "SQL Script Had Errors" Log "SQL Script Had Errors" Send-ToEmail -Subject "Deploy Failed!, SQL Script Had Errors" ` -LogFileName $Config.C.FileSettings.LogFileName ` -Attachmentpath $buildResultsFile } else { Log (("Deployment Successful")) Log (("Completed " + $SQLFile)) Send-ToEmail -Subject "Deploy Script Successful" -LogFileName ` $Config.C.FileSettings.LogFileName -Attachmentpath "$buildResultsFile"; } Move-Item -Path "$buildResultsFile" -Destination ` $Config.C.DirSettings.ArchiveDir.InnerText -Force $SQLFile = $SQLFile -replace ".sql$", ".*" Move-Item -Path "$SQLFile" -Destination ` $Config.C.DirSettings.ArchiveDir.InnerText -Force $processCount = $processCount + 1 } switch($processCount) { 0 {Log (("No files found to process"))} 1 {Log (("1 Script Processed"))} default {Log (($processCount.ToString() + " Scripts Processed"))} }


PowerShell | Continuous Integration | Development | OneScript

Database Continuous Integration – DeployTest

by JBrooks 17. February 2017 17:47

Part 3 of 4 - Directories and Configuration

Below are the default directories I use. These could all be under c:\DeployTest or something.
Directory Type of Files
Archive Completed files are moved to once processed
DatabaseFiles SQL Server database data files
DBBackup Resent backups of production for restoring
DeployUtils PowerShell support utilities
InBasket Incomming files needing to be processed
InCmds Incomming commands used to trigger processing
Logs Log files
Working Process working files

I didn't want to hard code the directory paths because I could make changes like moving the Archive directory to a shared drive like "\\MyShare\MyProject\Archives". I will also have multiple project using my PowerShell code so having config files makes it easier to work with.

The config file for the directories above looks like:

<?xml version="1.0" encoding="UTF-8" ?> <C> <DirSettings> <BaseDir>C:\DeployTest\</BaseDir> <InBasketDir Parent="BaseDir">InBasket\</InBasketDir> <WorkingDir Parent="BaseDir">Working\</WorkingDir> <DBSourceDir Parent="BaseDir">DBBackup\</DBSourceDir> <ArchiveDir Parent="BaseDir">Archive\</ArchiveDir> <DBFilesDir Parent="BaseDir">DatabaseFiles\</DBFilesDir> <InCmdDir Parent="BaseDir">InCmds\</InCmdDir> <LogDir Parent="BaseDir">Logs\</LogDir> </DirSettings> <FileSettings> <InFileMask Parent="InBasketDir">Northwind_Update_*.sql</InFileMask> <DBSourceFileMask Parent="DBSourceDir">Northwind_*.bak</DBSourceFileMask> <BuildResultsFileFormat Parent="LogDir">BuildResults_{0}.log</BuildResultsFileFormat> <LogFileFormat Parent="LogDir">TestDeploy_{0}.log</LogFileFormat> </FileSettings> <DBSettings> <Server>.</Server> <Database>Northwind</Database> <BackupDB>0</BackupDB> </DBSettings> <EmailSettings> <SendEmails>1</SendEmails> <SMTPServer></SMTPServer> <SMTPPort>587</SMTPPort> <UseSsl>1</UseSsl> <SMTPUserName></SMTPUserName> <SMTPPassword>Someday13!</SMTPPassword> <From></From> <To></To> </EmailSettings> </C>

Typically you would have one of these per project. This might be called something like NorthwindDeployTest.xml. Notice that there is a "BaseDir" element and then other directories are a relative path under it. They have an attribute Parent="BaseDir". This can be removed if you want to use a different directory not under the BaseDir.

So our example where archives were moved to a shared drive would change:

<ArchiveDir Parent="BaseDir">Archive\</ArchiveDir>



Now all that we need is a PowerShell script to read this XML config file and then process it. One of the files under DeployUtils is Load-DeployConfig.ps1 and that is what it does.

Function Load { param ( [string] $ConfigFileName = 'C:\Work\Deploy\TestDeployConfig.xml', [string] $LogFileName ) Write-Host "this is it: $LogFileName" # Do not make $Config private, it is used by Log() Log ("Loading Config File $ConfigFileName") -LogFileName $LogFileName [xml]$Config = Get-Content $ConfigFileName Log 'Adding LogFileName to Config' $LogFileName $logFileNode = $Config.SelectSingleNode('//C/FileSettings/LogFileName') if(!$logFileNode) { $Config.c.FileSettings.AppendChild($Config.CreateElement("LogFileName")) } $Config.C.FileSettings.LogFileName = $LogFileName Write-Host "Loaded: $($Config.C.FileSettings.LogFileName)" $LogFileName #Now do not need the optional LogFileName since it defaults to $Config.C.FileSettings.LogFileName Log "" Log "Process Config File" #fill in the parent for all of the elements with a Parent attribute foreach($setting in $Config.SelectNodes("//*[@Parent]")) { Log "$($setting.InnerText) ==>" $parent = $Config.C.DirSettings.SelectSingleNode("//" + $setting.Parent) # if($parent -ne $null) $setting.InnerText = $parent.InnerText + $setting.InnerText Log "`t`t$($setting.InnerText)" } Log "" return $Config }

In your main PowerShell script you would use this to load the config like:

$Config = Load -ConfigFileName $ConfigFile -LogFileName $logFileName

Then you can use the variables like:

Move-Item -Path "$SQLFile" -Destination $Config.C.DirSettings.ArchiveDir.InnerText


Development | OneScript | PowerShell | Continuous Integration

Database Continuous Integration

by JBrooks 27. January 2017 16:23


We are pleased to announcing the release of our product OneScript!  It automates the building of a SQL release script from individual SQL change scripts. This allows you to include database changes as a part of your continuous integration process.

Who is this for? Developers that work on applications where the contents of the database needs to be preserved. It is simple to use and integrates seamlessly into the development process. Developers continue to use the tools and practices that they already use.

It works by following these steps:

  1. Developers script out each database change during a development cycle and check them into source control (Examples: SVN – Subversion, TFS – Team Foundation Server.)
  2. The change scripts are each checked into different folders based on the type of database object that they change. Example folder names are: Tables, Views, Store Procs, Static Data, etc.
  3. Within OneScript (one-time setup) each folder name is assigned a sort order. So something like Tables = 100, Functions = 200, Views = 300, Store Procs = 400, etc.
  4. For a release a filter is defined. The logic usually takes the form of “scripts changed since a given date/time” or “include every change since the branch was created” (if you use branches.)
  5. Optionally, at the individual file level you can override its normal sort order or filter.
  6. When it does a build it will filter, sort and combine all of the individual change scripts into a single release script. You can do a build from the command-line!

We built OneScript because we have tried many other approaches including:

  • Keep a single big change script in source control that the developers keep adding to for a release.
  • Give change script files a name that starts with the next available number (0044_, 0045_, etc.) so they are combined in order into single release script at build time.
  • Do a “diff” between Development and Production databases and then script out the differences. Then hand edit it to remove anything not wanted. Separately, add our static data scripts.
  • Email change scripts to the tech lead.

Each one of these approaches suffered from one or more of the following problems:

  • Quickly becomes unmanageable.
  • Last one in wins for changes to the same object.
  • Conflicts are hidden until release time.
  • Undoing a change is a manual and tedious process.
  • No history or auditability of changes.
  • Doesn’t lend itself to automation.

To learn more visit our site



Development | SQL | OneScript

SQL Server 2005 to 2012 Differences for Developers

by JBrooks 14. November 2015 12:24

We are upgrading from SQL Server 2005 to 2012. There are a lot of features for developers that have been added over those 7 years.  Here are some you might find useful.

1. You can assign a value to a variable when you declare it.

DECLARE @Status varchar(50) = 'Success', @Today Date = Getdate()

2. You can insert multiple rows without needing to repeat the column list

INSERT INTO MyTable ( ID, LastName ) VALUES ( 100, 'Smith' ), ( 200, 'Jones' ), ( 300, 'Watson' )

(Up to 1,000 sets.)

3. Among the new data type there are DATE and TIME types.

No more having to use a DateTime when all that you really want is a date.

DECLARE @Today Date = Getdate()

4. IIF() function

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )


You can use the IIF() with an IN

DECLARE @LastName Varchar(50) = 'Smith' SELECT IIF(@LastName IN ('Smith','Jones'), 'Likes SQL', 'Likes ORACLE' )


5. CHOOSE() function

CHOOSE ( index, val_1, val_2 [, val_n ] ) SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')

6. CONCAT() function

CONCAT( string1, string2 [,stringN])

- All arguments are converted to a string first

- All nulls are converted to an empty string

DECLARE @FirstName VARCHAR(20) = 'Bob', @LastName VARCHAR(20), @Age int = 30 SELECT CONCAT(@FirstName, ' ', @LastName, ' is ', @Age)

7. You have the “C” like short cuts +=, -=, *=, /= and %=

DEClARE @x INT = 2, @y INT = 2 SET @x += @y SELECT @x as x, @y as y


8. Paging is now much easier with OFFSET and FETCH NEXT

DECLARE @PageNumber int = 4, @PageSize int = 40 SELECT WNumber, AuditTime, AuditTypeCode, ActivityCode FROM tb_AuditLog ORDER BY AuditTime DESC OFFSET (@PageNumber1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

9. FORMAT() function

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result', FORMAT(123456789,'###-##-####') AS 'Custom Number Result';

10. TRY_PARSE()and TRY_CONVERT() functions

IF TRY_PARSE('xxx' as DATETIME) IS NOT NULL SELECT 'TRY_PARSE: Conversion Successful' ELSE SELECT 'TRY_PARSE: Conversion Unsuccessful'

11. You can now create your own types in the database (just like VARCHAR and INT are types.)  You can even define a table type.


(Could have many more columns.)

You can then pass that type as a parameter to a stored procedure.


You can also pass the table in as a parameter from C#.

DataTable myIds = new DataTable(); myIds.Columns.Add("Id", typeof(Int32)); myIds.Rows.Add(100); myIds.Rows.Add(110); myIds.Rows.Add(2200); myIds.Rows.Add(40); dbCommand.Parameters.Add(new SqlParameter("@MyIds", myIds) { SqlDbType = SqlDbType.Structured }); DataSet ds = (DataSet) Db.ExecuteDataSet(dbCommand);


When you re-throw it keeps the correct line number of the error.

This can also be used with transactions.


13. New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents

UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created <20100101';

14. Reporting Services no longer requires IIS, it makes direct calls to HTTP.SYS.

15. Now have Oracle like sequences

CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Steve Luna'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Jim Brooks');


Development | Silverlight

Generating Indexes for Foreign Keys

by jbrooks 18. September 2014 09:03

Surprisingly SQL Server doesn’t automatically create indexes just because you have a foreign key. So at the end of a development project I just used the script below to generate the SQL needed.

select 'create index indx_' +'_' + + ' on ' + +'(' + + ');' from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where not exists (select 1 from sys.index_columns ic where ic.object_id = t.object_id and ic.column_id = c.column_id) order by 1

This returns the commands that I can then run to create the indexes.

create index indx_ActivityLog_activityTypeId on ActivityLog(activityTypeId);
create index indx_ActivityLog_userId on ActivityLog(userId);
create index indx_Areas_managerId on Areas(managerId);
create index indx_Categories_areaId on Categories(areaId);
create index indx_CriteriaItems_criteriaColorId on CriteriaItems(criteriaColorId);


SQL | Development

Silverlight Page Titles

by jbrooks 9. September 2013 17:21

In the past I would use part of the page for the page title while the MainPage had a lot of empty space in the center.  I wanted to move the title from the page to the MainPage as shown below.



To do this I first made a property on MainPage like this:

public partial class MainPage : UserControl
    public string Title
        get { return tbTitle.Text; }
        set { tbTitle.Text = value; }

Then I added this property to the App class:

public partial class App : Application
    public static string Title
            MainPage mp = App.GetMainPage();
            mp.Title = value;

A supporting method in this same App class is GetMainPage() where it gets the MainPage. I use this for other things too.

public static MainPage GetMainPage()
    if (Application.Current.RootVisual.GetType().Name == "BusyIndicator")
        return (MainPage)((ChangeAlerts.Controls.BusyIndicator)
        return (MainPage)Application.Current.RootVisual;

Then on each individual page I have something like this:

protected override void OnNavigatedTo(NavigationEventArgs e)
    App.Title = "Manage Accounts";


Silverlight | Development

Binding a Silverlight DataGrid’s ComboBox To a Model

by JBrooks 31. July 2013 11:49

I have a DataGrid that is bound to my Employee entity and I needed to be able to select his building from a list of buildings in that same model. There is a path to the Employee’s Building through buildingId, but no path to the full list of buildings so it could the source for the ComboBox.

The simple solution was to have the Buildings in a ViewModel and then reference that as a resource in my XAML.  The ViewModel retrieves the data in it’s constructor and it has a property EntitySet<Building> Buildings;

        <vm:BuildingsModel x:Key="buildingsModel"  />

Then on the CheckBox inside the <DataTemplate> I have its ItemSource use the resource.

<ComboBox Name="cb" ItemsSource="{Binding Source={StaticResource buildingsModel}, Path=Buildings}" 
    SelectedValue="{Binding Path=buildingId, Mode=TwoWay}">
            <StackPanel Orientation="Horizontal">
                <TextBlock Text="{Binding name, Mode=OneWay}" MinWidth="140" />
                <TextBlock Text="  " />
                <TextBlock Text="{Binding address, Mode=OneWay}" />
                <TextBlock Text=", " />

                <TextBlock Text="{Binding city, Mode=OneWay}" />
                <TextBlock Text=", " />
                <TextBlock Text="{Binding state, Mode=OneWay}" />


Development | Silverlight | XAML

Silverlight and Window Authentication

by JBrooks 4. June 2013 08:38

I deployed my Silverlight application with Windows Authentication.  I then set IIS to only use Windows Authentication. When I navigated to the web site I would get the error:

“Load operation failed for query ‘GetUser’. The remote server returned an error: NotFound.”


The fix that worked for me was to edit the file MyProject.Web/Services/AuthenticationService.cs.  I just changed this one method.

public class AuthenticationService : AuthenticationBase<User> {

    protected override User GetAuthenticatedUser(System.Security.Principal.IPrincipal principal)
        User newUser = new User();
        newUser.Name = principal.Identity.Name;

        return newUser;

This makes sense if you think about it, but the base class AuthenticationBase should have detected the authentication used and changed based on that.


Development | Silverlight

Show Child Count in a DataGrid

by jbrooks 11. March 2013 17:49

I have an application with the following as part of the data model:


So an Account can have many Users and a user can belong to many Accounts. I wanted to show the Accounts in a DataGrid along with the number of Users that were linked to that account like show in the 3rd column here:


The way I did this is to use a converter to count the number of entities in each Account.xrefAccountsUser set.  The DataGrid is bound to Accounts and the the XAML for the 3rd column is:

<sdk:DataGridTextColumn  Header="Users" 
Binding="{Binding xrefAccountsUsers, Converter={StaticResource setToCountConverter}}"

The converter is where we count up the number of entities in the related entity set (xrefAccountsUsers in this case.)  I could not find a type that I could cast the value to that had a Count property, so I  ended up casting it to IEnumerable and then loop thru the set and I do a count manually.  There should never be very many accounts so performance isn’t an issue.

public class SetToCountConverter : IValueConverter
    public object Convert(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        int cnt = 0;
        IEnumerable set = value as IEnumerable;
        if (set == null)
            return "0";
        foreach (var item in set)
        return cnt.ToString();
    public object ConvertBack(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        throw new NotImplementedException();


Silverlight | Development | XAML

Disable CheckBoxes in Silverlight’s DataGrid

by jbrooks 11. March 2013 12:41

I have an application where the administrator can edit a user’s roles.


I wanted to reuse this same grid to show the user what roles they have, but I didn’t want them to be able to change the data. There is a IsReadOnly property on each of the columns, but this still allows the user to change the checkboxes.

If I set the IsEnabled property on the datagrid to false, then the user can’t change the checkboxes but the datagrid looks washed out and hard to read.


So this is too washed out to go with.  I realized that another way to disable CheckBoxes is from the style:

    <Style TargetType="CheckBox">
        <Setter Property="IsThreeState" Value="False" />
        <Setter Property="HorizontalAlignment" Value="Center" />
        <Setter Property="VerticalAlignment" Value="Center" />

I wanted to see if there was a way to programmatically add a setter to this and there was.

// find the CheckBox style resource.
var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(CheckBox));
if (resource.Key != null)
    Style s = resource.Value as Style;
    (s.Setters as SetterBaseCollection).Add(new
    Setter(CheckBox.IsEnabledProperty, value));

In the code above, “value” can be true or false.  This gives me a little better presentation:


But, hey, this is Silverlight – why am I using those dinky check boxes anyway.  The better solution is to create a user control that acts like a checkbox and you can style it as big as you want.


And then the code to set the disable style just needs to change the type it is looking for on the 1 line:

var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(ucCheckBox));

Now, the disabled version is a lot more readable.



Development | Silverlight | XAML