Skip to content

Instantly share code, notes, and snippets.

View billinkc's full-sized avatar
💭
Presenting at #SQLSatOC

Bill Fellows billinkc

💭
Presenting at #SQLSatOC
  • Sterling Data Consulting
  • Kansas City, MO
View GitHub Profile
@billinkc
billinkc / JsonPackUnpack.sql
Last active January 13, 2017 19:07
Testing JSON with SQL Server 2016 and I'm confused on the seemingly extraneous square bracket around the child table
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Sales') IS NULL
BEGIN
CREATE TABLE #Sales
(
RecordKey int NOT NULL
, SalesDate date NOT NULL
, CONSTRAINT PK_tmp_Sales PRIMARY KEY (RecordKey)
)
INSERT INTO
@billinkc
billinkc / DoubleMetaphone.sql
Created August 24, 2016 14:01
Tired of looking through my script library for a double metaphone implmentation
-----------------------------------------------------------------------------
IF EXISTS
(
SELECT
*
FROM
dbo.sysobjects
WHERE
id = OBJECT_ID('dbo.DoubleMetaPhone')
AND OBJECTPROPERTY(id, 'IsDeterministic') IN (1, 0)
@billinkc
billinkc / ExportTables.biml
Created August 2, 2016 22:22
Biml Export table definitions. This script uses the extension methods GetDatabaseSchema and GetDropAndCreateDdl to reverse engineer a database model into memory and then emit sql files with the proper drop and create table scripts. For more info, see http://www.cathrinewilhelmsen.net/2015/07/12/biml-extension-methods-getdatabaseschema/
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template tier="1" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#
var schema = new List<string>{"CMS"};
var ODSCM = RootNode.OleDbConnections["ODS"];
var ODSDB = ODSCM.GetDatabaseSchema(schema, null, ImportOptions.None);
string fileNameTemplate = @"C:\Users\fellowsb\Documents\ODSDB\{0}_{1}.sql";
string currentFileName = string.Empty;
@billinkc
billinkc / twitter_735908622677475328.sql
Created May 26, 2016 19:29
Trigger rewrite based on https://twitter.com/vongillern/status/735908622677475328 Approximately correct, actual correctness left to the implementer
-- Really, what you want is probably something like
INSERT INTO
MeterAudit
(
MeterAuditId, MeterId, PreviousAuditId /*...*/
)
-- Find the last/most recent entry in MeterAudit for all the things we just changed
SELECT
NEWID() AS MeterAuditId, I.MeterId, MA.PreviousAuditId /*...*/
FROM
@billinkc
billinkc / Snippet.csproj
Created April 19, 2016 04:44
If it would build, this biml would generate an SSIS package with a Script task to play the intro bars to Star Wars.
<!--
This is the relevant bit of the include that VS makes for the dll
-->
<ItemGroup>
<COMReference Include="WMPLib">
<Guid>{6BF52A50-394A-11D3-B153-00C04F79FAA6}</Guid>
<VersionMajor>1</VersionMajor>
<VersionMinor>0</VersionMinor>
<Lcid>0</Lcid>
@billinkc
billinkc / so_36461498.biml
Last active May 27, 2016 20:24
Minimal reproduction of GetDropAndCreateDdl
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var sourceConnection = SchemaManager.CreateConnectionNode("tempdb", @"Provider=SQLNCLI11;Server=localhost\dev2014;Initial Catalog=tempdb;Integrated Security=SSPI;");
var importResult = sourceConnection.ImportDB("dbo", "so%", ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);
foreach (var table in importResult.TableNodes)
{
string fileName = string.Format(@"C:\ssisdata\so\{0}_{1}.sql", table.Name, table.Schema.Name);
@billinkc
billinkc / EventLogParser.ps1
Last active October 12, 2015 20:14
Based on https://www.simple-talk.com/blogs/2011/08/31/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell/ Running into issues using SchemaNo.sql despite the article's claim "because the Write-DataTable cmdlet uses sqlbulkcopy, you need the pass the columns to it in the same order as they occur in the table". Error is Write…
#######################
function Get-Type
{
param($type)
$types = @(
'System.Boolean',
'System.Byte[]',
'System.Byte',
'System.Char',
@billinkc
billinkc / dbo.MyPatchLevel.sql
Last active November 17, 2016 19:18
Possibly handy views for identifying SQL Server patches and then what is my patch level - how long since patched, etc. Covers 2005 to current
CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
SELECT
SRC.Build
, SRC.[Release Date]
, SRC.SimpleVersion
, SRC.[KB / Description]
FROM
@billinkc
billinkc / Program.cs
Created August 15, 2015 16:01
Rough cut of where I got in passing project parameters in to an SSIS package that uses the project deployment model but isn't stored in the SSISDB
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Dts.Runtime;
namespace ParameterPasser
{
/// <summary>
![Connection Manager General tab ][1]
![Connection Manager preview][2]
![Data flow][3]
[1]: http://i.stack.imgur.com/aXZqb.png
[2]: http://i.stack.imgur.com/6i7iY.png