Skip to content

Instantly share code, notes, and snippets.

Gavin Campbell gavincampbell

Block or report user

Report or block gavincampbell

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
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.