Skip to content

Instantly share code, notes, and snippets.

Gavin Campbell gavincampbell

View GitHub Profile
@gavincampbell
gavincampbell / SingleRowTable.sql
Last active Dec 17, 2015
Pattern for creating tables that can only ever have one row.
View SingleRowTable.sql
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
gavincampbell / oops.sql
Last active Mar 6, 2016
Transact-SQL script to drop all user-defined objects accidentally created in the SQL Server master database
View oops.sql
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
gavincampbell / CreateDateAzureSQLDW.sql
Last active Nov 7, 2016 — forked from mlongoria/CreateDateAzureSQLDW.sql
Creates a calendar table in Azure SQL Data Warehouse
View CreateDateAzureSQLDW.sql
--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
gavincampbell / Install-Latest-Pester.ps1
Created Feb 28, 2017
Powershell script to download and extract the latest version of [Pester](https://github.com/pester/Pester)
View Install-Latest-Pester.ps1
$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
View Jenkinsfile
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
gavincampbell / test insert image
Last active Apr 21, 2017
Insert an image with tsql
View test insert image
IF NOT EXISTS (SELECT * FROM testIMAGE where ID = 2)
BEGIN
INSERT INTO testIMage VALUES
(2,
0x89504E470D0A1A0A0000000D49484452000001480000014808060000003214673300000006624B474400FF00FF00FFA0BDA793000000097048597300000048000000480046C96B3E000038034944415478DAED9D779C5D55B9B09F5DCE39FBF439D36B32E93D19121242092D14814B0B28E5A2A05CAEE2FDB8D8BB5851E4AAD7AE572F227A45400101E98890400209E999F4C964D233BD9CDEF6F7C749A4972453CEDEFB7D7E3FFF904C3267AFBDD673DEB5D6BBDEA59BA66922088220BC095D9A4010044104290882208214044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110410A822008224841100411A4200882085210044104290882208214044110410A822088200541104490822008224841100411A4200882085210044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110410A82208820054110449082200882085210044104290882208214044110410A822088200541104490822008224841100411A4200882085210044104290882208214044110449023409E54324F3C6792CE9B28AFF913435308E92AB85578DD9FD88B64264B2A9B239DCD9248675194B77956D304452168B
@gavincampbell
gavincampbell / Vagrantfile
Last active Jun 17, 2017
Vagrantfile for SQL Server on Linux with WideWorldImporters sample database and tSQLt framework included
View Vagrantfile
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
gavincampbell / DeleteSpecifiedResourceGroup.ps1
Created Sep 8, 2017
Azure Automation Runbook to parse a VSTS merge commit hook and delete the resource group corresponding to the source branch identified therein.
View DeleteSpecifiedResourceGroup.ps1
[CmdletBinding()]
param([object]$WebhookData)
if ($WebhookData){
$requestBody = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)
$refspec = $requestBody.resource.SourceRefName
$branchName = $requestBody.resource.SourceRefName.Split("/")[-1]
@gavincampbell
gavincampbell / Jenkinsfile
Last active Nov 16, 2017
Branching in the Jenkinsfile
View 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
gavincampbell / packer-iam-policy.json
Last active Oct 9, 2018
AWS policy for Packer including S3 access
View packer-iam-policy.json
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"ec2:AttachVolume",
"ec2:AuthorizeSecurityGroupIngress",
"ec2:DeregisterImage",
You can’t perform that action at this time.