Category Archives: Technical documents

Technical documents

Backup/Restore Progress

Posted on by 
— Check progress of any running BACKUP or RESTORE operations
SELECT
r.session_id,
r.command, — BACKUP DATABASE, RESTORE DATABASE, etc.
r.percent_complete, — Progress percentage
r.start_time, — When the operation started
r.estimated_completion_time / 1000 AS est_completion_seconds,
DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS est_completion_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
r.wait_type,
r.wait_time / 1000 AS wait_seconds,
r.last_wait_type,
t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN (‘BACKUP DATABASE’, ‘BACKUP LOG’, ‘RESTORE DATABASE’, ‘RESTORE LOG’, ‘RESTORE HEADERONLY’)
ORDER BY r.start_time;

Azure Copy Database

Posted on by 
–Commands for creating a copy of an existing Azure database and checking the progress of the copy.

/*
use master
go
CREATE DATABASE [DESTINATION-DB] AS COPY OF [SOURCE-SERVER].[SOURCE-DB];
go
*/

–select @@servername

/* – Whilst that copy is running open up another windows and check on its progress with the following:
use master
go
—-Check status of copying, Will say ONLINE when finished, until then it will say copying
select name, state_desc,collation_name
from sys.databases;

—-Check status of copying, more info, again check for ONLINE
select name, state_desc,collation_name, *
from sys.databases
–order by state_desc;

—much more information;what was source of copy, what server was the source
Select *
from sys.dm_database_copies;

Select *
from sys.dm_operation_status
*/

/* -After some days/weeks drop the copy of the database
use master
go
DROP DATABASE [DESTINATION-DB]
GO
*/

Autogrowth Script

Posted on by 
/*
— This script generates alter statements for the user databases in an instance and takes away any hard limits.
— The rule is minimum autogrow increment 100MB, between 500-5000MB, 500MB increment and above 5000MB, 1000MB increment
*/

SET NOCOUNT ON;

SELECT
d.name AS DatabaseName,
mf.name AS FileName,
mf.type_desc AS FileType,

— Current size
CAST(CAST(mf.size AS BIGINT) * 8 / 1024 AS BIGINT) AS SizeMB,

