Cody Konior Senior SQL Server Database Administrator Spider photographer, stand-up comedian, astronomer, and occasional beekeeper.

A common mistake when splitting strings

This is a mistake I make from time to time to my own detriment when making ad-hoc changes. Take a large string usually cut and pasted from somewhere that you want to break into an array of lines:

PowerShell with a focus on automation

Thanks to everyone that came to see my presentation at the Perth SQL Server User Group! Here was the recorded video with annotations and notes for all the miscellaneous errors that occurred during the demos.

RBAR and UDFs

I was reading this great article on Hidden RBAR: Triangular Joins from 2007 and a similar one on CTEs (who would have thought something everyone does is such bad practice?!)

Microsoft Exam 70-465 (2014) Study Guide

The official exam description for Designing Database Solutions for Microsoft SQL Server 2012 was pretty thorough but has an additional annotated list of changes covering the Server 2014 additions made in April 2014.

Microsoft Exam 70-464 (2014) Study Guide

This is a full list from the Microsoft website integrated with their published April 2014 update.

Testing some NOLOCK and READPAST

I'm doing some study on locking and read this blog post about NOLOCK and READPAST in SQL Server 2005. The test was straightforward:

Children, Hot Dogs, and Body Bags

I really messed up the opening and the ending but the middle was a pretty solid effort. I hadn't been up for 3 years and really enjoyed giving it another try. The other comedians backstage were really cool and my spot was about middle of the pack for the 16 acts that went up that night.

MSDAINITIALIZE minimum required permissions

I was setting up a new SQL Server 2014 (x64) instance and wanted to read some spreadsheets using SQL. This is fairly easy to do:

Upgrading SQL 2005 to SQL 2014

I had an interesting discussion recently about whether this was still supported or not; it definitely was in SQL 2012 but in SQL 2014 it wasn't so clear because there are two sources of Microsoft documentation that are in conflict.

Iterating years in PowerShell

I was running a data extraction process which accepted an ISO format start date and end date but because of the time span involved it would timeout before completion.

Monty Hall simulation in PowerShell

Imagine this probability puzzle:

Check the error log for failed operations immediately before this error message

Have you seen this error in the SQL Server error log before?

Will alter procedure result in an execution failure?

It is obvious if you have scripts that are like IF EXISTS: DROP: CREATE PROCEDURE, and run this on a production system that is calling those procedures at the same time, that some will fail when the hit that period between the DROP and the CREATE.

Caveat of sp_rename

I was using sp_rename lately and came across this article that goes into the pitfalls.

Trigger deadlock testing with PowerShell

Call me crazy but triggers are usually bad news. In my experience they tend to rise to the top of the queue for bug fixing more frequently than their relatively small SLOC would belie, and often have performance problems that don't become apparent until months or years down the track as record counts increase.

Testing logins and passwords from a stored procedure

I recently wanted to know how given a table of user names (logins) and passwords how you could verify that list can connect to the local server from within a stored procedure. This is what I came up with, first the setup.

Taking down production using nested transactions

Most DBAs will have processes they need to run over a series of databases, and do so either with sys.sp_msforeachdb (which is bad, bad, bad because it can skip databases) or their own stored procedure which uses a static cursor over sys.databases.

How to add Brent Ozar or GoToWebinar calendar entries on a Mac

TL;DR version: If you encounter problems with GoToWebinar calendar entries on a Mac remove the ORGANIZER line from the file and try again. The full explanation follows.

Load Balancing SQL Server Backups

Introduction

I recently saw an issue where a large number of databases were consolidated onto a single server. Though they were backed up on a proper full -> differential -> log cycle, and the starting day for each cycle was roughly spread evenly over the week, the large variance of characteristics between each database resulted in backups running far too long on some days and terminating too early on others.

Checking for null in PowerShell

Updated 29 Sep 2016: Fixed a bug in IsNull where Bruno Martins pointed out it returned True for 0. This said… this post is super old… and should be ignored.