LSTANCZYK’s gists

LSTANCZYK / cleanup.sql

Created April 14, 2020 14:48

SSIS Database Cleanup

	/*
	Script name: Purge SSIS Catalog log tables
	Author: Tim Mitchell (www.TimMitchell.net)
	Date: 12/19/2018

	Purpose: This script will remove most of the operational information from the SSIS catalog. The
	internal.operations and internal.executions tables, as well as their dependencies,
	will be purged of all data with an operation created_time value older than the number
	of days specified in the RETENTION_WINDOW setting of the SSIS catalog.

LSTANCZYK / ScriptMain.cs

Created January 7, 2020 23:41 — forked from ryanvgates/ScriptMain.cs

SSIS Load Dll without GAC

	public ScriptMain()
	{
	AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
	}

	public Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
	{
	Assembly assembly = null;
	try
	{

LSTANCZYK / gruvbox_dark.json

Created November 20, 2019 22:28 — forked from davialexandre/gruvbox_dark.json

Gruvbox Dark color scheme for the new Windows Terminal

	{
	"background" : "#282828",
	"black" : "#282828",
	"blue" : "#458588",
	"brightBlack" : "#928374",
	"brightBlue" : "#83A598",
	"brightCyan" : "#8EC07C",
	"brightGreen" : "#B8BB26",
	"brightPurple" : "#D3869B",
	"brightRed" : "#FB4934",

LSTANCZYK / TSQL-to-POCO

Created February 12, 2019 16:30 — forked from joey-qc/TSQL-to-POCO

A simple TSQL script to quickly generate c# POCO classes from SQL Server tables and views. You may tweak the output as needed. Not all datatypes are represented but this should save a bunch of boilerplate coding. USAGE: Run this query against the database of your choice. The script will loop through tables, views and their respective columns. Re…

	declare @tableName varchar(200)
	declare @columnName varchar(200)
	declare @nullable varchar(50)
	declare @datatype varchar(50)
	declare @maxlen int

	declare @sType varchar(50)
	declare @sProperty varchar(200)

	DECLARE table_cursor CURSOR FOR

LSTANCZYK / Aggregate_Concatenate.sql

Created November 6, 2018 23:39 — forked from FilipDeVos/Aggregate_Concatenate.sql

SQL Server Aggregate to concatenate strings

	-- This script deploys the dbo.Concatenate() aggregate function on SQL Server. This is compiled from the code Concatenate.cs below.

	CREATE ASSEMBLY [concat]
	FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300E3EE2A540000000000000000E00002210B010B00000C00000006000000000000AE2B0000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000602B00004B000000004000009003000000000000000000000000000000000000006000000C000000282A00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B40B000000200000000C000000020000000000000000000000000000200000602E7273726300000090030000004

LSTANCZYK / SQL_CLR_Functions-NETCLR40.sql

Created November 6, 2018 23:38 — forked from martijnburgers/SQL_CLR_Functions-NETCLR40.sql

SQL CLR Functions for converting bytes to UTF-7, UTF-8, UTF32 and Unicode. Build for SQL Server 2012 and 2014.

	GO
	PRINT N'Creating [SQL_CLR_Functions]...for SQL Server 2012 and 2014.';


	GO
	CREATE ASSEMBLY [SQL_CLR_Functions]
	AUTHORIZATION [dbo]
	FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030041212C550000000000000000E00002210B010B000008000000060000000000002E270000002000000040000000000010002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000D42600005700000000400000C802000000000000000000000000000000000000006000000C0000009C2500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000034070000002000000008000000020000000000000000000000000000200000602E72737263000000C8020000004000000004000000

LSTANCZYK / HtmlDecode.sql

Created October 25, 2018 20:49 — forked from azborgonovo/HtmlDecode.sql

HtmlDecode in SQL

	CREATE FUNCTION [dbo].[udf_HTMLDecode] (@text NVARCHAR(MAX))
	RETURNS NVARCHAR(MAX) AS
	BEGIN
	DECLARE @vcResult NVARCHAR(MAX)
	DECLARE @vcCrLf NVARCHAR(2)
	DECLARE @siPos SMALLINT
	DECLARE @vcEncoded NVARCHAR(7)
	DECLARE @siChar SMALLINT

	SET @vcCrLF = CHAR(13) + CHAR(10)

LSTANCZYK / CronTab-Expander.sql

Created October 25, 2018 20:02 — forked from manfredk/CronTab-Expander.sql

Expand CronTab expressions with SQL / T-SQL

	USE Exercises
	GO
	/* ***********************************************************************************************
	*
	* CRONTAB EXPANDER
	*
	* ***********************************************************************************************/


	/**************************************************************************************************

LSTANCZYK / SQLDateSnippets.sql

Created October 18, 2018 18:24 — forked from Dynyx/SQLDateSnippets.sql

Handy SQL date snippets

	---Today
	SELECT GETDATE() 'Today'

	----Yesterday
	SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

	----First Day of Current Week
	SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'

	----Last Day of Current Week

LSTANCZYK / Example.sql

Last active October 12, 2018 22:50

Humanize Date SQL Function


	SELECT dbo.humanizeDate(getdate()-1.56)
	SELECT dbo.humanizeDate(getdate()-1)
	SELECT dbo.humanizeDate(getdate()-.56)
	SELECT dbo.humanizeDate(getdate()+22.56)