Simple TF.exe example to add, checkin and delete a file in TFS from the commmand line

I was asked a question earlier today and I thought it might be useful to share the answer.

 “.. I am trying to find a way to check in artifacts into TFS via command line utilities.  I have found a ton of documents on “tf”, but can not seem to locate that binary, any quick thoughts”

 Below are the contents of a quick example bat file:

 

@Echo OFF

REM My root workspace is c:\-x-\_TFS_

set tfPath = "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe"

cd c:\-x-\_TFS_\Test\

dir ..\..\*.* >> test.txt

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" add test.txt /lock:none

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" checkin /comment:"just a demo of tf.exe" /noprompt .\test.txt

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" delete test.txt /lock:none

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\tf.exe" checkin /comment:"just cleaning up a demo of tf.exe"  .\test.txt

Incremental SQL Deploy: When a schema change might cause data in a table to be lost

Why does a SQL table change in a VS2010 DB Project raise the “The schema update is terminating because data loss might occur” deployment error?

This question has come up a lot lately…

Get acquainted with the “Block incremental deployment if data loss might occur”

SQL deployment property

1.       “When you deploy changes to an existing database, some changes could cause data loss. If a change might cause data in a table to be lost, the deployment will be canceled if the Block incremental deployment if data loss might occur check box is selected. By default, this check box is selected, but you can find it in the Properties window for your project. For more information, see An Overview of Database Project Settings.” [1]

Changes that can raise the “The schema update is terminating because data loss might occur.” error

1.      “The following kinds of changes will cause data loss: if a table is dropped and recreated, if you change the size of a column (char(100) to char(50) or nchar(100) to char(100)), or if you change the collation of a column that has a character type.“ [1]

2.       “…, if you have renamed your objects or columns at the source you might incur data loss while updating the target.” [2]

Avoiding the “The schema update is terminating because data loss might occur.” error

1.       Table Renaming: When you use refactoring to rename a database object or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data. [1]

Note: To perform an table renaming using database refactoring [In4mr]

1.       select the table in the solution explorer

2.       On the File Menu select Data à Refactor à Rename

3.       This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

2.        Script data before renaming columns: As long as your source has new objects there is nothing to worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. [2]

3.       Column Renaming: Column renaming can be done by refactoring as well, but the process id slightly different. [in4mr]

Note: To perform an table renaming using database refactoring

1.       At the top right corner of the Solution Explorer panel click the Schema View button.

Schema View (Far Right)

2.       Browse to the table containing the column

3.       Expand the columns node

4.       Right click on the column to be renamed

5.       Select Refactor à Rename

6.       This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

4.       Adding Columns: Adding a column to a table's existing column list will not cause data loss. [infmr]

References:

1.      An Overview of Database Build and Deployment

                        http://msdn.microsoft.com/en-us/library/aa833165.aspx  

                        MSDN , July 2010

2.       Schema Compare in VS 2010

                                http://www.mssqltips.com/tip.asp?tip=2089

                                Arshad Ali , 8/12/2010

I hope this is helpful,

In4mr

my@in4mr.com

Marcus Aurelius played first edition D&D

Imagine playing Dungeons and Dragons in the 2nd century or as Anais put it playing D&D in the time of D&D.

A ROMAN GLASS GAMING DIE | CIRCA 2ND CENTURY A.D. | Christie's

"Execute As" clause with a CLR based procedure

If you have ever tried to write a file to a network share from sql server with a clr procedure using EXECUTE AS '' then you have learned true frustration.
Almost undocumented is that the only domain accounts that will actually be used for CLR pass trhough impersonation are CALLER, OWNER, and SELF.
To use these the dbo itself must be the account being used for access.

"Execute As" clause with a CLR based procedure

The other option is to use windows impersonation inside of your CLR class.

Delete those pesky orphan SQL Server Jobs

