tcartwright

/* Use DUI pattern to mimic merge */
 
    DELETE aq
    FROM dbo.AppQuestionResponse aq
        LEFT JOIN @QuestionAnswers aqtt ON aq.appQuestionID = aqtt.QuestionID
    WHERE aq.policyID = @policyID
        AND aqtt.QuestionID IS NULL
 
    UPDATE aq
    SET aq.response = aqtt.answer, aq.comment = aqtt.Explanation

tcartwright

#Requires -RunAsAdministrator

$osInfo = Get-WmiObject -Class Win32_OperatingSystem
# $osInfo | Format-List *

if ($osInfo.ServicePackMajorVersion -gt 0) {
    $spInfo = " SP $($osInfo.ServicePackMajorVersion).$($osInfo.ServicePackMinorVersion))"
}

Write-Output "Running on $($osInfo.PSComputerName) ($($osInfo.Caption) Build $($osInfo.properties["BuildNumber"].Value)$spInfo)"

tcartwright

DECLARE @job_name sysname,
		@job_id UNIQUEIDENTIFIER;					

DECLARE inject_jobs_cursor CURSOR FAST_FORWARD
FOR (
	SELECT [j].[name], [j].[job_id] 
	FROM msdb.dbo.[sysjobs] AS [j]
	WHERE [j].[enabled] = 1
		AND NOT EXISTS (
			SELECT * FROM [msdb].[dbo].[sysjobsteps] AS [s2] WHERE [s2].[job_id] = [j].[job_id] AND [s2].[step_name] = 'CHECK AG'

tcartwright

USE [master]
GO

CREATE OR ALTER PROC dbo.sp_GetLongRunningJobHistory (
	@DaysPast INT = 1, 
	@HistoryStartDate DATETIME = NULL,		/* @HistoryStartDate - Start date for historical average */
	@HistoryEndDate DATETIME = NULL, 		/* @HistoryEndDate - End date for historical average */
	@MinHistExecutions INT = 1.0, 			/* @MinHistExecutions - Minimum number of job runs we want to consider */
	@MinAvgSecsDuration INT = 1.0  			/* @MinAvgSecsDuration - Threshold for minimum duration we care to monitor */
) AS 

tcartwright

USE [master]
GO

CREATE OR ALTER PROC dbo.sp_ConvertQuery2HTMLTable (@SQLQuery NVARCHAR(MAX))
AS
BEGIN
	/*
		Original Source: https://www.mssqltips.com/sqlservertip/5025/stored-procedure-to-generate-html-tables-for-sql-server-query-output/
		Alterations: 
			Tim Cartwright: 

tcartwright

/* Borrowed this pattern from Red-gates change scripts */

/****************************************************************/
-- TOP OF SCRIPT
/****************************************************************/
SET NOEXEC OFF
SET XACT_ABORT ON
BEGIN TRANSACTION
/****************************************************************/
-- TOP OF SCRIPT

tcartwright

# https://4bes.nl/2021/09/19/update-all-powershell-modules-on-a-system/

<#
TIM C: Changes:
    - Added scope parameter so scope could be controlled
    - altered code to always check for old versions, as this script may not have done the install, but it can still remove old versions
    - changed contains and othercomparison syntax to be case insensitive
    - altered logic around when the module is not found in the gallery to make the verbose output clearer
    - added version parses around the version compares so string comparisons do not screw up the comparison
    - added admin check when using AllUsers

tcartwright

Clear-Host

Get-VpnConnection | Where-Object { $_.ConnectionStatus -ieq "Connected" } | ForEach-Object { 
    Write-Host "Disconnecting $($_.Name)"
    . rasdial "$($_.Name)" /DISCONNECT  
}

Write-Host "Done"

tcartwright

function UpdateList {
    param($list, $key, [version]$version)

    if (-not $list.ContainsKey($key)) {
        $list.Add($key, $version)
    } elseif ($list[$key] -lt $version) {
        Write-Host "Updating package [$key] depenency version from $($list[$key]) to $version" -ForegroundColor Yellow
        $list[$key] = $version
    }

tcartwright

-- https://sqlperformance.com/2020/05/system-configuration/0-to-60-switching-to-indirect-checkpoints

SELECT d.name, [d].[target_recovery_time_in_seconds], CONCAT('ALTER DATABASE [', d.name, '] SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT') 
FROM sys.databases d
WHERE [d].[database_id] > 4 
	AND [d].[target_recovery_time_in_seconds] = 0