text: ﻿/*
  Deployment script for DVLog
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "DVLog"
  :setvar DefaultFilePrefix "DVLog"
  :setvar DefaultDataPath ""
  :setvar DefaultLogPath ""
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [master];
  
  
  GO
  
  IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
  BEGIN
      ALTER DATABASE [$(DatabaseName)]
      SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      DROP DATABASE [$(DatabaseName)];
  END
  
  GO
  PRINT N'Creating $(DatabaseName)...'
  GO
  CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
  GO
  USE [$(DatabaseName)];
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET ANSI_NULLS ON,
                  ANSI_PADDING ON,
                  ANSI_WARNINGS ON,
                  ARITHABORT ON,
                  CONCAT_NULL_YIELDS_NULL ON,
                  NUMERIC_ROUNDABORT OFF,
                  QUOTED_IDENTIFIER ON,
                  ANSI_NULL_DEFAULT ON,
                  CURSOR_DEFAULT LOCAL,
                  RECOVERY SIMPLE,
                  CURSOR_CLOSE_ON_COMMIT OFF,
                  AUTO_CREATE_STATISTICS ON,
                  AUTO_SHRINK OFF,
                  AUTO_UPDATE_STATISTICS ON,
                  RECURSIVE_TRIGGERS OFF 
              WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [$(DatabaseName)]
              SET AUTO_CLOSE OFF 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET ALLOW_SNAPSHOT_ISOLATION OFF;
      END
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET READ_COMMITTED_SNAPSHOT ON 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                  PAGE_VERIFY NONE,
                  DATE_CORRELATION_OPTIMIZATION OFF,
                  DISABLE_BROKER,
                  PARAMETERIZATION SIMPLE,
                  SUPPLEMENTAL_LOGGING OFF 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  IF IS_SRVROLEMEMBER(N'sysadmin') = 1
      BEGIN
          IF EXISTS (SELECT 1
                     FROM   [master].[dbo].[sysdatabases]
                     WHERE  [name] = N'$(DatabaseName)')
              BEGIN
                  EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
      SET TRUSTWORTHY ON,
          DB_CHAINING OFF 
      WITH ROLLBACK IMMEDIATE';
              END
      END
  ELSE
      BEGIN
          PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
      END
  
  
  GO
  IF IS_SRVROLEMEMBER(N'sysadmin') = 1
      BEGIN
          IF EXISTS (SELECT 1
                     FROM   [master].[dbo].[sysdatabases]
                     WHERE  [name] = N'$(DatabaseName)')
              BEGIN
                  EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
      SET HONOR_BROKER_PRIORITY OFF 
      WITH ROLLBACK IMMEDIATE';
              END
      END
  ELSE
      BEGIN
          PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
      END
  
  
  GO
  ALTER DATABASE [$(DatabaseName)]
      SET TARGET_RECOVERY_TIME = 0 SECONDS 
      WITH ROLLBACK IMMEDIATE;
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                  CONTAINMENT = NONE 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
      EXECUTE sp_fulltext_database 'enable';
  
  
  GO
  PRINT N'Creating [LogWriter]...';
  
  
  GO
  CREATE ROLE [LogWriter]
      AUTHORIZATION [dbo];
  
  
  GO
  PRINT N'Creating [log]...';
  
  
  GO
  CREATE SCHEMA [log]
      AUTHORIZATION [dbo];
  
  
  GO
  PRINT N'Creating [dbo].[sysssislog]...';
  
  
  GO
  CREATE TABLE [dbo].[sysssislog] (
      [id]          INT              IDENTITY (1, 1) NOT NULL,
      [event]       [sysname]        NOT NULL,
      [computer]    NVARCHAR (128)   NOT NULL,
      [operator]    NVARCHAR (128)   NOT NULL,
      [source]      NVARCHAR (1024)  NOT NULL,
      [sourceid]    UNIQUEIDENTIFIER NOT NULL,
      [executionid] UNIQUEIDENTIFIER NOT NULL,
      [starttime]   DATETIME         NOT NULL,
      [endtime]     DATETIME         NOT NULL,
      [datacode]    INT              NOT NULL,
      [databytes]   IMAGE            NULL,
      [message]     NVARCHAR (2048)  NOT NULL,
      CONSTRAINT [PK_sysssislog] PRIMARY KEY CLUSTERED ([id] ASC)
  );
  
  
  GO
  PRINT N'Creating [log].[FlowExecutionError]...';
  
  
  GO
  CREATE TABLE [log].[FlowExecutionError] (
      [Id]               BIGINT           IDENTITY (1, 1) NOT NULL,
      [ExecutionId]      UNIQUEIDENTIFIER NOT NULL,
      [ErrorCode]        INT              NOT NULL,
      [ErrorDescription] VARCHAR (MAX)    NOT NULL,
      CONSTRAINT [PK_FlowExecutionError] PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [log].[FlowEvent]...';
  
  
  GO
  CREATE TABLE [log].[FlowEvent] (
      [FlowEventId]   INT          NOT NULL,
      [FlowEventName] VARCHAR (50) NOT NULL,
      CONSTRAINT [PK_FlowEvent] PRIMARY KEY CLUSTERED ([FlowEventId] ASC)
  );
  
  
  GO
  PRINT N'Creating [log].[FlowExecution]...';
  
  
  GO
  CREATE TABLE [log].[FlowExecution] (
      [Id]                BIGINT           IDENTITY (1, 1) NOT NULL,
      [LoadDate]          DATETIME2 (7)    NOT NULL,
      [FlowName]          VARCHAR (100)    NOT NULL,
      [ExecutionId]       UNIQUEIDENTIFIER NOT NULL,
      [ServerExecutionId] INT              NOT NULL,
      [PackageName]       VARCHAR (100)    NOT NULL,
      [PackageId]         UNIQUEIDENTIFIER NOT NULL,
      [VersionBuild]      INT              NOT NULL,
      [FlowEventId]       INT              NOT NULL,
      [FlowEventDate]     DATETIME2 (7)    NOT NULL,
      CONSTRAINT [PK_FlowExecution] PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [log].[vFlowExecutionError]...';
  
  
  GO
  CREATE VIEW [log].[vFlowExecutionError]
  
  AS
  
  SELECT [Id]
    ,[ExecutionId]
    ,[ErrorCode]
    ,[ErrorDescription]
  FROM [log].[FlowExecutionError]
  WHERE [ErrorCode] != 0
  GO
  PRINT N'Creating [log].[vFlowExecution]...';
  
  
  GO
  CREATE VIEW [log].[vFlowExecution]
  
  AS 
  
  SELECT ExecStart.[Id]
    ,ExecStart.[LoadDate]
    ,ExecStart.[FlowName]
    ,ExecStart.[ExecutionId]
    ,ExecStart.[ServerExecutionId]
    ,ExecStart.[PackageName]
    ,ExecStart.[PackageId]
    ,ExecStart.[VersionBuild]
    ,[FlowEventId] = ISNULL(ExecEnd.[FlowEventId], ExecStart.[FlowEventId])
    ,[FlowEventStartDate] = ExecStart.[FlowEventDate]
    ,[FlowEventEndDate] = ExecEnd.[FlowEventDate]
  FROM [log].[FlowExecution] ExecStart 
    LEFT JOIN [log].[FlowExecution] ExecEnd ON ExecStart.ExecutionId = ExecEnd.ExecutionId
      AND ISNULL(ExecEnd.FlowEventId, 1) != 0
  WHERE ExecStart.FlowEventId = 0
  GO
  PRINT N'Creating [log].[InsertFlowExecutionLog]...';
  
  
  GO
  CREATE PROCEDURE [log].[InsertFlowExecutionLog]
  	@LoadDate datetime2
  	,@FlowName varchar(100)
  	,@ExecutionId uniqueidentifier
      ,@PackageName varchar(100)
      ,@PackageId uniqueidentifier
      ,@VersionBuild int
      ,@FlowEventId int
  	,@ServerExecutionId int
  AS
  BEGIN
    SET NOCOUNT ON;
  
    INSERT INTO log.FlowExecution
    (
      LoadDate
      ,FlowName
      ,ExecutionId
  	,ServerExecutionId
      ,PackageName
      ,PackageId
      ,VersionBuild
      ,FlowEventId
      ,FlowEventDate
    )
    VALUES
    (
      @LoadDate
  	,@FlowName
  	,@ExecutionId
  	,@ServerExecutionId
      ,@PackageName
      ,@PackageId
      ,@VersionBuild
      ,@FlowEventId
      ,SYSDATETIME()
    );
  END
  GO
  PRINT N'Creating [log].[InsertFlowExecutionErrorLog]...';
  
  
  GO
  CREATE PROCEDURE [log].[InsertFlowExecutionErrorLog]
  	@ExecutionId uniqueidentifier
  	,@ErrorCode int
      ,@ErrorDescription varchar(MAX)
  AS
  BEGIN
    SET NOCOUNT ON;
  
    INSERT INTO log.FlowExecutionError
    (
      ExecutionId
      ,ErrorCode
      ,ErrorDescription
    )
    VALUES
    (
      @ExecutionId
      ,@ErrorCode
      ,@ErrorDescription
    );
  END
  GO
  PRINT N'Creating Permission...';
  
  
  GO
  GRANT EXECUTE
      ON OBJECT::[log].[InsertFlowExecutionLog] TO [LogWriter]
      AS [dbo];
  
  
  GO
  PRINT N'Creating Permission...';
  
  
  GO
  GRANT EXECUTE
      ON OBJECT::[log].[InsertFlowExecutionErrorLog] TO [LogWriter]
      AS [dbo];
  
  
  GO
  /*
  Post-Deployment Script Template							
  --------------------------------------------------------------------------------------
   This file contains SQL statements that will be appended to the build script.		
   Use SQLCMD syntax to include a file in the post-deployment script.			
   Example:      :r .\myfile.sql								
   Use SQLCMD syntax to reference a variable in the post-deployment script.		
   Example:      :setvar TableName MyTable							
                 SELECT * FROM [$(TableName)]					
  --------------------------------------------------------------------------------------
  */
  IF NOT EXISTS (SELECT * FROM log.FlowEvent)
    INSERT INTO log.FlowEvent (FlowEventId, FlowEventName) VALUES
      (-1, 'Error')
      ,(0, 'Start')
      ,(1, 'End');
  
  GO
  
  GO
  DECLARE @VarDecimalSupported AS BIT;
  
  SELECT @VarDecimalSupported = 0;
  
  IF ((ServerProperty(N'EngineEdition') = 3)
      AND (((@@microsoftversion / power(2, 24) = 9)
            AND (@@microsoftversion & 0xffff >= 3024))
           OR ((@@microsoftversion / power(2, 24) = 10)
               AND (@@microsoftversion & 0xffff >= 1600))))
      SELECT @VarDecimalSupported = 1;
  
  IF (@VarDecimalSupported > 0)
      BEGIN
          EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
      END
  
  
  GO
  ALTER DATABASE [$(DatabaseName)]
      SET MULTI_USER 
      WITH ROLLBACK IMMEDIATE;
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  