SQL Changeset setup

bhopenw

Hello,

Is there a way to automatically setup/register workspaces in SQL Changeset.

Ideally, we would like to save a project (.scp) file that compares a Dev DB to the Latest Scripts folder. Add this to soruce control, then when a Dev a has a change he/she can just open of the project and way he/she goes. If the workspace and code is not local it would create it.

While I believe this is not possible, I would like to have a script that I could run that would update the ChangeSet registry, then that Dev could open up the project. This way a dev would only have to run my script to set up the workspace for the project and not have going in SQL Changeset.

Basically, I am looking for a way to have some consentance for Developers, and avoid them have to setup in SQL Changeset all the time. I understand it is a one time thing but for some reason people are have issue with this set up.

Thanks,

BJHop

bhopenw

Okay,

It looks this is possible and it shouldn't be all that difficult using powershell.

We use SQL Changeset (SQLCS) to connect to TFS 2005 sp1

When SQL Changeset first starts up it adds some elements to the users local Application Data folder (C:\Documents and Settings\bhopenw\Local Settings\Application Data\Red Gate\SQL Changeset). In this directory, there is a xml file named configuration.xml, this is the file use by SQLCS to registery the TFS/SCCI workspaces.

With that fact found it is truly a simple powershell script to get this working. below is the workflow, I have not finished up the script yet but when I do I will post it.

* Ensure SQLCS has been started at least once, need to have local app Data section set, if not start it. * Ensure SQLCS is not running, if it is prompt to stop or kill it * Create workspace with mappings set from parameters/config file * Add <PairOfScProviderOpenProjectInfo> node to configuration.xml * start SQLCS

Where I see this working is we are going to have a common SCP and this script will set up the local environment (workspace) for the developers.

Thanks,

BJHop

bhopenw

As I said
here is the powershell code I created to get the job done
It will update sql changeset with a new workspace to poll

I believe it will help us enfore local standards to help work with a local scp file

This script got a bit more complex than I first thought but I think it works well

enjoy
Please let me know if you have any comments or questions

#Set-SQLChangeset
#this script set up SQL Changeset for use with a SCP and local workspace
param([string] $serverPath = $(throw 'The TFVC server path to DB schema is required'),
      [string] $localPath = $(throw 'The local path to workspace is required'),
	  [string] $tfsUrl = $('http://as73tfs01:8080'))


###MAIN###
$ErrorActionPreference = "Stop"

write-debug "Starting"

#using 
[void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")
[void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.VersionControl.Client")

#set variables 
$tfs = New-Object Microsoft.TeamFoundation.Client.TeamFoundationServer($tfsUrl)
$vcs = $tfs.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer])
$wsCurrent = [Microsoft.TeamFoundation.VersionControl.Client.Workstation]::Current

#may push to this to config file 
[string] $wsName = "SQLChangeset_" + $(Split-Path $localPath -leaf)
[string] $path2ConfigXml = "C:\Documents and Settings\$($env:USERNAME)\Local Settings\Application Data\Red Gate\SQL Changeset\Configuration.xml"
[string] $path2sqlCs = "C:\Program Files\Red Gate\SQL Compare 8\SQL Changeset\RedGate.SqlChangeset.exe"
[string] $user = $Env:USERDOMAIN + "\" + $Env:USERNAME

if (-not (Test-Path $path2ConfigXml))
{
	#SQL Changeset has not been started for first time
	
	Write-warning "Starting SQL Changeset for first time" 
	Write-Warning "Please close once started"
	
	& $path2sqlCs
	
}

#we need to be sure that SQL CS is not running 
#if it is we need to find out what to do 

