SQL Compare and MSBuild

agolovan

I am new to this forum. I found an article "Red Gate MSBuild Tasks " explaining of how to run SQL Compare tasks from MSBUILD using C# code. However, I would like to do same as it is explaining in another article, "NAnt tasks for Red Gate SQL Compare", but just for MSBuild, not for NAnt. I need "MSBuild for SQL Compare" using configuration file and not C# (we are using Cruise Control NET and MSBuild). Any help will be really appreciated.

bhopenw

I am unaware of the articles you talk about but I would believe that the easiest way to call SQL Compare via MSBuild would be to use the <EXEC> task (http://msdn.microsoft.com/en-us/library/x8zx72cd.aspx) to call the SQLCompare.exe (need SQL Compare Pro). Furthermore, I would utilize the arguments xml (http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Compare&c=SQL_Compare/help/7.0/sc_cl_usingxmltospecifyclarguments.htm&toc=SQL_Compare/help/7.0/toc356448.htm) when calling SQLCompare.exe

With that said, I would take it one step further and call powershell via EXEC task to make the call to SQLCompare.exe, this way you have power of powershell to basically do what ever you want. Powerhsell and MSBuild work great together and are much quicker to get working than using the ITask class via C# to write a custom task. Plus your scripts are not coupled with MSBuild when using powershell as they are when using C# and ITask.

Let me know if this help or if you have any other questions
I can even post the powershell script I use to call SQLCompare.exe


BJHop
MCTS-TFS

KevDog

Yes, please post the powershell script. That would be very helpful.

bhopenw

Okay this some what pieced together b/c my scripts run on a platform of scripts we have developed and would not make a whole lot o sense if I posted them stright up

Below is the TeamBuild targets that could be used

we use team build to set up the workspace where the scripts folder is

we also have argument xml checked and this is what we call from the cmd line, makes it easy and we have a record of what is being called

<Target Name="AfterBuildDrop" Condition="$(SyncDB) == 'true'">

	<AddBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepText="Search DB_ Dev for latest HRLYCLDL Scripts" />
	
	<Exec Command="powershell -c ".\BuildScripts\DB_Sync.ps1 '$(SolutionRoot)' '$(DropLocation)'"" WorkingDirectory="$(PowerShellScripts)" IgnoreExitCode="true">
		<Output TaskParameter="ExitCode" PropertyName="DB_ExitCode" />
	</Exec>
	
	<Error Condition="$(DB_ExitCode)==2" Text="There was an unhandle exception in the DB Sync script " />
	
	<Error Condition="$(DB_ExitCode)==1" Text="General error code." />
	
	<Error Condition="$(DB_ExitCode)==8" Text="Unsatisfied argument dependency or violated exclusion when user runs command line. E.g. /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or alternatively /arg2 cannot be used with /arg1 but you have tried to use them both." />
	
	<!-- should have all the Red Gate error msg catches here

	<UpdateBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepResult="true" />
	
	<OnError ExecuteTargets="OnErrorDB_Sync"/>
</Target>

<Target Name="OnErrorDB_Sync" >
	<UpdateBuildStep BuildUri="$(BuildUri)" BuildStepName="DB_Sync" BuildStepResult="false" />
</Target>

Now here is the DB_Sync.ps1

param($slnRoot = $(throw 'The snl root path is needed'),
      $dropLoc = $(throw 'The Build Drop location is required'))

trap
{
	$host.SetShouldExit(2)
	return $false
}

function get-ErrorMsg ([int] $exitCode)
{

# These messages were copied from the SQLCompare.exe -help section 
#if SQL Compare is updated need to ensure that the below holds true

	[string] $errMsg = "" 
	
	switch ($exitCode)
	{ 
		1 { $errMsg = "General error code." }
		3 { $errMsg = "Illegal argument duplication. Some arguments may not appear more than once in a command-line. If such arguments appear more than once this exit code will be returned." }
		8 { $errMsg = "Unsatisfied argument dependency or violated exclusion when user runs command line. E.g. /arg2 depends on /arg1 but you have specified /arg2 without specifying /arg1, or alternatively /arg2 cannot be used with /arg1 but you have tried to use them both." }
		32 { $errMsg = "Value out of range. Numeric value supplied for an argument that is outside the range of valid values for that argument." }
		33 { $errMsg = "Value overflow. The magnitude of a value supplied for an argument is too large and causes an overflow." }
		34 { $errMsg = "Invalid value. The value supplied for an argument is invalid." }
		35 { $errMsg = "No / invalid software license or trial period has expired." }
		63 { $errMsg = "The databases being compared are identical with respect to any constraints on objects being compared." }
		64 { $errMsg = "General command-line usage error." }
		65 { $errMsg = "Data error. Some input data required by the tool is invalid or corrupt." }
		69 { $errMsg = "A resource or service required to run the tool is unavailable." }
		73 { $errMsg = "Failed to create report" }
		74 { $errMsg = "IO error occurred. Generally returned if the program attempts to write to a file that already exists without the user having specified the /force option." }
		77 { $errMsg = "Action cannot be completed because the user does not have permission." }
		126 { $errMsg = "Execution failed because of an error." }
		130 { $errMsg = "Execution stopped because Ctrl+Break." }

	}
	
	return $errMsg
}



###MAIN###

#set XML args paths 
$sqlCompareArgsPath_sync = Join-Path $slnRoot "\XmlArgs\Sync.xml"

#set output area
$outputResults = Join-Path $dropLoc "SyncResults.log"

pushd "C:\Program Files\Red Gate\SQL Compare 8"
		
#run sync job

$rs = .\sqlcompare.exe /argfile:"$($sqlCompareArgsPath_Sync)"

#catch exit code
[int] $sqlExitCode = $lastExitCode

$rs | Out-File -FilePath $outputResults -Force -Append

if ($sqlExitCode -ne 0)
{
	[string] $errMsg = get-ErrorMsg $sqlExitCode
	write-error $errMsg
	$host.SetShouldExit($sqlExitCode)
}


popd 

This has been simplified but is should work

I hope this helps

Thanks,

BJHop