Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

TFS Usage in SQL Server Management Studio

RSS
Modified on Wed, Oct 20, 2010, 4:32 PM by Administrator Categorized as Visual Studio and Developer Tools

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


  • 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

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

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>

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.