gavincampbell’s gists

gavincampbell / oops.sql

Last active March 6, 2016 16:03

Transact-SQL script to drop all user-defined objects accidentally created in the SQL Server master database

	DECLARE @dropStatements TABLE (dropStatement varchar(1000), dropOrder tinyint);
	DECLARE @dropStatement varchar(1000);
	INSERT INTO @dropStatements
	select
	CONCAT(
	'DROP',' ',
	typename,' ',
	OBJECT_SCHEMA_NAME(object_id),
	'.',
	OBJECT_NAME(object_id),';'),

gavincampbell / SingleRowTable.sql

Last active December 17, 2015 14:44

Pattern for creating tables that can only ever have one row.


	CREATE TABLE ConfigTable(
	[ConstrainToSingleRow] [BIT] NOT NULL DEFAULT ((1)),
	SomeValue [INT] NULL,
	A_String [VARCHAR](10) NULL,
	Flag1 [BIT] NULL DEFAULT ((0)),
	Flag2 [BIT] NULL DEFAULT ((0)),
	A_decimal [DECIMAL](9, 2) NULL,
	PRIMARY KEY CLUSTERED
	(

gavincampbell / CreateDateAzureSQLDW.sql

Last active November 7, 2016 23:02 — forked from mlongoria/CreateDateAzureSQLDW.sql

Creates a calendar table in Azure SQL Data Warehouse

	--Creates a table called Dim.Calendar. Change the table name on line 69. Change date range on line 2.
	DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

	-- prevent set or regional settings from interfering with
	-- interpretation of dates / literals

	CREATE TABLE #dimdate
	(
	[date] DATE,
	[day] tinyint,

gavincampbell / Install-Latest-Pester.ps1

Created February 28, 2017 11:45

Powershell script to download and extract the latest version of [Pester](https://github.com/pester/Pester)

	$latestRelease = Invoke-WebRequest https://github.com/pester/Pester/releases/latest -Headers @{"Accept"="application/json"}
	$json = $latestRelease.Content \| ConvertFrom-Json
	$latestVersion = $json.tag_name

	$url = "https://github.com/pester/Pester/archive/$latestVersion.zip"
	$download_path = "$env:USERPROFILE\Downloads\pester-master.zip"

	Invoke-WebRequest -Uri $url -OutFile $download_path

	Get-Item $download_path \| Unblock-File

gavincampbell / Jenkinsfile

Created April 4, 2017 23:54

	node {
	stage('git checkout'){
	git 'file:///C:/Projects/Chinook.JenkinsDemo'
	}
	stage('Build Dacpac from SQLProj'){
	bat "\"${tool name: 'Default', type: 'msbuild'}\" /p:Configuration=Release"
	stash includes: 'Chinook.JenkinsDemo\\bin\\Release\\Chinook.JenkinsDemo.dacpac', name: 'theDacpac'
	}
	stage('Deploy Dacpac to SQL Server')
	{

gavincampbell / test insert image

Last active April 21, 2017 10:16

Insert an image with tsql

+IF NOT EXISTS (SELECT * FROM testIMAGE where ID = 2)
+BEGIN
+INSERT INTO testIMage VALUES
+(2,
+x89504E470D0A1A0A0000000D49484452000001480000014808060000003214673300000006624B474400FF00FF00FFA0BDA793000000097048597300000048000000480046C96B3E000038034944415478DAED9D779C5D55B9B09F5DCE39FBF439D36B32E93D19121242092D14814B0B28E5A2A05CAEE2FDB8D8BB5851E4AAD7AE572F227A45400101E98890400209E999F4C964D233BD9CDEF6F7C749A4972453CEDEFB7D7E3FFF904C3267AFBDD673DEB5D6BBDEA59BA66922088220BC095D9A4010044104290882208214044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110410A822008224841100411A4200882085210044104290882208214044110410A822088200541104490822008224841100411A4200882085210044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110410A82208820054110449082200882085210044104290882208214044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110449023409E54324F3C6792CE9B28AFF913435308E92AB85578DD9FD88B64264B2A9B239DCD9248675194B77956D304452168B

gavincampbell / Jenkinsfile

Last active November 16, 2017 03:48

Branching in the Jenkinsfile

	node{

	checkout scm
	def the_one_to_run
	if (env.BRANCH_NAME == "master"){
	the_one_to_run = load 'master.groovy'
	}
	else{
	the_one_to_run = load 'not-master.groovy'
	}

gavincampbell / Vagrantfile

Last active February 10, 2022 03:19

Vagrantfile for SQL Server on Linux with WideWorldImporters sample database and tSQLt framework included

	Vagrant.configure("2") do \|config\|
	config.vm.box = "centos/7"
	config.vm.network "forwarded_port", guest:1433, host:1433

	config.vm.provider :libvirt do \|libvirt\|
	libvirt.memory = "4096"
	end

	config.vm.provision "shell",
	inline: <<-SHELL

gavincampbell / DeleteSpecifiedResourceGroup.ps1

Created September 8, 2017 00:54

Azure Automation Runbook to parse a VSTS merge commit hook and delete the resource group corresponding to the source branch identified therein.

	[CmdletBinding()]
	param([object]$WebhookData)


	if ($WebhookData){

	$requestBody = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)
	$refspec = $requestBody.resource.SourceRefName

	$branchName = $requestBody.resource.SourceRefName.Split("/")[-1]

gavincampbell / misc.ps1

Last active September 2, 2020 08:26

Powershell Aides-Mémoire

	#Creating a pscredential from a username:password pair in a single line:
	$Creds = New-Object System.Management.Automation.PSCredential ($User, $(ConvertTo-SecureString $Password -AsPlainText -Force))
	#Self signed wildcard certificate
	New-SelfSignedCertificate -Subject .my.domain -DnsName my.domain, .my.domain -CertStoreLocation Cert:\LocalMachine\My -NotAfter (Get-Date).AddYears(10)
	# trusting the PS gallery so we can install packages unattended, e.g. for DSC
	Install-PackageProvider NuGet -Force -Scope CurrentUser
	Import-PackageProvider NuGet -Force
	Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
	#generating Random String
	# source: https://devblogs.microsoft.com/scripting/generate-random-letters-with-powershell/

Gavin Campbell gavincampbell