---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第21章 SQL Serverのその他の機能
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 21.1 プログラムの実行
---------------------------------------------------------------------

EXEC master..xp_cmdshell 'COPY E:\w2kPro~1\Micros~1\'
                    + 'MSSQL\BACKUP\*.* M:', no_output

---------------------------------------------------------------------
-- 21.2 Windowsスクリプトファイルの実行
---------------------------------------------------------------------

EXEC xp_cmdshell 'C:\windows\command\cscript.exe '
     + 'C:\windows\samples\wsh\Excel.vbs', no_output

---------------------------------------------------------------------
-- 21.3 OLEオートメーション／COMオブジェクトの実行
---------------------------------------------------------------------

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO


CREATE PROC dbo.ap_SpellNumber
-- オートメーションオブジェクトの使用のデモンストレーション
    @mnsAmount MONEY,
    @chvAmount VARCHAR(500) OUTPUT,
    @debug INT = 0
AS
    SET NOCOUNT ON

    DECLARE @intErrorCode INT,
            @intObject INT,  -- オブジェクトのトークンを保持
            @bitObjectCreated BIT,
            @chvSource varchar(255),
            @chvDesc varchar(255)

    SELECT @intErrorCode = @@ERROR

    IF @intErrorCode = 0
        EXEC @intErrorCode = sp_OACreate 'DjnToolkit.DjnTools',
                                          @intObject OUTPUT

    IF @intErrorCode = 0
        SET @bitObjectCreated = 1
    ELSE
        SET @bitObjectCreated = 0

    IF @intErrorCode = 0
        EXEC @intErrorCode = sp_OAMethod @intObject,
                                         'SpellNumber',
                                         @chvAmount OUTPUT,
                                         @mnsAmount

    IF @intErrorCode <> 0
    BEGIN
        RAISERROR(' 数値を表す単語を取得できません', 16, 1)
        EXEC sp_OAGetErrorInfo @intObject,
                               @chvSource OUTPUT,
                               @chvDesc OUTPUT
        SET @chvDesc = 'Error ('
                     + CONVERT(VARCHAR, @intErrorCode)
                     + ', ' + @chvSource  + ') : ' + @chvDesc
        RAISERROR (@chvDesc, 16, 1)
    END

    IF @bitObjectCreated = 1
        EXEC sp_OADestroy @intObject

    RETURN @intErrorCode

---------------------------------------------------------------------
-- 21.4 SQL Server 2005 Integration Servicesパッケージの実行
---------------------------------------------------------------------

EXEC xp_cmdshell ' dtexec /F "C:\SSIS\DW_ETL.dtsx"', no_output


EXEC xp_cmdshell ' dtexec /SQ DW_ETL /Ser myServer', no_output

---------------------------------------------------------------------
-- 21.5 DTSパッケージの実行
---------------------------------------------------------------------

EXEC xp_cmdshell 'dtsrun /SmyServer /Udbo /E /NDW_ETL '
              +  '/ADatabase:8=Asset5 /AServer:8=myServer', no_output


CREATE PROC  util.ap_DTS_Loop
-- Assetデータベースをループする
-- 各ループでDTSパッケージを実行する
    @debug int = 0
AS

SET NOCOUNT ON
DECLARE @intCount INT,
        @intCounter INT,
        @chvDOS VARCHAR(2000),
        @chvDB SYSNAME,
        @chvServer SYSNAME

DECLARE @intErrorCode INT,
        @chvProcedure SYSNAME

SET XACT_ABORT ON
SET NOCOUNT ON

SET @chvProcedure = 'util.ap_DTS_Loop'

IF @debug <> 0
    SELECT '**** '+ @chvProcedure + ' START ****'

CREATE TABLE #db(Id INT IDENTITY(1, 1),
                 Name SYSNAME)

INSERT INTO #db(Name)
    SELECT name FROM master.dbo.sysdatabases
    WHERE name LIKE 'Asset%'

-- ループを設定する
SELECT @intCount = COUNT(*),
       @intCounter = 1,
       @chvServer = @@SERVERNAME
FROM #db

-- データベースリスト全体をループする
WHILE @intCounter <= @intCount
BEGIN
    -- データベース名を取得する
    SELECT @chvDB = name
    FROM #db
    WHERE Id = @intCounter

    SELECT @chvDOS = 'dtsrun /S' + @chvServer
                   + ' /Udbo /E /NDW_ETL'
                   + ' /ADatabase:8=' + @chvDB
                   + ' /AServer:8=' + @chvServer

    IF @debug = 0
        EXEC master.dbo.xp_cmdshell @chvDOS, no_output
    ELSE
        SELECT @chvDOS

    -- 前に戻って次のプロパティを取得する
    SET @intCounter = @intCounter + 1
END

DROP TABLE #db

IF @debug <> 0
    SELECT '**** '+ @chvProcedure + ' END ****'
RETURN


EXEC util.ap_DTS_Loop 1

---------------------------------------------------------------------
-- 21.6 レジストリの利用
---------------------------------------------------------------------

DECLARE @chvSQLPath VARCHAR(8000)

EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
    'BackupDirectory',
    @chvSQLPath OUTPUT

SELECT @chvSQLPath SQLPath
GO


EXEC master.dbo.xp_regwrite
    'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
    'Test',
    'REG_SZ',
    'Test'
GO

---------------------------------------------------------------------
-- 21.7 ジョブ
---------------------------------------------------------------------

