---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第18章 データベースの配置
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 18.1 データベース全体の配置：従来の方法
---------------------------------------------------------------------

EXEC sp_detach_db 'Asset5'


EXEC sp_attach_db @dbname = 'Asset5',
                  @filename1 = 'C:\Program Files\Microsoft SQL ',
                             + 'Server\MSSQL.1\MSSQL\Data\Asset5.mdf'
                  @filename2 = 'C:\Program Files\Microsoft SQL '
                            + 'Server\MSSQL.1\MSSQL\Data\Asset5_log.ldf'

EXEC sp_attach_single_file_db
                 @dbname = 'Asset5',
                 @physname = 'C:\Program Files\Microsoft SQL ',
                          + 'Server\MSSQL.1\MSSQL\Data\Asset5.mdf'

---------------------------------------------------------------------
-- 18.2 個々のオブジェクトの配置
---------------------------------------------------------------------

SET NOCOUNT ON
EXEC util.ap_DataGenerator 'AcquisitionType'
EXEC util.ap_DataGenerator 'EqType'
EXEC util.ap_DataGenerator 'Location'
EXEC util.ap_DataGenerator 'OrderStatus'
EXEC util.ap_DataGenerator 'OrderType'
EXEC util.ap_DataGenerator 'Status'
EXEC util.ap_DataGenerator 'Province'


-- Asset5データベースの配置スクリプトのリスト
Asset5 - database.DBS
Asset5 - UDT.sql
Asset5 - Table.sql
Asset5 - DRI.sql
Asset5 - Functions.sql
Asset5 - sp.sql
Asset5 - Views.sql


CREATE PROC util.ap_BatchExec_OA
-- 指定したフォルダ内のすべてのSQLスクリプトファイルをアルファベット順に実行する
-- OLEオートメーションの使い方のデモンストレーション
      @ServerName SYSNAME = '(local)\rc',
      @UserId SYSNAME = 'sa',
      @PWD SYSNAME = 'my,password',
      @DirName VARCHAR(400)='C:\sql\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(max),
        @FilePath VARCHAR(500),
        @ScriptId VARCHAR(200),
        @Cmd VARCHAR(1000)

-- ファイルのリストを取得する
CREATE TABLE #FileList(ScriptId INT IDENTITY(1, 1),
                        FileName VARCHAR(500))

SELECT  @Cmd = 'cd ' + @DirName + ' & type ' + @File

INSERT #FileList(FileName)
EXEC master.sys.xp_cmdshell @Cmd

-- 空行とコメントを削除する
DELETE #FileList WHERE FileName IS NULL
DELETE #FileList WHERE FileName LIKE '--%'

-- SQL Serverに接続するためにCOMを準備する
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @objSQL OUTPUT
IF @hr < 0
BEGIN
    PRINT 'error create SQLDMO.SQLServer'
    EXEC sys.sp_OAGetErrorInfo @objSQL, @hr
    RETURN
END

EXEC @hr = sp_OAMethod @objSQL, 'Connect', NULL, @ServerName, @UserId, @PWD
IF @hr < 0
BEGIN
    PRINT 'error Connecting'
    EXEC sys.sp_OAGetErrorInfo @objSQL, @hr
    RETURN
END 

EXEC @hr = sp_OAMethod @objSQL, 'VerifyConnection', @return OUTPUT
IF @hr < 0
BEGIN
    PRINT 'error verifying connection'
    EXEC sys.sp_OAGetErrorInfo @objSQL, @hr
    RETURN
END

-- ファイルシステムオブジェクトを準備する
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUTPUT
IF @hr < 0
BEGIN
    PRINT 'error create FileSystemObject'
    EXEC sp_OAGetErrorInfo @FileSystemObject, @hr
    RETURN
END

-- トランザクションを開始する
IF @UseTransaction <> 0
BEGIN
    EXEC @hr = sp_OAMethod @objSQL, 'BeginTransaction '
    IF @hr < 0
    BEGIN
        PRINT 'error BeginTransaction'
        EXEC sp_OAGetErrorInfo @objSQL, @hr
        RETURN
    END
END

