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¶

Download and install Visual Studio Team Foundation Server MSSCCI Provider.msi.
- You can Google the file name. (It's on Microsoft's website.)
- The download for SQL Server Management Studio 2005 can be found here: Visual Studio Team Foundation Server MSSCCI Provider.zip

Within SQL Server Management Studio, navigate to Tools menu > Options > Source Control > Plug-In Selection. For Current source control plug-in select Team Foundation Server MSSCCI Provider.

Source Control Plug-In Selection

In the Options dialog, select the Source Control > Environment node and make the following settings
- Keep items checked out when checking in = cleared
- On Save = Prompt for check out
- On Edit = Prompt for check out
- Allow checked-in items to be edited = cleared

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.

Within Management Studio, create a new project.
- For an existing Solution, right click the Solution node in the Solution Explorer and click Add > New Project. For a new Solution, select File menu > New > Project.
- Select the SQL Server Scripts template
- For Name specify the name of the folder containing your scripts
- For Location specify the parent of the folder containing your scripts.

In Management Studio, close the solution.

In Windows Explorer, rename the solution and project files to include your username, then edit both in a text editor to reflect the new names. (The reason for this is because when you open a SQL file, it will try to associate connection information with the file.)

Open the new solution in Management Studio, and from there add the solution and project to TFS.

To add existing files, you can
- Within Solution Explorer, right click on a project and navigate to Add > Existing Item.
- Within Management Studio, navigate to Project menu > Add Existing Item
- Follow the procedure below to manually edit the project file.

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>