TFS Usage in SQL Server Management Studio

Overview

Although it is possible to have SQL script files stored and managed within TFS, to work with the files in Management Studio, you have to check out the files from TFS, open the SQL file (navigating to its folder if it's not in your recent files list), then check it back into TFS when you're done. It would be much simpler if you could manage these SQL files within Management Studio like you do with other code files within Visual Studio. This article is a walkthrough of how to setup Management Studio with TFS, and how to create a solution and project under source control.

Given how cumbersome it is to (1) create the SQL Server solution and project files, and (2) to work with TFS from Management Studio, this procedure isn't worth the effort to manage only a few files. It is when the number of files grows that the benefits of this approach are seen.

Walkthrough

Setup



Source Control Plug-In Selection

Source Control Plug-In Selection



Source Control Environment Settings

Source Control Environment Settings


Adding Existing SQL Files to the Project

All source control requires a SQL Server Project (*.ssmssqlproj) file. If you want to manage existing scripts, use the following procedure.






Manually Editing the Project File

Open the SQL Server Project files (*.ssmssqlproj) in an XML editor to add the SQL files to it. Here's some sample XML. For each file you want in the project, you will need a FileNode node under SqlWorkbenchSqlProject\Items\LogicalFolder[@Name='Queries]\Items.

<SqlWorkbenchSqlProject 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="Store Procs (PJASINSK)">
  <Items>
    . . .
    <LogicalFolder Name="Queries" Type="0">
      <Items>
        <FileNode Name="usp_MyStoredProcedure.sql">
          <FullPath>usp_MyStoredProcedure.sql</FullPath>
        </FileNode>
      </Items>
    </LogicalFolder>
    . . .
  <SccProjectName>$/. . . /My_TFS_Path</SccProjectName>
  <SccAuxPath />
  <SccLocalPath>.</SccLocalPath>
  <SccProvider>MSSCCI:Team Foundation Server MSSCCI Provider</SccProvider>
</SqlWorkbenchSqlProject>