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

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.

Walking through SQL Server cursor design decisions

The Brief

Stop me if this sounds familiar.

How to get Kerberos for SQL Server running, fast

This is the accumulated hands-on knowledge of days of work and hours of technical Kerberos videos that I didn't (and still do not) understand.

PowerShell script to fix the end tags in FireFox bookmarks exported as HTML

I exported my bookmarks in HTML format from FireFox and wanted to do some processing on them, but parsers like the HTML Agility Pack could not handle it properly because the format does not include DT (Url blocks) or DD (Description blocks) end tags, and that in turn results in massively nested XPath lists.

A script to convert the JavaScriptSerializer JSON format to a PowerShell format

Continuing on from earlier, after I discovered that FireFox includes far more bookmark information in its native format (JSON) I wanted to create a parser for that instead. Normally PowerShell provides you two simple ways of doing this: ConvertFrom-Json and ConvertTo-Json.

PowerShell and Get-ChildItem problems

Today I needed to modify a function to remove some bad files that were causing an import to fail across a long list of servers (using Invoke-Command). Here's a sample I've constructed to show and test the removal part.

PowerShell FTP

I've tried a few different options for FTP with PowerShell and the best one so far is WinSCP with its .NET assembly.

Comparing varbinary data types in SQL Server

This is something to be very, very wary of when storing data blobs in SQL Server. If you compare varbinary fields they will match when one field has one or more extra zero bytes at the end, even if they are different lengths. Observe:

How to construct a string in PowerShell with a certain number of spaces

This is easy.

Error handling in PowerShell (and the real world)

PowerShell error handling is great but becomes more complicated as your functions become more complicated and interdependent. For a while I used -ErrorAction Stop on everything because I was available to immediately deal with unforseen problems, but when functions call functions sometimes you don't really want them to just stop, you want to continue or possibly parse the output later.

SQL Server replication validation on vertically filtered articles

I use one-way transactional SQL Server replication to ship data out from a source server to multiple destinations, and recently added a publication with a vertically filtered article; that is to say a table where I exclude a column from replication because I do not want it sent to the subscribers.

SSIS stops in BIDS while debugging without showing any error

I had an error today where Business Intelligence Design Studio was running an SQL Server Integration Services package (I'm spelling it all out so search can find it) and failing with an error.

SQL CLR and "The context connection is already in use"

I encountered an error where an SQL CLR that had been in use for a long time was triggering this error from SQL Server:

Identity Columns and Not For Replication

I had replication errors recently with some subscriptions that were set up with replication support only (manually created and populated destination tables). These are transactional push subscriptions that are not updateable (i.e. they are one way only from the source to the destinations and the destinations must keep the same keys as the source).

SQL Server Transactional Replication Validation

After setting up transactional replication on a set of servers with @sync_type = "replication support only" I was receiving errors on validation where the row count was correct but the checksum was not. Meanwhile SQL Data Compare was telling me the tables on each server were exactly the same. This was caused by two issues.

SQL Server's SQLServer PSProvider and WMI

No solutions today, only sadness and finger pointing.

PowerShell "System error 85 has occurred" with net use

Today I received this message while running a PowerShell script on a remote server:

SSIS error 0xC000F427 and derived columns

I had a nasty message pop up in the middle of running an SSIS package with DTEXEC. That same package had been working fine for a long time, and was running in BIDS as recently as yesterday on the same machine with no problem.