Skip to content

Instantly share code, notes, and snippets.

View matt40k's full-sized avatar

Matt Smith matt40k

31 followers · 9 following
View GitHub Profile
@matt40k
matt40k / FindDecimalErrors.sql
Created March 26, 2019 11:46
select
concat('select top 10 [', c.name, '] from [', schema_name(t.schema_id), '].[', t.name, '] where [', c.name, '] like ''%.%'' and [', c.name, '] not like ''%.0%''')
from
sys.tables t
inner join sys.columns c on
t.object_id = c.object_id
inner join sys.types ty on
c.user_type_id = ty.user_type_id
and ty.name = 'decimal'
@matt40k
matt40k / GetTenantId.ps
Last active February 18, 2019 22:12
Get a Office365 Tenant ID
$domain = "microsoft.com"
$tenantId = (Invoke-WebRequest -UseBasicParsing https://login.windows.net/$domain/.well-known/openid-configuration|ConvertFrom-Json).token_endpoint.Split('/')[3]
Write-Host $tenantId
@matt40k
matt40k / ShortcutDeployment.ps1
Last active February 4, 2019 12:56
Created by DJ-1701 - Shortcut deployment script - The following is the latest powershell shortcut deployment script, first shown in http://www.edugeek.net/forums/windows-server-2000-2003/76664-cc3-vanilla-windows-without-wiping-4.html#post1404554
# Specify source and destination of shortcut folders below.
# ****************************************************************************
# * Note: In the source location, create two folders named 32-Bit and 64-Bit *
# * to host your Start Menu shortcuts (as shortcut paths may vary). *
# ****************************************************************************
$Source = "\\ABC-SVR-01\Applications$\StartMenu"
$Destination="C:\ProgramData\FolderName\Windows\Start Menu"
# Inform user if no Source or Destination has been provided.
If (($Source -eq "") -or ($Destination -eq ""))
@matt40k
matt40k / CloudFlareDNSLookupApi.ps1
Last active November 23, 2022 10:28
Uses DNS over HTTPS endpoint to lookup A records - PowerShell
$domain = 'cloudflare.com'
$url = 'https://cloudflare-dns.com/dns-query?name='+$domain+'&type=A';
$header = @{"accept"="application/dns-json"}
$response = (Invoke-WebRequest -Uri $url -Headers $header -UseBasicParsing).Content
$r = [System.Text.Encoding]::UTF8.GetString($response) | ConvertFrom-Json
$r
foreach ($item in $r.Answer)
{
Write-Host $item
@matt40k
matt40k / Report.html
Created January 22, 2019 21:48
Auto-refresh a Power BI report
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PowerBI</title>
<script>
var noOfMins = 1;
setInterval(function() {
window.location.reload();
}, (noOfMins * 60 * 1000));
@matt40k
matt40k / cte.sql
Created December 20, 2018 10:54
Creates a list of dates between two dates
declare
@dt_Start datetime = '2018-01-01'
,@dt_End datetime = '2018-12-31'
;WITH dts AS (
SELECT
sqldatetime = @dt_Start
UNION ALL
SELECT
DATEADD(DAY, 1, sqldatetime)
@matt40k
matt40k / UpdateExcel.ps1
Created November 20, 2018 10:36
Update the data in Excel files
$dir = "c:\temp\" + (Get-Date).ToString("yyyy-MM-dd")
Write-Host $dir
$excelObj = New-Object -ComObject Excel.Application
$excelObj.Visible = $true
$files = Get-ChildItem -Path $dir
foreach ($file in $files)
{
$filePath = $file.FullName
$workBook = $excelObj.Workbooks.Open($filePath)
@matt40k
matt40k / gist:0359b3d46487831311f62dc71e9f7905
Last active November 11, 2018 18:42 — forked from mbourgon/gist:6029690
SSIS_2012_Show_Connection_Strings
USE ssisdb
--thebakingdba.blogspot.com 2013/07/18 1.00
--purpose - grab the connection strings from SSISDB for the package and the job runs.
SELECT ISNULL(project_based_values.project_name,
job_based_values.project_name) AS project_name,
ISNULL(project_based_values.package_name,
job_based_values.package_name) AS package_name,
ISNULL(project_based_values.parameter_name,
job_based_values.parameter_name) AS parameter_name,
project_based_values.Connection_String AS Project_Connection_String,
@matt40k
matt40k / ms-excel.reg
Created October 31, 2018 11:18
Fix for opening Excel docs from URL \ website \ MS Teams
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\ms-excel]
@="Url:Excel Protocol"
"URL Protocol"=""
"UseOriginalUrlEncoding"=dword:00000001
[HKEY_CLASSES_ROOT\ms-excel\DefaultIcon]
@="C:\\Program Files (x86)\\Microsoft Office\\Root\\Office16\\EXCEL.EXE,0"
@matt40k
matt40k / ssrs_dayth.txt
Created October 5, 2018 15:32
SSRS expression to add th\rd\nd to day - so 1st, 2nd, 3rd, 4th etc
=str(Fields!DayNumberInCalendarMonth.Value)
+ SWITCH(
right(str(Fields!DayNumberInCalendarMonth.Value), 1) = "1", "st"
,left(str(Fields!DayNumberInCalendarMonth.Value), 1) = "1", "th"
,right(str(Fields!DayNumberInCalendarMonth.Value), 1) = "2", "nd"
,right(str(Fields!DayNumberInCalendarMonth.Value), 1) = "3", "rd"
,true, "th"
)