EXEC util.ap_LogBackupIfAlmostFull 'Asset5', 95


CREATE PROC util.ap_LogBackupIfAlmostFull
-- ログ領域の使用率が@FltPercentLimitにセットされた数値を超えている場合、
-- トランザクションログのバックアップを取る
(
    @chvDbName SYSNAME,
    @fltPercentLimit FLOAT,
    @debug INT = 0
)
AS

    SET NOCOUNT ON
    SET XACT_ABORT ON

    DECLARE @intErrorCode INT,
            @fltPercentUsed FLOAT,
            @chvDeviceName SYSNAME,
            @chvFileName SYSNAME,
            @chvSQLPath VARCHAR(8000)

    -- この時点でログ領域がどれだけ使用されているか確認
    EXEC util.ap_LogSpacePercentUsed_Get @chvDbName,
                                         @fltPercentUsed OUTPUT

    -- 制限に達していない場合は何もしない
    IF @fltPercentUsed < @fltPercentLimit
        RETURN

    EXEC master.dbo.xp_regread
        'HKEY_LOCAL_MACHINE',
        'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
        'BackupDirectory',
        @chvSQLPath OUTPUT

    SELECT @chvDeviceName = @chvDbName
                          + CONVERT(VARCHAR, GETDATE(), 112),
           @chvFileName   = @chvSQLPath + '\'
                          + @chvDeviceName
                          + '.bkp'

    IF @debug <> 0
        SELECT @chvDeviceName chvDeviceName,
               @chvFileName chvFileName

    SET XACT_ABORT OFF
    BEGIN TRY
        EXEC sys.sp_addumpdevice 'disk', @chvDeviceName, @chvFileName
    END TRY
    BEGIN CATCH
        SELECT @intErrorCode = ERROR_NUMBER();
    END CATCH
    SET XACT_ABORT ON

    -- バックアップデバイスが既に存在しているため、エラー番号15026は問題なし
    IF @intErrorCode = 0 OR @intErrorCode = 15026
        BACKUP LOG @chvDbName TO @chvDeviceName

    RETURN


EXEC util.ap_LogBackupIfAlmostFull 'Asset5', 0, 1


USE msdb
EXEC sp_add_job @job_name = 'Asset Backup Log',
    @enabled = 1,
    @description = 'Backup transaction Log of Asset database',
    @owner_login_name = 'sa'

EXEC sp_add_jobserver @job_name = 'Asset Backup Log',
    @server_name = 'DSUNDERIC\ss2k'

EXEC sp_add_jobstep @job_name = 'Asset Backup Log',
    @step_name = 'Backup Log',
    @subsystem = 'TSQL',
    @server =  'DSUNDERIC\ss2k5',
    @command = ' BACKUP LOG Asset TO bkpAssetLog',
    @retry_attempts = 5,
    @retry_interval = 5

EXEC sp_add_jobschedule @job_name = 'Asset Backup Log ',
    @name = 'Nightly Backup',
    @freq_type = 4,               -- 毎日実行
    @freq_interval = 1,           -- 隔日
    @active_start_time = '000000' -- 深夜


USE msdb
EXEC sp_start_job @job_name = 'Asset Backup Log'

---------------------------------------------------------------------
-- 21.8 電子メール
---------------------------------------------------------------------

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO


DECLARE @mailitem_id INT
EXEC @mailitem_id = msdb.dbo.sp_send_dbmail
     @profile_name = 'Asset Admin',
     @recipients = 'dejan@asset.co.yu',
     @body = 'The ETL procedure completed successfully.',
     @subject = 'Automated Completion Message';
SELECT @mailitem_id [@mailitem_id]


DECLARE @mailitem_id INT
EXEC @mailitem_id = msdb.dbo.sp_send_dbmail
     @profile_name = 'Asset Admin',
     @recipients = 'admin@asset.co.yu',
     @query = 'SELECT * FROM Asset5.dbo.OrderHeader
              WHERE TargetDate > CONVERT(SMALLDATETIME,
              CONVERT(VARCHAR(30), DATEADD(d, 1, GETDATE()), 101))
              AND OrderStatusId = 1',
    @attach_query_result_as_file = 1;

SELECT @mailitem_id [@mailitem_id]


SELECT * FROM msdb.dbo.sysmail_allitems
WHERE mailitem_id = 123

---------------------------------------------------------------------
-- 21.9 Webサービスとして公開するストアドプロシージャ
---------------------------------------------------------------------

CREATE ENDPOINT ept_AssetInventory
       STATE = STARTED
   AS HTTP
   (
       PATH = '/AssetInventory',
       AUTHENTICATION = (INTEGRATED),
       PORTS = (CLEAR),
       SITE = 'LG'
   )
   FOR SOAP
   (
       WEBMETHOD 'AssetInventoryQuickByMakeModel'
           (NAME='Asset5.dbo.ap_InventoryByMakeModel_Quick3'),
       WEBMETHOD 'AssetPhoto'
           (NAME='Asset5.dbo.ap_InventoryByMakeModel_Count'),
       WEBMETHOD 'AssetProperties'
           (NAME='Asset5.dbo.ap_InventoryProperties_Get'),
       BATCHES = DISABLED,
       WSDL = DEFAULT,
       DATABASE = 'Asset5',
       NAMESPACE = 'http://Asset5/AssetInventory'
   )

