Executing SQL in a PowerShell Hook Script (KBA1370)

-- This script is used to turn read_committed_snapshot on.
 
SET NOCOUNT ON;
USE [master];
GO
 
BEGIN TRY 
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET MULTI_USER WITH NO_WAIT;
    PRINT 'Database [$(VDB_DATABASE_NAME)] successfully configured with RCSI';
END TRY 
BEGIN CATCH 
    PRINT 'Error caught:';
    SELECT  
        ERROR_NUMBER() AS ErrorNumber 
        ,ERROR_MESSAGE() AS ErrorMessage; 
    -- If failure of this operation is not critical to VDB operations, attempt to recover.
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET MULTI_USER WITH NO_WAIT;
    -- If failure of this operation is dangerous to VDB operations, raise the error to the PowerShell script by uncommenting the line below.
    -- A failed hook operation will trigger a failure of the provisioning job.
    -- RAISERROR ('Setting read_committed_snapshot failed.', 11, 127) WITH NOWAIT, SETERROR;
END CATCH;

#
# Copyright (c) 2019 by Delphix. All rights reserved.
#

Set-Variable UTF8_CODEPAGE 65001 -option readonly
Set-Variable UTF8_ENCODING "System.Text.UTF8Encoding" -option readonly

# Uncomment the following log to turn on debugging
# Set-PSDebug -Trace 2;

$VDB_HOST = $env:VDB_INSTANCE_HOST
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_INSTANCE = $env:VDB_INSTANCE_NAME
$CONNECT_STRING = "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT"

$script:ErrorActionPreference = "Continue"

function die {
    Write-Error "Error: $($args[0])"

    # run exit handler, if defined
    if (Get-Command -type Function -name atExit 2> $null) {
        atExit
    }
    exit 1
}

function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}

# Set the codepage to UTF-8, suppressing the command output that tells us the active code page
chcp $UTF8_CODEPAGE >$null
verifySuccess "Failed to set codepage to $UTF8_CODEPAGE (UTF-8)"

# And tell Powershell that, yes, we really would like UTF-8 output.
$OutputEncoding = New-Object -typename $UTF8_ENCODING
verifySuccess "Failed to set `$OutputEncoding to $UTF8_ENCODING"

sqlcmd -l 30 -b -S "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT" -W -E -s ";" -i "C:\Users\delphixaduser\SetDatabaseOption.sql"  -o "C:\Users\Delphixaduser\DelphixPSLog_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).log"

# sqlcmd -l 30 -b -S "localhost\SQL2008R2,62790" -W -E -s ";" -i "C:\Users\delphixaduser\SetDatabaseOption.sql"  -o "C:\Users\Delphixaduser\DelphixPSLog_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).log"

verifySuccess "SQLCMD_FAILED"

# Uncomment the following line to turn off debugging
# Set-PSDebug -Trace 0;