-- 一時テーブルをループ処理して実際のファイル名を取得する
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_OAGetErrorInfo @FileSystemObject, @hr
            RETURN
        END
        EXEC @hr = sp_OAMethod @TextStream, 'ReadAll', @BatchText OUTPUT
        IF @hr < 0
        BEGIN
            PRINT 'Error using ReadAll method.'
            EXEC sp_OAGetErrorInfo @TextStream, @hr
            RETURN
        END

        -- @BatchTextを表示し、実行する
        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_OAGetErrorInfo @objSQL, @hr
                   RETURN
               END
           END
           PRINT 'Error ExecuteImmediate.' -- トランザクションをロールバック
           EXEC sp_OAGetErrorInfo @objSQL, @hr
           RETURN
        END

        EXEC 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
EXEC sp_OADestroy @FileSystemObject

IF @UseTransaction <> 0
BEGIN
    EXEC @hr = sp_OAMethod @objSQL, 'CommitTransaction '
    IF @hr < 0
    BEGIN
        PRINT 'error CommitTransaction'
        EXEC sp_OAGetErrorInfo @objSQL, @hr
        RETURN
    END
END

RETURN


CREATE PROC util.ap_BatchExec8
-- 指定したSQLスクリプトファイルを実行する
    @ServerName SYSNAME = '.\rc',
    @UserId SYSNAME = 'sa',
    @PWD SYSNAME = 'my,password',
    @DirName VARCHAR(400) = 'C:\sql\test',
    @File VARCHAR(400) = 'list.txt',
    @UseTransaction INT = 0,
    @debug INT = 0
AS

SET NOCOUNT ON

DECLARE @FilePath VARCHAR(500),
        @FileId INT,
        @MaxFileID INT,
        @OldFileId INT,
        @Cmd VARCHAR(1000),
        @i INT,
        @iOld INT,
        @max INT,
        @s VARCHAR(max),
        @line VARCHAR(max)

-- ファイルのリストを取得する
CREATE TABLE #FileList(FileId INT IDENTITY(1, 1),
                       FileName VARCHAR(500))

SELECT @Cmd = 'cd ' + @DirName + ' & type ' + @File

INSERT #FileList(FileName)
EXEC master.sys.xp_cmdshell @Cmd

-- 空行とコメントを削除する
DELETE #FileList WHERE FileName IS NULL
DELETE #FileList WHERE FileName LIKE '--%'

IF @debug <> 0
    SELECT * FROM #FileList

CREATE TABLE #script(SQL VARCHAR(max),
                     LineId INT IDENTITY)

SELECT @FileId = MIN(FileId),
       @MaxFileID = MAX(FileId)
FROM #FileList

-- ファイルをループする
WHILE @FileId <= @MaxFileID
BEGIN
    -- 処理するファイルの名前を取得する
    SELECT @FilePath = @DirName + '\' + FileName
    FROM #FileList
    WHERE FileId = @FileId

    IF @FilePath <> ''
    BEGIN
        IF @debug <> 0
            PRINT 'Reading ' + @FilePath

        SET @cmd = 'Type "' + @FilePath + '"'

        INSERT #script(SQL)
        EXEC master.sys.xp_cmdshell @Cmd

        SELECT  @i = MIN(LineId),
                @max = MAX(LineId),
                @s = ''
        FROM #script

        WHILE @i <= @max
        BEGIN
            SELECT @line = COALESCE(SQL, ' ')
            FROM #script
            WHERE LineId = @i

            IF @debug <> 0
                SELECT 'read line =', @i i, @line line

            IF LEFT(@line, 2) <> 'GO'
            BEGIN
                -- lineとgoは次の回
                SELECT @s = @s + CHAR(13) + CHAR(10) + @line
                IF @debug <> 0
                    SELECT @s [@s]
            END
            ELSE
            BEGIN
                BEGIN TRY
                    IF @debug = 0
                        EXEC sp_sqlexec @s
                    ELSE
                        SELECT @s
                END TRY
                BEGIN CATCH
                    PRINT ERROR_MESSAGE()
                    PRINT 'Process stopped.'
                    RETURN
                END CATCH
                SET @s = ''
            END
            -- 1行ずつ継続する
            SET @iOld = @i
            SELECT @i = MIN(LineId)
            FROM #script
            WHERE LineId > @iOld
        END
    END
    -- 次のファイルを取得する
    SET @FileID = @FileId + 1
    SELECT @fileID FileId

    TRUNCATE TABLE #script
END
RETURN


SET XACT_ABORT ON
BEGIN TRAN
EXEC util.ap_BatchExec8 "(local)\ss2k5", "sa", "my,password", "C:\script\test list.txt"
COMMIT TRAN


TbDbBatchExec (local)\ss2k5 sa my,password C:\script\test list.txt

