Migrating a TFS workspace to a new Windows user

The IT guys at work decided that our Windows domain should be deprecated and created new Windows users on a brand new domain. Among the many problems it gave us, there was the TFS workspaces which are hosted at a partner's datacenter.

So when they brought the old domain down, TFS started acting crazy. The reason being the workspaces being owned by the old users and unaccessible to the new ones.

If you're trying to migrate workspaces between users and you search about it, you'll probably find some "tf workspaces" commands. The problem is that these commands will seem to run fine but will fail to replace the workspace's owner.

So here's an "easy" step-by-step to do it through VisualStudio. It assumes you can still have access to your previous user:

1. You must sign-in to TFS using your previous user. To confirm, look into Windows Start Menu --> Control Panel --> User Accounts --> Credential Manager and look for entries related to your TFS server. If wrong user, edit and change. Be sure to change all related entries (I had two of them).
2. In VisualStudio, go to File --> Source Control --> Advanced --> Workspaces, and for each workspace:
3. Edit, then expand the "Advanced >>" tab.
4. Under "Owner", put the full user name (including the domain).
5. Click OK. If no error, skip to step 11.
6. If error due to pending changes, do as suggested and shelve them as follows:
7. Open "Source Control Explorer".
8. Right click on the top entry under "Folders" and select "Shelve pending changes".
9. Be sure to have "Preserve pending changes locally" UNCHECKED, set a meaningful name such as "UserMigration" and Shelve.
10. Go back to step 2.
11. Close VS.
12. Remove or update your TFS credentials to the new user (see step 1 to see where to find them).
13. Reopen VS.
14. If you never went through steps 7 to 10, skip on to step 18.
15. Unshelve your changes under File --> Source Control --> Find --> Find Shelvesets.
16. On the "Find Shelvesets" window, set the user to the previous user and double-click the shelveset you created on steps 7 to 10.
17. Click "Unshelve changes". If there are conflicts, you'll probably want to keep the shelved version, not the workspace version.
18. Done!

Conditional COLLATE on SQL Server query

Here's a little gift for those of you trying to have conditional COLLATE while searching for some text in a SQL Server table.

This is useful when you want to have an easy control on case insensitiveness. That is, use a flag to set it ON or OFF.

The difficulty of having conditional COLLATE is that SQL Server applies a COLLATE statement to the entire clause. For example, if there's one COLLATE within a CASE, it will be applied to the entire CASE and trying to use different COLLATE statements will raise a conflict error.

It's also not possible to specify the COLLATE value as a variable unless you use dynamic SQL.

So here's my answer to this problem:

declare @ignoreCase BIT = 1

declare @pattern VARCHAR(max) = '%featureconfig%'

SELECT

       Name, 
       SUBSTRING(

              Script,

              CASE WHEN @ignoreCase = 1 THEN PATINDEX(@pattern, Script COLLATE Latin1_General_CI_AI) ELSE 0 END

              +

              CASE WHEN @ignoreCase = 0 THEN PATINDEX(@pattern, Script COLLATE Latin1_General_CS_AI) ELSE 0 END

              - 90, /* Start showing 90 characters before match, for context */

              LEN(Script)

       )

  FROM TableWithNameAndScriptHere

  WHERE 1 = 0 /* bogus FALSE condition to ease adding or removing "OR" clauses */

       OR (@ignoreCase = 1 AND (script like @pattern COLLATE Latin1_General_CI_AI))

       OR (@ignoreCase = 0 AND (script like @pattern COLLATE Latin1_General_BIN))

order by Name

The "SUBSTRING" part is to show the context where the first match is enclosed. As mentioned above, it's not possible to specify two different COLLATE statements within a CASE, so the solution is to specify each COLLATE in its own CASE.

The correct CASE will result in the index where the pattern first matches, whereas the incorrect CASE will result in 0. The two can then be added up so that their applicability can be interchanged by the @ignoreCase variable.

The WHERE clause itself only needs different LIKE conditions for each COLLATE.

Easy enough.