Versioning Stored Procedures with Visual Studio 6.0 and SQL Server 7.0

Dave Mendlen
Visual Studio

November 1998

Summary: Discusses versioning Microsoft® SQL Server™ 7.0 stored procedures with Visual SourceSafe™ 6.0 and Visual Studio 6.0. (5 printed pages) Covers:

• Setting up the database to support versioning
  
  
• Setting up Visual Studio/Visual SourceSafe to support versioning
  
  
• Walkthrough of versioning a stored procedure

You need to be able to treat your stored procedures the same way you treat the rest of the application that you build with Visual Studio. That means locking up the originals in Visual SourceSafe and versioning them. There are a number of great reasons to use SQL Server 7.0 with Visual SourceSafe, including:

• All users work against the same database. That means you can work in teams efficiently.
  
  
• When you open a stored procedure, the text is read from the database.
  
  
• The source control library and database will be in synch (unless someone has a stored procedure checked out).
  
  
• You are allowed to view a stored procedure in the editor without checking it out from source control. However, the stored procedure is read-only. To edit the stored procedure, you must check it out.

Setup Tricks

Microsoft Visual InterDev™ and Visual J++™ Setup will automatically install Visual SourceSafe on your client computer. However, for the source control feature to work, you must install the Visual SourceSafe client software on the computer containing your SQL Server.

When you're installing Visual InterDev, make sure to select Visual InterDev Server Setup when this option is offered. This will install the Visual SourceSafe client software on your SQL Server.

In addition, the following procedure will help you set up your SQL Server to use Visual SourceSafe with stored procedures.

Setting Up Your Computer for Stored Procedure Source Control Through Microsoft Visual SourceSafe

When using source control with stored procedures, the SQL Server will be logging into Visual SourceSafe and making the necessary operations on the Visual SourceSafe database. Therefore, it will require appropriate permissions to perform these actions. It is recommended to reduce the security issues by setting the SQL Server service to run as a logged-on user.

To set the SQL server service to run as a logged on user

1. Click Control Panel, and run Services.
   
   
2. Select MSSQLServer and click the Startup button.
   
   
3. In the Service dialog box, select This Account.
   
   
4. In the This Account text box, type a domain and user name (for example, "domain\username") that has access to the server where the Visual SourceSafe software is installed.
   
   
5. Type a password into the Password text box. Click OK.
   
   
6. Stop the MSSQLServer service and then restart it.

To add a database to source control

1. Right-click the data connection in the Data View window.
   
   
2. Choose the Add to Source Control command. The Enable Source Control dialog box is displayed.

   

Figure 1. Enable Source Control dialog box

3. Enter the location of your source control database, a project name for your source control project, and a comment, if desired.

   Note   The source control database location (the srcsafe.ini file) is relative to your SQL Server database, not to your local computer. In Figure 1, the C: drive and the path shown are on the computer containing the SQL Server database.

4. A Lock icon is displayed next to each stored procedure in the database, indicating that it is under source control.
   
   
5. New stored procedures added to a database under source control must be manually added to the source control library.

To add a new stored procedure to source control

1. Right-click the stored procedure in the Data View window.
   
   
2. Choose the Add to Source Control command.

   The lock icon is displayed next to the stored procedure. The stored procedure is read from the database and placed in the source control library.

You can check out any of the stored procedures from the database and edit them in your project.

To check out a stored procedure

1. Right-click the stored procedure in the Data View window.
   
   
2. Choose the Check Out command. A Check icon is displayed next to the stored procedure.

When you attempt to check out a stored procedure from source control, both the source control version and the database version are retrieved. These versions are then compared. If they are different, you are prompted for which version to use. You can select the database version (and choose to update the source control version), just use the database version, or just use the source control version.

Figure 2. Checking out and editing stored procedures

If you select the source control version, the stored procedure is compiled on the database. If the compile fails, the stored procedure is opened in the editor and marked for editing.

You can also click the View Differences button and see a window displaying the different text for the source control and database versions of the stored procedure.

After you have checked out and edited a stored procedure, you will want to check the changes back into the source control library. When this is done, the stored procedure is read from the database and updated in the source control library. If the stored procedure is open in an editor and marked for editing (as just discussed, the database compile failed when you tried to check the file out), the Check In command is disabled.

To check in a stored procedure

1. Right-click the stored procedure in the Data View window.
   
   
2. Choose the Check In command.
   
   
3. Enter a comment for this version of the stored procedure in the source control library if you so desire.

   The Check icon next to the stored procedure is removed.

   Note   You are always working against the database. When you check in a file, the latest database version goes into source control.

You can undo a checkout if you decide you don't want to save your changes.

To undo a checkout

1. Right-click the stored procedure in the Data View window.
   
   
2. Choose the Undo Check Out command.

   The Check icon next to the stored procedure is removed. The Lock icon is displayed.

When you elect to undo a checkout, an attempt is made to resynchronize the source control library and the database. The previous version from the source control library is compiled on the database. If an error occurs in compiling, no further action is taken and the lock on source control is cleared.

Note   In this case, you're getting the source control version, and replacing the version that you have been modifying, because you decided to undo the checkout.

Final Trick

If you get the following dialog box, just type in "Admin" or some other legal user in Visual SourceSafe.

Figure 3. Source Control Login dialog box

Summary

Use the Visual Studio/Visual SourceSafe integration with SQL Server 7.0 to ease your ability to work in teams with SQL Server 7.0. Stored procedures are still important in a three-tier world, however you need to ensure that changes made by one developer aren't trampled by those of another or by your database administrator. These tools will ensure that your teams will work more productively.