Source code control is used in development environments in which several programmers work with the same piece of code. Applications and databases that are created in a development environment must be deployed first in a test environment and then in a production environment; this raises a variety of issues. This article covers two different approaches for source code management and database deployment. It is excerpted from SQL Server 2000 Stored Procedure & XML Programming, second edition, written by Dejan Sunderic (McGraw-Hill/Osborne, 2004; ISBN: 0072228962).
Source code control (or version control) is typically introduced in development environments in which more than one developer needs to work with the same piece of code. It allows development organizations and their members to
Manage code centrally
Manage multiple versions of the same code
Track change history
Compare versions
Prevent or allow developers from modifying the same piece of code at the same time
Synchronize deployment of all modifications needed to implement a single feature or bug fix
The problem that you will face relatively often is that, while you develop your database (and application) in a development environment, you must deploy the database first in a test environment and then in a production environment. Initially, you need to deploy the complete database, but later you will have to update the database with design changes and hotfixes.
In this chapter, I will introduce methods and tools for source code management and database deployment. I will present solutions using two different approaches. One approach is for developers who have Visual Studio .NET (and who are probably doing both database and application development). The other, more traditional, approach is geared toward SQL Server specialists who are working with traditional database development tools. I will use the tools that are delivered with SQL Server, as well as tools that I have developed, to automate some processes.
--------------------------------------------------------------------The Concept of Source Code Management
Microsoft provides source code control software as an integral part of its development environment under the name Visual SourceSafe. This application allows developers to control their most valuable asset—source code. You can also use the Visual SourceSafe database to manage other file types such as web content, documentation, and test data, but our focus in this chapter is on how to use Visual SourceSafe to manage database objects.
Introduction to Microsoft Visual SourceSafe
Microsoft’s primary purpose in delivering Visual SourceSafe as a part of its Visual Studio .NET suite of development tools is to provide a project-oriented means of storing and organizing code that allows developers to spend more time developing their projects and less time managing them. The emphasis is on ease of use and integration with a wide range of development tools. SQL Server developers can benefit greatly from this ease of use and integration, not only with regard to source code, but also as a means of organizing related files such as project documentation and test data.
As with SQL Server, there are different ways to use Visual SourceSafe. It is essentially a client/server application, but if you are an independent developer, your development workstation will likely also be your application server, database server, and source code server. Of course, if you are an independent developer, you may be wondering why you have a need for source code control at all. I will discuss this issue later in the chapter. For now, you can take my word that source code control is just as important for the solo developer working on a simple project as it is for a large development team working on a complex, component-based project.
If you are a member of a development team, the Visual SourceSafe client will allow you to work with local copies of code while preventing other members of your team from overwriting your changes while you have the code checked out from the Visual SourceSafe database. The benefit of this simple concept is obvious, but you have to work with and become comfortable with Visual SourceSafe before its many other benefits will become just as obvious. After you have posted your source code, you can
Get the current version of all files.
Check out a copy of a file that needs to be changed. Visual SourceSafe is, by default, configured to prevent all other developers from changing the file until it is returned (checked in) to the Visual SourceSafe database.
View differences between a local version of a source code file and the latest version stored in the Visual SourceSafe database.
Label versions of files to identify them with a particular release of a software product.
Retrieve older versions of a particular file or a complete set of project files.
View changes between any two versions of a source code file.
Share common files between separate projects.
Make a single backup copy of the complete source code and all supporting files.
Create branches of source code files to separately manage multiple versions of a software project.
Merge code in different branches of the source code file.
Before you can use Visual SourceSafe, you need to create users and assign privileges to them.
When you install Visual SourceSafe, you create just two users: Admin and Guest. The Admin user has all privileges in the database and can also create other users. The Guest user is initially limited to read-only access to source code files. Both users are created with their password set to an empty string (that is, blank). Since this state constitutes a threat to your source code, your first step should be to set the Admin password using Visual SourceSafe Administrator (User | Change Password). When you are done, create a user for yourself (Users | Add User) with the appropriate permissions.
TIPIf your Visual SourceSafe username and password match your operating system username and password, you will not have to type them each time you open Visual SourceSafe on the local system. Visual SourceSafe can be configured to use them automatically.
With Visual SourceSafe, you can assign more refined permission levels, such as Add, Rename, Delete, Check In, Check Out, Destroy, and Read. To activate this wide-ranging control, click Tools | Options | Project Security and check the Enable Project Security option.
Adding Database Objects to Visual SourceSafe in Visual Studio .NET
To demonstrate the implementation of source code control in a database project, you add code from your sample Asset database in Visual Studio .NET:
Create the Asset database project in Visual Studio .NET.
Open Solution Explorer.
Make sure that the Asset database is one of the Database References. If the reference does not already exist, right-click Database References and select New Database Reference to create one that points to the Asset database. Figure 11-1.The Generate Create Scripts dialog box
In the Server Explorer, expand the Data Connections node.
Right-click the Asset data connection and choose Generate Create Script. The program prompts you for objects to be scripted (see Figure 11-1 above).
Select Script All Objects, and then switch to the Options tab and select the Script Database, Script Object-level Permissions, Windows Text (ANSI), and Create One File per Object options.
When the program prompts for the folder, accept …\Asset\Change Scripts. The program will then create a set of script files for the database objects (see Figure 11-2).
Select File | Source Control | Add Solution to Source Control. Visual Studio .NET will prompt you to log in to the Visual SourceSafe Common database: Figure 11-2.Crate scripts
If your Visual SourceSafe database is stored locally, you can keep the Common database. If your Visual SourceSafe database is not stored locally, use the Browse button to locate the shared srcsafe.ini file.
NOTE On my machine, the Visual SourceSafe database is located in the C:\Program Files\Microsoft Visual Studio\Common\VSS folder. My computer, in this case, is a development workstation, as well as the database server and Visual SourceSafe server.
If the Visual SourceSafe client is installed on the same machine as SQL Server, the location of the Visual SourceSafe database that you need to specify in this text box should be relative to the server machine. If you are developing from a workstation that is separate from the “development” server, you have to be careful how you enter the location of the Visual SourceSafe database. You should use the server’s absolute path (for example: C:\Program Files\Microsoft Visual Studio\Common\VSS) regardless of whether you have that drive mapped on your workstation using another drive letter (such as S:).
Visual SourceSafe prompts you to add a project to Visual SourceSafe. Name the project (you can also type a comment to describe the project).
Visual SourceSafe creates a project and locks all Create scripts (scripts that can be used to drop and create objects from scratch). You can see a small lock icon beside each Create script in Solution Explorer:
NOTE From this moment, you must check out a Create script before you can change it.
When Create scripts are locked, you can open them for viewing in the editor, but Visual Studio .NET will prevent you from changing them until you check them out.
To view a Create script, right-click the script you want to review and select Open. Visual Studio .NET opens a copy of the Create script but marks it “read-only.”
The following list demonstrates how to change a stored procedure:
Close the window with the read-only version of the Create script.
Right-click the script for a stored procedure and select Check Out from the pop-up menu. The program prompts you for confirmation and comment:
Make some trivial change to the stored procedure.
Save the changes (File | Save) in the change file.
Right-click the file and choose Check In from the menu. The program prompts you for confirmation and comment again.
TIp Take the time to describe what changes you made in your comment. This will be incredibly helpful if some detective work is required later.
Click Check In. Visual Studio .NET saves the changes in Visual SourceSafe and locks the Create script.
At this point, the stored procedure does not yet exist in SQL Server. You must right-click the file and select Run to add it to the default database. Visual Studio .NET opens the Database Output pane to show the results (and possibly errors) of the execution (see Figure 11-3).
Figure 11-3.Database Output pane of the Create script executed against the server
NOTE
Unfortunately, this solution does not prevent another developer from using some other tool to change a database object directly in the database. You can even open Server Explorer in Visual Studio .NET to change them without source code control. Visual SourceSafe only works through consensus. Loose cannons can still wreak havoc on your development ship.
To promote the change to another server, you can choose the Run On option in the context menu of the change script.
TIP When I first started to use Visual SourceSafe, the directions implied by the terms “Check Out” and “Check In” sounded inverted to me. Just think of Visual SourceSafe as an actual safe from which you are taking your code and into which you subsequently return your code after you are done with it.
Now, go back and check out the same stored procedure again.
Open it and reverse your previous changes.
Save the stored procedure.
Run the Change script against the server to test it (right-click the Change script and select Run).
Assume that you are not satisfied with these changes and that you want to abandon them. (Assume that you have tested them and the result is not what you expected.) To do so:
Select Undo Check Out from the context menu. The Visual SourceSafe Server locks the file again and uses the previous copy from Visual SourceSafe to reverse the changes in the local file.
To reverse changes to the database, you must Run the change script again.
NOTE Undo Checkout does not actually change any code already deployed, but merely reverses the check out process.
The full power of Visual SourceSafe can only be realized through one special tool— Visual SourceSafe Explorer. Take a look at this tool by following these steps:
Open Visual SourceSafe Explorer from the Windows Start menu (depending on the version that you have: Start | Programs | Microsoft Visual Studio .NET | Microsoft Visual SourceSafe | Visual SourceSafe) or from Visual Studio .NET (File | Source Control | Microsoft Visual SourceSafe).
Expand the Asset project and drill down until you reach Stored Procedures (see Figure 11-4).
The following sections examine some of the most interesting features of the Visual SourceSafe Explorer, particularly history, labels, and versions.
History
Visual SourceSafe keeps an audit trail of changes made to a file. To view this history of changes:
Right-click the stored procedure that you edited earlier in this chapter and select Show History from the pop-up menu.
Visual SourceSafe prompts you to define the history details you would like to display:
In this case, accept the defaults and click OK.
Figure 11-4.Visual SourceSafe Explorer
Visual SourceSafe Explorer displays a list of the different versions of the stored procedure, along with the name of the user responsible for each action.
Now you have several options. If you select one version, you can view (click View) the code in an ASCII viewer. You can also see details (click Details) of the selected version such as comments and timestamp. The Get button lets you obtain a version of the stored procedure in a text file.
You can also temporarily or permanently set one of the previous versions to be a current one. The Pin option is usually applied as a temporary measure to test the behavior of an older version of a procedure. If you find that changes you made in your code are introducing more problems than they are solving, you can use the Rollback function to return to an earlier version of the code. Note that all newer versions will be deleted.
My favorite option is Diff. It compares two versions of a file. To use it:
Select two versions of a stored procedure (for example, version 2 and version 3) in the History window. You can select multiple versions by pressing the CTRL key and then clicking them.
Click the Diff button. The Difference Options dialog box appears.
This dialog box lets you specify parameters for comparing files. If you wish to ignore case and white space, click OK to accept the defaults; Visual SourceSafe Explorer displays a window in which the differences between the two versions are highlighted (see Figure 11-5).
Figure 11-5. File differences in Visual SourceSafe Explorer
You have probably realized by now that the term “version” in Visual SourceSafe does not actually correspond to the concept of version (or release) that we generally think of when we consider software. A Visual SourceSafe “version” actually corresponds to a change in the source code. You should use labels in Visual SourceSafe to implement the equivalent of a release.
You can apply the Label option from the main window of the Visual SourceSafe Explorer. You can select one or more files and/or one or more projects (folders). When you apply the Label option (File | Label), the Label dialog box appears and prompts you to specify the text of the label (your official release number, for example).
The current versions of all selected files will be labeled. Later, you can use these labels to collect the code that belongs to a particular version. This feature can be very important for supporting or testing the product.
Even more exciting is the opportunity to view the complete history of a project (right-click the project folder and select Show History from the pop-up menu) and determine many historical facts about the project, such as which changes were performed on it after a particular release.
Adding Database Objects to Visual SourceSafe: Traditional Approach
Unfortunately, if you do not have Visual Studio .NET, it is not easy enough to manage the code of individual database objects with only the tools built into SQL Server and Visual SourceSafe. The process involves two steps:
Generate scripts from SQL Server.
Check in files into Visual SourceSafe.
Therefore, I’ve created a tool that loops through database objects and scripts them into separate files—TbDbScript. It’s written in VBScript and you can download it from www.TrigonBlue.com/sqlxml/sqlxml_download.htm. To run it, you must use Windows Script Host and cscript.exe. Execute from the command prompt:
cscript TbDbScript.vbs .\ss2k sa password c:\dbscripter\ Asset
The parameters are: server, login, password, destination of database files, and, optionally, the database name. Use the space character as a parameter delimiter. If you omit the last parameter, the program will script all nonsystem databases on the server.
When scripting is finished, you will find database objects in the set of Create scripts in the folder named after the database (see Figure 11-6).
The tool also creates deployment scripts. They contain Create scripts grouped by type. You will read more about them in the “Deployment Scripts: Traditional Approach” section, later in the chapter.
It is true that the Generate SQL Scripts Wizard in Enterprise Manager will perform similar actions, but there are several significant differences:
TbDbScript follows naming conventions used in Visual Studio.
Generation does not require user intervention and therefore is less prone to errors. The resulting files are always the same.
The script generates individual database object Create scripts and deployment scripts at the same time.
You can also schedule usage of TbDbScript, which may be very useful when the development team is not using Visual SourceSafe religiously—as when the team is making changes live to the development database.
Every deployment script file begins with a Use database_name statement and they can even be deployed manually using Query Analyzer.
Figure 11-6. Database object scripts generated by TbDbScript
Now that Create scripts and deployment scripts are created, all you need to do is check them into the Visual SourceSafe database. You can do this manually with Visual SourceSafe Explorer or automatically with a little VBScript tool—TbDir2Vss.vbs. You can download the tool from www.TrigonBlue.com/sqlxml/sqlxml_download.htm. To run it, you must use Windows Script Host and cscript.exe. You need to specify the location of the srcsafe.ini file, username, password, Visual SourceSafe project/folder, and local folder:
Traditionally, RDBMS installation is perceived as complicated, and your customer will understand that you need to send a database administrator (or a couple of them) to set up the environment. When you work with a file-based database system such as Access, database deployment is not a big issue. You usually create a setup program for your client application, and your database (mdb) file is just one more file that must be installed on the computer. When you are working in a client/server environment with SQL Server, you first have to install and configure the database server and then install databases.
Fortunately, installation of SQL Server has been simplified significantly. Almost anyone can perform it and there are usually no problems. In fact, SQL Server can be configured to be installed unattended. Microsoft SQL Server Desktop Engine (MSDE) is designed to be deployed on client computers using a special set of setup files that can be included in your setup program.
Some early versions of SQL Server required that all dependent objects be present on the server before a new object could be created. Administrators had to use many tricks to transfer a database from one server to another. The introduction of Deferred Name Resolution has reduced the complexity of database deployment in the SQL Server environment. For example, a stored procedure can be created even if it references a stored procedure that is not yet on the server. Unfortunately, it is not perfect yet. For example, it is not possible to create a foreign key that references a table that is not yet in the database.
The methods for database deployment can be divided into two groups:
Deployment of a complete database
Deployment of individual objects
Deployment of a Complete Database: Traditional Approach
The idea behind this method is to use some means of moving the complete database so that relationships between individual database objects do not have to be managed once they are established. There are several options:
Detach and reattach the database
Use the Copy Database Wizard
Back up and restore
The idea behind the first two options is to detach the database from the server, copy the database files to the production server, and then attach the database files to the new server (and reattach the database files to the original server, if applicable). To detach the Asset database manually, you can use the following script:
EXEC sp_detach_db 'Asset'
SQL Server checks the integrity of the database, flushes everything that is in memory to disk, stops further changes to the database, and releases database files.
NOTE You must have exclusive use of the database to perform this function.
You can then copy the files (in this case, Asset.mdf and Asset_log.ldf) from the \mssql\data folder to a data folder on the target server. To attach the Asset database, you can use
If your database consists of more files, simply add them to the list of parameters. But if your database contains just one data file, you can use an alternative command:
TIP There is no harm in dropping the transaction log file and attaching just the data file (as long as you do not have some special reason, such as replication, to preserve the log).
You can execute these Transact-SQL statements manually in Query Analyzer or from the setup program. The setup program can use the command-prompt utility osql.exe to run a script file or use ADO to execute the script.
NOTE I have chosen this method for deployment of the sample database to your computer.
A new feature found in SQL Server 2000 is the Copy Database Wizard. You can use it to copy (or move) a database on a known (production, testing, or some other) server. Behind the scenes, the wizard uses stored procedures for detaching and attaching the database. It also contains features for copying logins, error messages, jobs, and system stored procedures, which may be useful for completing server configuration. The disadvantage to this wizard is that it can be used only between servers on the same network. It is useful for deployment of databases used internally in a local department or organization, but not for deployment of databases that are required for shrink-wrapped software.
Another solution is based on creating a backup of the database on a development server and then restoring the database on a production server. Again, this can be performed manually or it can be scripted and included in the setup program.
Unfortunately, these techniques will not restore the links between server logins and database users. Server logins are stored in the master database; on different servers, different logins will have different IDs. Database users are stored in each user database. One of the parameters for a database user is the ID of the login to which it is attached. However, that ID is likely to refer to a different login on the production server. The simplest way to handle this problem is either to create all users again using Enterprise Manager or a script that you have prepared in advance, or to use roles instead of users as the foundation of your security solution. See the “Security” section in Chapter 10 for more information. SQL Server offers another solution to this problem—see “Synchronization of Login and Usernames” in Chapter 10.
Another disadvantage to these methods is that you have to maintain a “clean” database—a database that contains just database objects and seed data. Such a database can be delivered to a customer, but it cannot be used for development and testing. In both development and test environments, you need to add test data in order to test all features. You need to develop either scripts for adding test data or, alternatively, scripts for removing test data from a development database.
Some organizations choose to manage the code for individual objects and to deploy the database piecemeal by executing the code on the production server. This provides more flexibility, but requires more effort.
Deployment Scripts: Traditional Approach
Individual object scripts can be grouped in files with all objects of a particular type or even with all objects in a database. Such files can be created using the Generate SQL Script tool in Enterprise Manager. It can be set so that the group of objects of the same type is saved in a single file. It is also possible to use a custom tool to aggregate individual database object files from the Visual SourceSafe database. Most ERD modeling tools can also produce such scripts (but their scripts often require manual intervention). You can also use TbDbScript, described earlier in this chapter.
To have better control, I like to use the TbDbScript tool, or the Generate SQL Script tool in SQL Server, to create one deployment script for each type of database object. When the system contains more than one database, I find it very useful that TbDbScript names deployment script files using the Database - DbObjectType.sql convention (see Figure 11-7).
Scripting Data: Traditional Approach
Some tables contain data (seed, static, or lookup data) that needs to be deployed along with the database schema. To assist in deployment and to facilitate storing the data with the source code, use the setup_DataGenerator stored procedure, described in Chapter 9.
Use the setup_DataGenerator procedure on all tables with data that need to be scripted:
The result will be a script that consists of Insert statements (which had to be cropped to fit the page):
----------------------------------------------------------- Insert into AcquisitionType (AcquisitionTypeId,AcquisitionType) values Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values Insert into AcquisitionType(AcquisitionTypeId,AcquisitionType) values
----------------------------------------------------------- Insert into EqType(EqTypeId,EqType) values (1,'Desktop') Insert into EqType(EqTypeId,EqType) values (2,'Notebook') Insert into EqType(EqTypeId,EqType) values (3,'Monitor') Insert into EqType(EqTypeId,EqType) values (4,'Ink Jet Printer') ...
Save the resulting scripts in a text file (I often use Database - Data.sql as the name of this file).
Alternatively, you can use Visual Studio .NET to script data and add it to Visual SourceSafe:
Open Server Explorer, navigate through the nodes, and expand the Tables node in the Asset database.
Select the tables with seed data (such as AcquisitionType, EqType, OrderStatus, and OrderType).
Right-click the selection and select Export Data from the menu.
The program prompts you for Locations For Exported Data File and to confirm that you want to export the selected data. The default location will be the folder that contains the Create scripts you generated earlier.
When you confirm the export operation, the program generates a set of DAT files. You typically need to select the files in Solution Explorer and Check (them) In.
These files are not SQL Server scripts but simple binary files (see the content of a file in Figure 11-8).
Figure 11-8.Content of DAT file
Deploying Scripts: Traditional Approach
The deployment scripts can then be executed manually one by one in Query Analyzer, but I have created a stored procedure that allows me to automate execution of a set of scripts—prBatchExec.
To prepare for deployment, I need to create a list of scripts and save it in a text file. The procedure executes the scripts in the order in which they are listed in the text file:
-- list of deployment scripts for Asset database Asset - database.DBS Asset - UDT.sql Asset - Table.sql Asset - DRI.sql Asset - Functions.sql Asset - sp.sql Asset - Views.sql
Although Deferred Name Resolution allows you to ignore the order of creation of stored procedures, there are still some dependencies that must be followed. For example, indexes must be created after tables, tables after user-defined data types, and all of them after the database has been initiated. With this in mind, one of the main advantages of prBatchExec is that it preserves the order of execution of files. No human intervention is required and the opportunity for error is reduced.
The procedure uses SQL Distributed Management Objects (SQL-DMO) to execute individual scripts against the database server. SQL-DMO is a set of COM objects that encapsulate the functionality needed for administering SQL Server. To use SQL-DMO from SQL Server, you have to use the system stored procedures for OLE Automation (COM), described in Chapter 10:
create proc prBatchExec -- Execute all sql files in the specified folder using the alphabetical order. -- Demonstration of use of OLE Automation. @ServerName sysname = '(local)\ss2k2', @UserId sysname = 'sa', @PWD sysname = 'my,password', @DirName varchar(400)='C:\dbScripter\test', @File varchar(400) = 'list.txt', @UseTransaction int = 0 as set nocount on declare @FileSystemObject int, @objSQL int, @hr int, @property varchar(255), @return varchar(255), @TextStream int, @BatchText varchar(8000), @FilePath varchar(500), @ScriptId varchar(200), @Cmd varchar(1000) --- Get list of files create table #FileList (ScriptId int identity(1,1), FileName varchar(500)) select @Cmd = 'cd ' + @DirName + ' & type ' + @File insert #FileList (FileName) exec master..xp_cmdshell @Cmd -- remove empty rows and comments delete #FileList where FileName is null delete #FileList where FileName like '--%' -- prepare COM to connect to SQL Server EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objSQL OUTPUT IF @hr < 0 BEGIN print 'error create SQLDMO.SQLServer' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END EXEC @hr = sp_OAMethod @objSQL, 'Connect', NULL, @ServerName, @UserId, @PWD IF @hr < 0 BEGIN print 'error Connecting' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END EXEC @hr = sp_OAMethod @objSQL, 'VerifyConnection', @return OUTPUT IF @hr < 0 BEGIN print 'error verifying connection' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END -- prepare file system object EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUTPUT IF @hr < 0 BEGIN print 'error create FileSystemObject' exec sp_displayoaerrorinfo @FileSystemObject, @hr RETURN END -- begin transaction if @UseTransaction <> 0 BEGIN EXEC @hr = sp_OAMethod @objSQL, 'BeginTransaction ' IF @hr < 0 BEGIN print 'error BeginTransaction' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END END -- iterate through the temp table to get actual file names select @ScriptId = Min (ScriptId) from #FileList WHILE @ScriptId is not null BEGIN select @FilePath = @DirName + '\' + FileName from #FileList where ScriptId = @ScriptId if @FilePath <> '' BEGIN print 'Executing ' + @FilePath EXEC @hr = sp_OAMethod @FileSystemObject, 'OpenTextFile', @TextStream output, @FilePath IF @hr < 0 BEGIN print 'Error opening TextFile ' + @FilePath exec sp_displayoaerrorinfo @FileSystemObject, @hr RETURN END EXEC @hr = sp_OAMethod @TextStream, 'ReadAll', @BatchText output IF @hr < 0 BEGIN print 'Error using ReadAll method.' exec sp_displayoaerrorinfo @TextStream, @hr RETURN END -- print @BatchText -- run it. EXEC @hr = sp_OAMethod @objSQL, 'ExecuteImmediate', Null , @BatchText IF @hr <> 0 BEGIN if @UseTransaction <> 0 BEGIN EXEC @hr = sp_OAMethod @objSQL, 'RollbackTransaction ' IF @hr < 0 BEGIN print 'error RollbackTransaction' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END END print 'Error ExecuteImmediate.' --Transaction will be rolled back.' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END EXECUTE sp_OADestroy @TextStream END print 'Finished executing ' + @FilePath select @ScriptId = Min(ScriptId) from #FileList where ScriptId > @ScriptId end print 'Finished executing all files.' drop table #FileList EXECUTE sp_OADestroy @FileSystemObject if @UseTransaction <> 0 BEGIN EXEC @hr = sp_OAMethod @objSQL, 'CommitTransaction ' IF @hr < 0 BEGIN print 'error CommitTransaction' exec sp_displayoaerrorinfo @objSQL, @hr RETURN END END RETURN 0
Before you can use the prBatchExec stored procedure, you must locate in SQL Server Books Online sp_displayoaerrorinfo and sp_hexadecimal and store them in the same database with prBatchExec (in other words, the Asset database).
To execute the prBatchExec procedure, you need to specify values for the parameters for the SQL Server instance, login, password, folder that contains your deployment scripts, and the name of the file containing the list of deployment scripts. You also need to decide whether deployment is to be performed as a transaction. Transactions cannot be used for initial deployment because database creation cannot be performed by a transaction. However, using transactions is very useful for incremental builds.
The prBatchExec procedure has one limitation. It can process only short (up to 8000 characters) scripts. I have decided to include it in this the book for two reasons. First, 8000 characters is probably enough for running an incremental build. Second, it’s educational—it demonstrates use of COM objects from Transact-SQL. For full builds, you have to use an updated version—prBatchExec3.
You can also download BatchExec.exe program from www.Trigonblue.com/sqlxml/sqlxml_download.htm. This is a console C# application and you can run it on computers that have the .NET Framework installed using
BatchExec (local)\ss2k2 sa my,password c:\script\test list.txt
Create scripts generated in Visual Studio .NET can also be “glued” together and deployed on other servers:
Select the Create Scripts folder in Solution Explorer, and then select Project | Create Command File.
Set the Name of Command File and move all or just some of the scripts in the Available Scripts list to the list of Scripts To Be Added To The Command File.
If you have moved some of the table files (TAB) that have data files (DAT) associated with them, the Add Data button becomes available. Click the button and the program prompts you to confirm associations between files:
You probably do not need to change anything, so just click OK and the program returns you to the previous screen.
Click OK again and the program generates a command file (or batch file) that can be used to execute all Create scripts on any server (see Figure 11-9).
Figure 11-9.Command file for deploying Create scripts
Incremental Build: Traditional Approach
Whichever method you choose for performing a full build of the database, you will eventually need to deploy design changes and hotfixes while preserving data in the database. Such changes can even accumulate over time. Typically, code changes for procedures can simply be executed in their latest form against the production database, but changes to the database structure (tables) must be implemented in such a way that they preserve data.
prBatchExec is very useful for deploying incremental changes on the database server. Individual changes to database objects can be grouped by defect number or version number (see Figure 11-10).
It is especially useful to run the process as a transaction in this case. If an unexpected error occurs during the deployment, it is preferable to roll back all the changes, leaving the production system intact.
TIP
Once you assemble deployment scripts, it is critical to perform sufficient unit testing. You can run the scripts against a new server or a new instance on the existing server and test the changes. You should repeat the deployment, fixing issues that you find, until it runs without a glitch. The ultimate test is whether the application can work with the database system without additional intervention.
Figure 11-10. Deployment script for incremental build
Incremental Build in Visual Studio .NET
Alternatively, you can create incremental scripts in Visual Studio .NET. In this case, you create Change scripts and manage them in the folder of the same name (in Solution Explorer). You should again create a command file, but you should probably name it differently. You will use the same techniques and methods that have already been described regarding the full build in the earlier section “Scripting Data in Visual Studio .NET.”
TIP You should pay special attention to avoid mixing versions of files and to execute database changes in the right order. It is a good idea to add sequential numbers or the date and time at the beginning of filenames. You can store different releases in different folders, or you can have different command files if you keep all change scripts in the same folder. When you are done, test, test, and test again.
MS SQL Server 
/ 17 
ASP.NET Export to Excel and Word using VB.NET and C#
