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

What's wrong with SQLPS?

Updated 2017-04-26: This appears to be fixed in SSMS 2017.01 and the PowerShell Gallery version of the SqlServer module.

Updated 2016-01-13: Added SQL Server 2016 comments and created a better, safer method of modifying the broken DLL.

I've seen a few reports of an error occurring when you use the SQL Server 2014 PowerShell provider to connect to an SQL Server 2012 instance. I have also encountered a few issues like this and did some investigation into the root cause.

Master Data Services error

I've been practicing with Master Data Services 2012 SP1 today by following along Profisee's free training course and came across two MDS bugs.

Scraping for iPhone Availability

When the iPhone 6 Plus came out availability was extremely limited, and you were meant to login to an Apple page around midnight every night to check for and reserve stock at your local store.

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.