while ($(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'})) 
{
	Write-Host "SQL Changeset cannot be running while this process is completed"
	
	Write-Host "To exit right-click on the SQL ChangeSet icon in the notification"
	
	Write-Host "area  in the right hand side of the status bar where the clock usually is"
		
	switch -regex (Read-Host -Prompt "[R] Retry, [K] Kill the process, [A] Abort")
	{
		"R|r"{}
		"A|a"
		{
			write-host -ForegroundColor Red "Abort: Cannot continue with Red Gate SQL Changeset running"
			return
		}
		"K|k"
		{
			Write-Warning "Killing the process may have unkown effects on SQLChangeset"
			switch -regex (Read-Host -Prompt "Are you sure you want to kill the SQL Changeset process [Y/N]")
			{
				"Y|y" 
				{
					$sqlcs = $(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'})
			
					$sqlcs.Kill()
			
					sleep -Seconds 5
				}
				
				"N|n"
				{}
			}
			
		}
	}
}



if (-not (Test-Path $localPath))
{
	New-Item -Path $(Split-Path $localPath -Parent) -Name $(Split-Path $localPath -leaf) -ItemType Directory | Out-Null
}
else
{

	if ($wsCurrent.IsMapped($localPath))
	{
		Write-Host -ForegroundColor Red "Error: Local path already mapped; $localPath is already mapped on this workstation" 
		
		return
		
	}
	
	
}

#check to ensure a workspace with same name not already created 
if($wsCurrent.GetAllLocalWorkspaceInfo() | ?{$_.Name -match $wsName})
{
	Write-Host -ForegroundColor Red "Error: workspace name - $wsName - already in use"
	Write-Host "Workspace name is taken for the local path directory name"
	return
}

##assume directy name is a good name ie DB name
$ws = $vcs.createworkspace($wsName)

$ws.map($serverPath, $localPath)

Write-Debug "Workspace created"

[System.IO.FileInfo] $configXml = Get-Item $path2ConfigXml

Copy-Item -Path $configXml.FullName -Destination $($configXml.FullName + ".bak") -Force

[string] $configXmlContents = gc $configXml
#check to make sure the server path has not already been used 
Write-Debug "configuration xml backed up"

#search 
if ($configXmlContents -match "\$serverPath")
{
	Write-Host -ForegroundColor Red "Error: $serverPath already in use in SQL Changeset"
	Write-Host "Workspace will be deleted"
	$ws.delete()
	return
}

[xml] $xml = [xml] (gc $configXml)

$projects = $xml.ConfigurationFile.SelectSingleNode("Projects")

#need to crate xml elements 
[System.Xml.XmlElement] $pair = $xml.CreateElement("PairOfScProviderOpenProjectInfo")
[System.Xml.XmlElement] $scp = $xml.CreateElement("ScProvider")
[System.Xml.XmlElement] $scpC = $xml.CreateElement("ScProvider")
[System.Xml.XmlElement] $opi = $xml.CreateElement("OpenProjectInfo")
[System.Xml.XmlElement] $opiC = $xml.CreateElement("OpenProjectInfo")

$scpC.SetAttribute("Name", "Team Foundation Server MSSCCI Provider")
$scpC.SetAttribute("Registry", "SOFTWARE\Microsoft\Team Foundation Server MSSCCI Provider")
$scpC.SetAttribute("Dll", "C:\Program Files\Microsoft Team Foundation Server MSSCCI Provider\TfsMsscciProvider.dll")

$scp.AppendChild($scpC)

Write-Debug "Updating configuration xml"

#This is the path SQL CS uses 
#I think the info it needs to get the workspace object 
[string] $wsPath = "$tfsUrl/|$wsName|$user"

$opiC.SetAttribute("LocalPath", $localPath)
$opiC.SetAttribute("Name", $serverPath)
$opiC.SetAttribute("Path", $wsPath)
$opiC.SetAttribute("User", $user)

$opi.AppendChild($opiC)

$pair.AppendChild($scp)
$pair.AppendChild($opi)

$projects.AppendChild($pair)

$xml.Save($configXml.FullName)

Write-Host "SQL Changeset has been successfully updated"
Write-Host "Starting SQL Changeset"
& $path2sqlCs