— Current FILEGROWTH
CASE
WHEN mf.is_percent_growth = 1
THEN CAST(mf.growth AS NVARCHAR(10)) + ‘%’
ELSE CAST(CAST(mf.growth AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentFileGrowth,

— Current MAXSIZE
CASE
WHEN mf.max_size = -1
THEN ‘UNLIMITED’
ELSE CAST(CAST(mf.max_size AS BIGINT) * 8 / 1024 AS NVARCHAR(20)) + ‘MB’
END AS CurrentMaxSize,

— Proposed growth
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 < 500 THEN 100
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 BETWEEN 500 AND 5000 THEN 500
ELSE 1000
END AS NewFileGrowthMB,

— Generated SQL
'ALTER DATABASE [' + d.name + '] MODIFY FILE ' +
'( NAME = N''' + mf.name + ''', ' +
'FILEGROWTH = ' +
CAST(
CASE
WHEN CAST(mf.size AS BIGINT) * 8 / 1024 4 — user databases only
AND d.state_desc = ‘ONLINE’
AND d.is_read_only = 0
AND d.is_distributor = 0
ORDER BY
d.name,
mf.type_desc,
mf.name;

Trigger Check

Posted on by 
— Query to check for any database triggers in a Database
SELECT
t.name,
t.is_disabled,
te.type_desc AS trigger_event
FROM sys.triggers t
JOIN sys.trigger_events te ON t.object_id = te.object_id
WHERE t.parent_class_desc = ‘DATABASE’;

–If found they can be disabled/enabled with:
/*
USE [Admin]
go
DISABLE TRIGGER tr_MStran_alterschemaonly ON DATABASE;
GO
USE [Admin]
go
ENABLE TRIGGER tr_MStran_alterschemaonly ON DATABASE;
GO
*/

Filesystem Info

Posted on by 
/*
Checking contents and size of filesystems
*/

SELECT file_or_directory_name
, level, is_directory, creation_time, (size_in_bytes /1024 ) as [Size_in_KB], (size_in_bytes /1024/1024/1024 ) as [Size_in_GB]
FROM sys.dm_os_enumerate_filesystem(N’R:\SQLServerBackups\LDESQLDBUAT002\’, N’*.*’)
order by level, creation_time desc

/* Other methods to check contents of filesystems

SELECT *
FROM OPENROWSET(BULK ‘R:\SQLServerBackups\*’, FORMAT = ‘diff’) AS FileList;
go

EXEC master..xp_dirtree ‘R:\SQLServerBackups\eu-dave-sqldb-prd’, 1, 1
go

*/

/* To find free space on the Drives:
SELECT DISTINCT
dovs.volume_mount_point AS Drive,
CAST(dovs.total_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS TotalSize_GB,
CAST(dovs.available_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS FreeSpace_GB,
CAST(dovs.available_bytes * 100.0 / dovs.total_bytes AS DECIMAL(10, 2)) AS PercentFree
FROM
sys.master_files mf
CROSS APPLY
sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs;
*/

/* For free space on all drives
SELECT
fixed_drive_path AS [Drive],
drive_type_desc AS [Type],
CAST(free_space_in_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS [Free_GB]
FROM sys.dm_os_enumerate_fixed_drives
go
*/

Fragmentation checker

Posted on by 
/*
Check fragmentation of a database
*/

SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 30
ORDER BY DDIPS.avg_fragmentation_in_percent desc

Hallengren Script options

Posted on by

The Hallengren Maintenance scripts are an excellent collection of maintenance scripts, here are some options I often set for the IndexOptimize and Full Backup jobs

Backup

EXECUTE [DatabaseBackup]
@Databases = ‘USER_DATABASES’,
@Directory = N’D:\Backups’,
@BackupType = ‘FULL’,
@Verify = ‘Y’,
@CleanupTime = 28, –Hours beyond which it will remove the old backup file
@CleanupMode = ‘BEFORE_BACKUP’, — Delete the Backup file before or after the new backup
@Checksum = ‘Y’,
@LogToTable = ‘Y’,
@MinBackupSizeForMultipleFiles = 5120, –Size before it splits the backup into stripes
@NumberOfFiles = 4, –Number of stripes
@BufferCount = 12,
@MaxTransferSize = 4194304

IndexOptimize

EXECUTE [IndexOptimize]
@Databases = ‘USER_DATABASES’,
@TimeLimit = 14400, — 4 hours total run time
@LockTimeout = 300, — 5 minutes (amount of time it waits for a lock on any table)
@LockMessageSeverity = 10, –(The message severity raised if lock not obtained)
@LogToTable = ‘Y’

Keep Alive script – aaa-awake

Posted on by

A powershell script wrapped inside a bat file to keep a PC or server session active, place them in the same directory and run it by double clicking the bat file.

aaa-awake-ps.bat

@echo off
powershell -NoProfile -ExecutionPolicy Bypass -File "%~dp0aaa-awake-ps.ps1"

REM DEBUGGING SECTION - Just remove the REM at the start of the line
REM echo.
REM echo Script finished. Exit code: %ERRORLEVEL%
REM pause

aaa-awake-ps.ps1

Add-Type @"
using System;
using System.Runtime.InteropServices;
public static class IdleReset {
[DllImport("kernel32.dll")]
public static extern uint SetThreadExecutionState(uint esFlags);
}
"@

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$cycles = 57  # ~9.5 hours at 10 min intervals

try {
    for ($i = 1; $i -le $cycles; $i++) {

        # 1. System-level idle reset
        [IdleReset]::SetThreadExecutionState([uint32]2147483650) | Out-Null

        # 2. Tiny mouse movement
        $pos = [System.Windows.Forms.Cursor]::Position
        [System.Windows.Forms.Cursor]::Position = New-Object System.Drawing.Point ($pos.X + 1), $pos.Y
        Start-Sleep -Milliseconds 200
        [System.Windows.Forms.Cursor]::Position = $pos

        # 3. ScrollLock toggle
        [System.Windows.Forms.SendKeys]::SendWait("{SCROLLLOCK}")
        Start-Sleep -Milliseconds 200
        [System.Windows.Forms.SendKeys]::SendWait("{SCROLLLOCK}")

        # Status
        Write-Host "$(( $cycles - $i ) * 10) minutes left"

        Start-Sleep -Seconds 600
    }
}
finally {
    # Restore normal Windows power-management behaviour
    [IdleReset]::SetThreadExecutionState([uint32]2147483648) | Out-Null
}

MSSQL – Transfer logins and passwords between servers

Posted on by

Transfer logins and passwords to destination server (Server A) using scripts generated on source server (Server B)


  1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
  2. Open a new Query Editor window, and then run the following script.

USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint – (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT ‘No login(s) found.’
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

      SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
        — obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        — obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
      SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
      SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
      SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO



Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

  1. Run the following statement in the same or a new query window: 

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

 Steps on the destination server (Server B):

  1. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

    Important Before you go to step 2, review the information in the “Remarks” section below.
  2. Open a new Query Editor window, and then run the output script that’s generated in step 2 of the preceding procedure.

For more information refer to this webpage

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

DR_encryption_solution_for_failing_over_encrypted_table-column

Posted on by

USE [CreditDataWarehouse]
GO

/*
**
** Desc: Adds/Resets Master key password to prod database
**
** Auth : Olafsson
**
** Date: 6-May-2019

** Change History

** PR Date Author Description
** — ——– ——- ————————————
**
*/

IF OBJECT_ID(‘dbo.C0012433944’, ‘U’) IS NOT NULL
BEGIN
DROP TABLE dbo.C0012433944
PRINT ‘<<< Success : Dropped dbo.C0012433944 >>>’
END
GO

DECLARE @PASSWORD VARCHAR(128) = CONVERT(VARCHAR(128),CRYPT_GEN_RANDOM(128),1)

SELECT @PASSWORD AS secret, @@SERVERNAME AS servername INTO dbo.C0012433944

DECLARE @SQL VARCHAR(255) = ‘ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = ”’ + @PASSWORD + ””

Exec(@SQL)

PRINT ‘EXECUTED ALTER MASTER KEY ENCRYPTION’

GO

— Revoke all permissions on table C0012433944 to everyone except sysadmins

use [CreditDataWarehouse]
go

DENY delete ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY references ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY select ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO
DENY update ON OBJECT::dbo.C0012433944 TO cdw_Asm_User;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Dev;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CDW_Web;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY insert ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY references ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY select ON OBJECT::dbo.C0012433944 TO CMPROD;
GO
DENY update ON OBJECT::dbo.C0012433944 TO CMPROD;
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\cdw_dataloader-g];
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [DBG\philma];
GO

DENY delete ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY insert ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY references ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY select ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO
DENY update ON OBJECT::dbo.C0012433944 TO [dbg\svc_CDW_SSAS];
GO

— Create Switcher job on both SQL Instances
USE [msdb]
GO

/ Object: Job [CreditDataWarehouse DB Encryption switcher] Script Date: 06/05/2019 10:11:32 / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 06/05/2019 10:11:32 /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’CreditDataWarehouse DB Encryption switcher’,
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’A job to monitor the CreditDataWarehouse database for failover and if one is spotted to update the master key to re-enable decryption.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [Check for failover and if so refresh master key] Script Date: 06/05/2019 10:11:32 /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Check for failover and if so refresh master key’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’IF EXISTS(select * from sys.databases where name=”CreditDataWarehouse” and state_desc=”ONLINE”)
BEGIN
IF EXISTS(SELECT * FROM CreditDataWarehouse.dbo.C0012433944 WHERE [servername] != @@SERVERNAME)
BEGIN
DECLARE @PASSWORD VARCHAR(128)
SELECT @PASSWORD = secret FROM CreditDataWarehouse.dbo.C0012433944
DECLARE @SQL VARCHAR(1000) = ”use CreditDataWarehouse; OPEN MASTER KEY DECRYPTION BY PASSWORD = ””” + @PASSWORD + ”””; ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY ; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;CLOSE MASTER KEY; ”
DECLARE @SQL2 VARCHAR(1000) = ”update CreditDataWarehouse.dbo.C0012433944 set [servername] = @@servername;”
EXEC(@SQL)
EXEC(@SQL2)
PRINT ”OPEN MASTER KEY DECRYPTION”
END
END
GO’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Every 10 seconds’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20190430,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’c384ab01-0fa6-400c-a797-e80fbabf7c20′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO