Posts Tagged ‘command line’

Getting Custom TFS Checkin Policies To Work When Committing From The Command Line (i.e. tf checkin)

September 6th, 2013 1 comment

Update – I show how to have your checkin policies automatically update the registry keys shown in this blog post on this newer blog post. If you are not the person creating the checkin policies though, then you will still need to use the technique shown in this post.

I frequently check code into TFS from the command line, instead of from Visual Studio (VS), for a number of reasons:

  1. I prefer the VS 2010 style of checkin window over the VS 2012 one, and the 2010 style window is still displayed when checking in from the command line.
  2. I use AutoHotkey to pop the checkin window via a keyboard shortcut, so I don’t need to have VS open to check files in (or navigate to the pending changes window within VS).
    – Aside: Just add this one line to your AutoHotkey script for this functionality. This sets the hotkey to Ctrl+Windows+C to pop the checkin window, but feel free to change it to something else.
    ^#C UP::Run, tf checkin
  3. Other programs, such as Git-Tf and the Windows Explorer shell extension, call the TFS checkin window via the command line, so you don’t have the option to use the VS checkin pending changes window.

          The Problem

        The problem is that if you are using a VSIX package to deploy your custom checkin policies, the custom checkin policies will only work when checking code in via the VS GUI, and not when doing it via the command line.  If you try and do it via the command line, the checkin window spits an “Internal error” for each custom checkin policy that you have, so your policies don’t run and you have to override them.

        P. Kelly mentions this problem on his blog post, and has some other great information around custom checkin policies in TFS.
        The old TFS 2010 Power Tools had a feature for automatically distributing the checkin policies to your team, but unfortunately this feature was removed from the TFS 2012 Power Tools.  Instead, the Microsoft recommended way to distribute your custom checkin policies is now through a VSIX package, which is nice because it can use the Extension And Updates functionality built into VS and automatically notify users of updates (without requiring users to install the TFS Power Tools).  The problem is that VSIX packages are sandboxed and are not able to update the necessary registry key to make custom checkin policies work from the command line.  I originally posted this question on the MSDN forums, then I logged a bug about this on the Connect site, but MS closed it as “By Design” Sad smile. Maybe if it gets enough up-votes though they will re-open it (so please go up-vote it).


      The Workaround

      The good news though is that there is a work around.  You simply need to copy your custom checkin policy entry from the key:

      "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0_Config\TeamFoundation\SourceControl\Checkin Policies"


      "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\11.0\TeamFoundation\SourceControl\Checkin Policies" (omit the Wow6432Node on 32-bit Windows).


      Not Perfect, but Better

      The bad news is that every developer (who uses the command line checkin window) will need to copy this registry value on their local machine.  Furthermore, they will need to do it every time they update their checkin policies to a new version.

      While this sucks, I’ve made it a bit better by creating a little powershell script to automate this task for you; here it is:

      # This script copies the required registry value so that the checkin policies will work when doing a TFS checkin from the command line.
      # Turn on Strict Mode to help catch syntax-related errors.
      # 	This must come after a script's/function's param section.
      # 	Forces a function to be the first non-comment code to appear in a PowerShell Module.
      Set-StrictMode -Version Latest
      $ScriptBlock = {
          # The name of the Custom Checkin Policy Entry in the Registry Key.
          $CustomCheckinPolicyEntryName = 'YourCustomCheckinPolicyEntryNameGoesHere'
          # Get the Registry Key Entry that holds the path to the Custom Checkin Policy Assembly.
          $CustomCheckinPolicyRegistryEntry = Get-ItemProperty -Path 'HKCU:\Software\Microsoft\VisualStudio\11.0_Config\TeamFoundation\SourceControl\Checkin Policies' -Name $CustomCheckinPolicyEntryName
          $CustomCheckinPolicyEntryValue = $CustomCheckinPolicyRegistryEntry.($CustomCheckinPolicyEntryName)
          # Create a new Registry Key Entry for the iQ Checkin Policy Assembly so they will work from the command line (as well as from Visual Studio).
          if ([Environment]::Is64BitOperatingSystem)
          { $HKLMKey = 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\11.0\TeamFoundation\SourceControl\Checkin Policies' }
          { $HKLMKey = 'HKLM:\SOFTWARE\Microsoft\VisualStudio\11.0\TeamFoundation\SourceControl\Checkin Policies' }
          Set-ItemProperty -Path $HKLMKey -Name $CustomCheckinPolicyEntryName -Value $CustomCheckinPolicyEntryValue
      # Run the script block as admin so it has permissions to modify the registry.
      Start-Process -FilePath PowerShell -Verb RunAs -ArgumentList "-Command $ScriptBlock"

      Note that you will need to update the script to change YourCustomCheckinPolicyEntryNameGoesHere to your specific entry’s name.  Also, the “[Environment]::Is64BitOperatingSystem” check requires PowerShell V3; if you have lower than PS V3 there are other ways to check if it is a 64-bit machine or not.

      If you have developers that aren’t familiar with how to run a PowerShell script, then you can include the following batch script (.cmd/.bat file extension) in the same directory as the PowerShell script, and they can run this instead by simply double-clicking it to call the PowerShell script:

      SET ThisScriptsDirectory=%~dp0
      SET PowerShellScriptPath=%ThisScriptsDirectory%UpdateCheckinPolicyInRegistry.ps1
      :: Run the powershell script to copy the registry key into other areas of the registry so that the custom checkin policies will work when checking in from the command line.
      PowerShell -NoProfile -ExecutionPolicy Bypass -Command "& '%PowerShellScriptPath%'"

      Note that this batch script assumes you named the PowerShell script “UpdateCheckinPolicyInRegistry.ps1”, so if you use a different file name be sure to update it here too.

      Your developers will still need to run this script every time after they update their checkin policies, but it’s easier and less error prone than manually editing the registry.  If they want to take it a step further they could even setup a Scheduled Task to run the script once a day or something, or even implement it as a Group Policy so it automatically happens for everyone, depending on how often your company updates their checkin policies and how many developers you have.

      Ideally I would like to simply be able to run this script during/after the VSIX installer.  I have posted a question on Stack Overflow to see if this is possible, but from everything I’ve read so far it doesn’t look like it; maybe in the next generation of VSIX though.  If you have any other ideas on how to automate this, I would love to hear them.

      Happy coding!

      Using MSBuild to publish a VS 2012 SSDT .sqlproj database project the same way as a VS 2010 .dbproj database project (using command line arguments to specify the database to publish to)

      March 18th, 2013 20 comments

      Post and code updated on March 21, 2013, and again on March 22, 2013.

      We recently upgraded from VS (Visual Studio) 2010 to VS 2012, and with it had to upgrade our .dbproj database project to a .sqlproj.  When making the switch I realized that .sqlproj database projects do not support specifying the database to deploy to as MSBuild command line arguments; instead you have to pass in the path to an xml file that has the necessary information.

      So with the old .dbproj database project, you could deploy it to a database using:

      MSBuild /t:Deploy /p:TargetDatabase="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:DeployToDatabase="True" "[PathToBranch]Database\Database.dbproj"

      But with the new .sqlproj database project you have to do:

      MSBuild /t:Publish /p:SqlPublishProfilePath="myPublishFile.publish.xml" "[PathToBranch]Database\Database.sqlproj"

      Where “myPublishFile.publish.xml” contains the database server and name to publish to.

      One other minor thing to note is that it is called “deploying” the database with .dbproj, and is called “publishing” the database with .sqlproj; so when I say Deploy or Publish, I mean the same thing.

      We use TFS at my organization and while making new builds for our Test environment, we have the build process deploy the database solution to our various Test databases.  This would mean that for us I would either need to:

      1 – create a new [DbName].publish.xml file for each database, check it into source control, and update the build template to know about the new file, or

      2 – update the file contents of our myPublishFile.publish.xml file dynamically during the build to replace the Database Name and Server in the file before publishing to the database (i.e. read in file contents, replace string, write file contents back to file, publish to DB, repeat).

      Option 1 means more work every time I want to add a new Test database to publish to.  Option 2 is better, but still means having to update my TF Build template and create a new activity to read/write the new contents to the file.

      Instead, there is a 3rd option, which is to simply add the code below to the bottom of the .sqlproj file.  This will add some new MSBuild targets to the .sqlproj that will allow us to specify the database name and connection string using similar MSBuild command line parameters that we used to deploy the .dbproj project.

      The code presented here is based on this post, but the author has closed the comments section on that post and has not replied to my emails about the bugs in his code and example, so I thought I would share my modified and enhanced solution.

      	Custom targets and properties added so that we can specify the database to publish to using command line parameters with VS 2012 .sqlproj projects, like we did with VS 2010 .dbproj projects.
      	This allows us to specify the MSBuild command-line parameters TargetDatabaseName, and TargetConnectionString when Publishing, and PublishToDatabase when Building.
      	I also stumbled across the undocumented parameter, PublishScriptFileName, which can be used to specify the generated sql script file name, just like DeployScriptFileName used to in VS 2010 .dbproj projects.
      	Taken from:
        <PropertyGroup Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
          <PublishToDatabase Condition="'$(PublishToDatabase)' == ''">False</PublishToDatabase>
          <TargetConnectionStringXml Condition="'$(TargetConnectionString)' != ''">
            &lt;TargetConnectionString xdt:Transform="Replace"&gt;$(TargetConnectionString)&lt;/TargetConnectionString&gt;
          <TargetDatabaseXml Condition="'$(TargetDatabaseName)' != ''">
            &lt;TargetDatabaseName xdt:Transform="Replace"&gt;$(TargetDatabaseName)&lt;/TargetDatabaseName&gt;
              &lt;Project xmlns:xdt="" xmlns=""&gt;
          <SqlPublishProfilePath Condition="'$([System.IO.Path]::IsPathRooted($(SqlPublishProfilePath)))' == 'False'">$(MSBuildProjectDirectory)\$(SqlPublishProfilePath)</SqlPublishProfilePath>
          <!-- In order to do a transform, we HAVE to change the SqlPublishProfilePath -->
          <TransformStackTraceEnabled Condition="'$(TransformStackTraceEnabled)'==''">False</TransformStackTraceEnabled>
        <Target Name="AfterBuild" Condition="'$(PublishToDatabase)'=='True'">
          <CallTarget Targets="Publish" />
        <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" />
        <Target Name="BeforePublish" Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
          <Message Text="TargetDatabaseName = '$(TargetDatabaseName)', TargetConnectionString = '$(TargetConnectionString)', PublishScriptFileName = '$(PublishScriptFileName)', Transformed Sql Publish Profile Path = '$(TransformOutputFile)'" Importance="high" />
          <!-- If TargetDatabaseName or TargetConnectionString, is passed in then we use the tokenize transform to create a parameterized sql publish file -->
          <Error Condition="!Exists($(SqlPublishProfilePath))" Text="The SqlPublishProfilePath '$(SqlPublishProfilePath)' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'" />
          <ParameterizeTransformXml Source="$(SqlPublishProfilePath)" IsSourceAFile="True" Transform="$(TransformPublishXml)" IsTransformAFile="False" Destination="$(TransformOutputFile)" IsDestinationAFile="True" Scope="$(TransformScope)" StackTrace="$(TransformStackTraceEnabled)" SourceRootPath="$(MSBuildProjectDirectory)" />


      So after adding this code at the bottom of the .sqlproj file (above the </Project> tag though), you can now build and publish the database solution from the MSBuild command line using:

      MSBuild /t:Build /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishToDatabase="True" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

      Here you can see the 3 new parameters that we’ve added being used: TargetDatabaseName, TargetConnectionString, and PublishToDatabase.

      When the TargetDatabaseName or TargetConnectionString parameters are provided we generated a new transformed .publish.xml file, which is the same as the provided “Template.publish.xml” file, but with the database and connection string values replaced with the provided values.

      The PublishToDatabase parameter allows us to publish to the database immediately after the project is built; without this you would have to first call MSBuild to Build the database project, and then call MSBuild again to Publish it (or perhaps using “/t:Build;Publish” would work, but I didn’t test that).

      If you want to simply publish the database project without building first (generally not recommended), you can do:

      MSBuild /t:Publish /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

      Be careful though, since if you don’t do a Build first, any changes that have been made since the last time the .sqlproj file was built on your machine won’t be published to the database.

      Notice that I still have to provide a path to the template publish.xml file to transform, and that the path to this file is relative to the .sqlproj file (in this example the Template.publish.xml and .sqlproj files are in the same directory).  You can simply use one of the publish.xml files generated by Visual Studio, and then the TargetDatabaseName and TargetConnectionString xml element values will be replaced with those given in the command line parameters.  This allows you to still define any other publish settings as usual in the xml file.

      Also notice that the PublishToDatabase parameter is only used when doing a Build, not a Publish; providing it when doing a Publish will not hurt anything though.

      While creating my solution, I also accidentally stumbled upon what seems to be an undocumented SSDT parameter, PublishScriptFileName.  While the DeployScriptFileName parameter could be used in VS 2010 .dbproj projects to change the name of the generated .sql file, I noticed that changing its value in the .publish.xml file didn’t seem to have any affect at all (so I’m not really sure why Visual Studio puts it in there).  I randomly decided to try passing in PublishScriptFileName from the command line, and blamo, it worked!  I tried changing the <DeployScriptFileName> element in the .publish.xml file to <PublishScriptFileName>, but it still didn’t seem to have any effect.

      So now if I wanted to deploy my database project to 3 separate databases, I could do so with the following code to first Build the project, and the Publish it to the 3 databases:

      MSBuild /t:Build "[PathToBranch]\Database\Database.sqlproj"
      MSBuild /t:Publish /p:TargetDatabaseName="[DbName1]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName1].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
      MSBuild /t:Publish /p:TargetDatabaseName="[DbName2]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName2].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
      MSBuild /t:Publish /p:TargetDatabaseName="[DbName3]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName3].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

      You could also instead just call MSBuild using the Build target with the PublishToDatabase parameter (which might actually be the safer bet); whatever you prefer.  I have found that once the database project is built once, as long as no changes are made to it then subsequent “builds” of the project only take a second or two since it detects that no changes have been made and skips doing the build.

      If you have any questions or feedback, let me know.

      Happy coding!