Creating a Daily Visual SourceSafe (VSS) Label with a SQL Agent Job

SQL Server Consultant
SQL Server Programmer
C# Programmer
VB.Net Consultant
ASP.Net Consultant
SQL Problem Solver

Creating a Daily Visual SourceSafe (VSS) Label with A SQL Agent Job

Download the source code, script and exec:

As we headed into Maynard to join some of our friends for an evening at a club listening to overly loud music, my wife, Ulli, suggested that we should go with some of these same friends and their kids to one of the local Chinese restaurants that has a buffet. My reply was that I don’t go to buffets. I can’t go to one and then not eat too much. Her reply was that all I needed was “some discipline”. To which I replied that I have discipline enough to not go the buffet in the first place and that was enough discipline to avoid the problem.

When it comes to eating, my supply of self discipline goes only so far. I have some more self discipline when it comes to work, but never enough. How about you and your team: Do you have enough?

When it comes to self discipline I’ve found it’s always better not to rely on it for very long. Anything that requires discipline should be automated or the team is going to be set up for failures, both small and large.

So when I worked in a team recently that was failing to put Visual SourceSafe (VSS) labels on projects after they were checked in we decided to look to automation for a solution. What we cam up with is to continue to require that labels be put on versions of code when major builds where created. But we decided to augment that with automated daily labels.

The module that follows handles the job and illustrates how easy it is to use the SourceSafeTypeLib OLE Automation object to work with Visual SourceSafe from .Net. This example is written in VB.Net but it could just as well have been in C# or VB 6.0:

Imports SourceSafeTypeLib
Imports Microsoft.VisualBasic

Module MakeVSSDailyLabel
    '———————————————————————————
    '— SourceSafe constants
    '———————————————————————————
    '— The SourceSafe root project.
    Public Const cVSSRootProject As String = "$/"

'— The SourceSafe INI file.
Public Const cSourceSafeINI As String = ""

    '— The SourceSafe User ID/Password.
    Public Const cVSSUserName As String = "Admin"
    Public Const cVSSPassword As String = ""

    Sub Main()

        Dim objSourceSafe As SourceSafeTypeLib.VSSDatabase
        Dim objVSSProject As VSSItem
        Dim strProject As String

        '------------------------------------------------------
        '--- Create the SourceSafe ActiveX object.
        '------------------------------------------------------

objSourceSafe = New SourceSafeTypeLib.VSSDatabase

        '------------------------------------------------------
        '--- Open a connection to the SourceSafe database=
        '--- using the srcsafe.ini file and a valid
        '--- userid/password combination.
      '------------------------------------------------------

objSourceSafe.Open(cSourceSafeINI, cVSSUserName, cVSSPassword)

        '------------------------------------------------------
        '--- Create the project path of
        '------------------------------------------------------

strProject = cVSSRootProject

        '------------------------------------------------------
        '--- Set the project for the database script files.
        '--- (Project is expected to be something like:
        '--- $/RootProject/SubProject)
        '------------------------------------------------------

On Error Resume Next
objVSSProject = objSourceSafe.VSSItem(strProject)

        '------------------------------------------------------
        '--- If the project does not exist...
        '------------------------------------------------------

On Error GoTo 0

        '------------------------------------------------------
        '--- Check the files into SourceSafe.
        '------------------------------------------------------

        Dim sLabel As String
       sLabel = Microsoft.VisualBasic.Format(DateTime.Now, "yyyy-MM-dd") & " Daily" _
                   objVSSProject.Label(sLabel, "Standard daily label")

objVSSProject = Nothing
objSourceSafe = Nothing

End Sub

End Module

The complete solution can be downloaded using the button at the top of this article.

If you execute this project it adds a VSS label at the project root, which makes it apply to all projects. The time of day that the label is executed doesn’t matter.

Now that we have an easy way to create the label, who’s going to run the program every day without fail? We don’t want to rely on anyone’s discipline so again we turned to automation.

The most reliable way to get this program run every day is of course to automate it. There are many programs that will run a program on schedule including Windows’ AT command. For a variety of reasons my favoriate, when available, is SQL Server Agent.

Among the reasons that I prefer SQL Server Agent are:

It’s already been paid for, since most projects that I work on use SQL Server
It’s very reliable and time tested.
The scheduling options are fairly flexible.
I use it for scheduling jobs that run inside SQL Server so why not use it for other jobs.
It keeps a clear history.

To run a executable in SQL Server Agent every day do the following using SQL Enterprise Manager:

· Create a new job with a name that is meaningful such as “VSSDailyLabel”

· Add a step with a meaningful such as “Run VSSDailyLabel”

o The step’s type should be “Operating System Command (cmnexec)

o The command is: “c:\utilities\VSSDailyLabel.exe”. You’ll need to change the path on the command to the path where you’ve saved the executable.

· Add a schedule that runs once a day at a time after the programmers have gone home. I pick 11:55 PM. I made the job run only on Monday through Friday to eliminate two extra labels each week.

I made a SQL Agent job this way and then scripted it out to a file, “Run VSS Daily Label .sql”, that is in the above download. You should read the script and make changes in several places if you’re going to use it. For starters, you might have to change the path to the VSSDailyLable.exe if you don’t put it into “c:\utilities”. Also you might want to change the owner of the script and you may have to change the server name. Here’s the script:

-- Script generated on 11/22/2004 8:28 AM
-- By: AndrewNovick
-- Server: XXX

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT

SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories
           WHERE name = N'[Uncategorized (Local)]') < 1
         EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
    FROM   msdb.dbo.sysjobs
      WHERE (name = N'VSS Daily Label')

IF (@JobID IS NOT NULL) BEGIN

-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN

    -- There is, so abort the script
    RAISERROR (N'Unable to import job ''VSS Daily Label'' since '
              + 'there is already a multi-server job with this name.'
                , 16, 1)
    GOTO QuitWithRollback
END
ELSE

    -- Delete the [local] job
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'VSS Daily Label'
    SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT
          , @job_name = N'VSS Daily Label', @owner_login_name = N'sa'
          , @description = N'No description available.'
          , @category_name = N'[Uncategorized (Local)]'
          , @enabled = 1, @notify_level_email = 0, @notify_level_page = 0
          , @notify_level_netsend = 0, @notify_level_eventlog = 2
          , @delete_level= 0