CREATE PROCEDURE [dbo].[sputil_DeleteJobsLike_lite] (@jobname Varchar(255),@jobsdeleted int =null output)

  AS

                -- ============================================================================

                -- Author:                            drb

                -- Create date: 12/01/2009

                -- Description:   deletes jobs that names match a string

                -- Note: @dbName includes the schema

                /* USAGE

                DECLARE @_deleted int

                SET @_deleted =0

                EXECUTE [sputil_DeleteJobsLike_lite] @jobname='QRY_7_',@jobsdeleted=@_deleted output

                SELECT @_deleted [Jobs Deleted]

                */

                -- ============================================================================

  BEGIN

                DECLARE @SQL VARCHAR(MAX)

                SET @SQL = ''

 

                SELECT @jobsdeleted = ISNULL(COUNT(1),0)  FROM msdb.dbo.sysjobs (nolock) where name like '%'+@jobname+'%'

 

                SELECT @SQL =

                                                                @SQL + 'EXEC msdb..sp_delete_job @job_name = N'''+[name]+''';

                                                                ' +CHAR(10)+CHAR(13) 

                FROM                   msdb.dbo.sysjobs

                WHERE                 name like '%'+@jobname+'%'

               

                IF (RTRIM(@SQL)<>'')

                EXEC(@SQL)

 

END

This is the "lite" version. The full version has contextual logging, verbosity level, parent transaction tracking and a WhatIf mode.
If that sounds interesting let me know and I will put up that framework.

Hubris, humility and data modelling

Humility is similar to weight loss in that sometimes you have to convince yourself it exists before it will.

Sometimes you have to assume you are wrong even when you don't think you are in order to see what you missed. This can be tough as all of us live in firmly developed personal paradigms. Extricating ourselves from these to view the known as if it were unknown, or another known, is rarely some thing we consider without the assistance of a strong catalyst.

Today I had to step back and consider all the possible reasons for a group of intelligent people to design a data warehouse in a manner that went against all my personal experience. I had up until now believed that I was missing a peice of the puzzle which they possessed. Try as I might though I could not see beyond my own world definitions.

Biting my tongue and hubris I began my research. Beginning with the architecture reference sited for the design, Kimball. Here I found the source, not the solution, of the differences in approach that first caught my eye. You see I was raised and spoonfed Inmon models as a sophmore data waterhouse designer at intel. This strange unfamiliar design most definately rooted in kimball's methodology.

This does not begin to address the irregularities and what appeared to be contrary patterns in the dimensional model I had seen. It does however indicate that both approaches are valid and not only that, but equally so.
Exhibit 1. kimball and inmon dw models
To be cont...

TFS Deployer in TFS 2010

This configuration took a while to put together and debug, but once in place it is an incredibly powerful and flexible system.
What follows is the core workflow I implemented for build quality driven deployment.

The process works like this:
1. A “solutions” directory is placed in the root of the team project and a <projectname>.<environment>.tfsbuild.sln (i.e. qmail.dev.tfsbuild.sln) is placed in it.
2. A BuildPrep (or PreBuild) and a PostBuild project are added to the .tfsbuild.sln. These handle preparation and cleanup tasks.
3. A continuous integration build is configured for this solution file.
4. A check in takes place
5. The solution’s BuildPrep project kicks off the following steps:
a. The latest Common.dll and Common.Providers.dll are copied from file://mytfs/common to the projects ReferenceAssemblies folder
b. Updates a SharedAssemblyVersion file with a Major.Minor.Build.Rev formatted and incremented build number. Then copies it to each project in the solution. This keeps grouped DLLs on the same version number.
6. The projects are compiled
7. If this is a release build then the DLLs are StrongNamed
8. The build output is copied to file://mytfs/TFS/Builds
9. A build notification is sent out to the TFSSubscribers@mydom.com distribution list.
10. At this time the Build Quality is “Unassigned”
11. A Developer or BuildMaster reviews the build and assigns a Build Quality. Typically the first assignment is “Ready for Initial Test”.
12. This change fires a BuildQualityChange Event that TFSDeployer is notified of.
13. TFS Deployer then Checks out the BuildProcessTemplates\Deployment scripts for the build that fired the event. These include the DeploymentMappings.xml and the various PowerShell task automation scripts.
14. The event is processed by using rules laid out in the DeploymentMappings.xml document attached. (snippet below)

<Mapping BuildDefinitionPattern="^Dev\.PEKContentService.*$"
Computer="myTFS"
OriginalQuality="*"
NewQuality="Ready for Initial Test"
Script="Dev.PEKContentService-Any2RFIT.ps1"
RunnerType="PowerShell"
NotificationAddress="TFSSubscribers@mydom.com">
<ScriptParameter name="DevelopmentServerName" value="myWEB.myDOM.COM" />
<ScriptParameter name="DevelopmentServerIP" value="10.101.244.106" />
<ScriptParameter name="QAServerName" value="MWEB.myDOM.COM" />
<ScriptParameter name="QAServerIP" value="10.101.244.174" />
<ScriptParameter name="ProdServerName" value="HOSA-P-WEB01-CD" />
<ScriptParameter name="ProdServerIP" value="10.180.28.50" />
</Mapping>

15. Based on the DeploymentMappings rules and the BuildQualityChange event properties the designated powershell script is run. In this case that is “Dev.PEKContentService-Any2RFIT.ps1” (snippet below)

if ($DevelopmentServerName -eq "myWeb") { Write-Output "Script parameter received! Publishing Common.dll to file://$developmentservername/Common/" ; }
#get build variables
$tproj = $TfsDeployerBuildDetail.TeamProject
$bquality = $TfsDeployerBuildDetail.Quality
$dloc = if($TfsDeployerBuildDetail){$TfsDeployerBuildDetail.DropLocation}else{""}
...
#initialize build log
$TFSDEployerLog = "file://mytfs/TFSBUILDS/Log.txt"
$date = ( get-date ).ToString('yyyyMMdd hh:mm:ss')
#write Release Notes
"This is a build notification that a $tproj build has been upgraded to (Ready For Initial Test) on..." >> $TFSDeployerLog
#Register Destination Variables
$Destination = "file://$developmentservername/wwwroot.dev/"
$DestinationPEKWebsiteBin = "file://$developmentservername/wwwroot.dev/PekContentService/bin/"
$DestinationPEKWebsiteBin = "file://$developmentservername/wwwroot.dev/PekContentTestHarness/"
...
#Register Source Variables
$SourceDlls = "$dropLocation\*.dll"
...
$Source="$dropLocation\_PublishedWebsites\"
#publish files and directory structure
Get-ChildItem -Path $Source
Copy-Item -Destination $Destination -Recurse -Force
Get-ChildItem -Path $dropLocation\actn.Lib.Security.dll
Copy-Item -Destination $DestinationPEKWebsiteBin -Force
...
#zip up test form
Get-ChildItem -Path $dropLocation\*.TestForm*
Copy-Item -Destination $DestinationTestHarness -Force
Get-ChildItem -Path $dropLocation\ComponentSpace*
Copy-Item -Destination $DestinationTestHarness -Force
dir DestinationTestHarness
add-Zip $dropLocation\PursuitListTestHarness.zip
#configuration and other follow-up tasks are scripted here…

16. When this script completes the code has been deployed to Dev and can be tested for integration issues by the developers.
17. Then they are comfortable the Developers, a buildmaster, a QA person or a Business Analyst can update the build quality to "Initial Test Passed"
18. This will trigger another event, and the TFSDeployer rules engine will invoke the corresponding script which then deploys and configures the application in the associated environment.