Source Code Management and Database Deployment - Scripting Data in Visual Studio .NET

(Page 8 of 9 )

Alternatively, you can use Visual Studio .NET to script data and add it to Visual SourceSafe:

1. Open Server Explorer, navigate through the nodes, and expand the Tables node in the Asset database.

2. Select the tables with seed data (such as AcquisitionType, EqType, OrderStatus, and OrderType).

3. Right-click the selection and select Export Data from the menu.

4. 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.

5. 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

Next: Deploying Create Scripts in Visual Studio .NET >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Source Code Management and Database Deployment

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from SQL Server 2000 Stored Procedure & XML Programming, second edition, written by Dejan Sunderic (McGraw-Hill/Osborne, 2004; ISBN: 0072228962). Check it out at your favorite bookstore today. Buy this book now.