Fixing TCX files with the Garmin Trackpoint Time bug

Something that’s bugged me and perhaps some of the Garmin 1000 owners in the Strava community for a while is the failure of certain activities to sync.

As an avid triathlete, data analyst and technologist, one side of the sport I love is there is loads of data!

Frequently though, I return from a ride, upload the activity only to find that it won’t transfer to Strava. This is due to the trackpoint times for some inexplicable reason switching to July 4th 2019 half way through the file.

I’d manually corrected the file a few times, but eventually I’d had enough and put together a PowerShell script to do the work for me.

Continue reading “Fixing TCX files with the Garmin Trackpoint Time bug”

Simple PS script to copy files from one share to another

XCopy is great but sometimes you need something a little more powerful. Powershell can give you the ability to perform more complex manipulations.

Here we’re finding the previous weekday and using it to find, copy and rename a set of files into an automated regression testing environment.

Continue reading “Simple PS script to copy files from one share to another”

Include HTML in a SSIS Configuration File

This stumped me for a while, but a useful pointer from a colleague reminded me how this can be achieved…

Quite simply as the configuration file is XML we can use CDATA so that the content won’t be parsed. Here’s an example below:

<!-- Package replaces {SQLResults} with an HTML table showing Count by User for the last loaded date -->
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::EmailBody].Properties[Value]" ValueType="String">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<div style="background-color:#fff;padding:20px;">
<table cellpadding="0" cellspacing="0">

Using the CDATA attribute inside the XML nodes will allow you to add HTML or any other XML tags inside your configuration file.

What port number does my SQL Server listen on?

Looking for an easy way to know what port number your SQL Server is listening on? You can of course go to the Configuration Manager or check through the registry… but an even simpler way is to query the view SYS.DM_EXEC_CONNECTIONS which will tell you provided you’re connected over TCP/IP

Continue reading “What port number does my SQL Server listen on?”

The Developer DBA toolkit

Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:

  1. It saves a lot of time because it means that you don’t always have to remote on to the server to view debugging information or execute local commands. e.g. Is XYZ service running? Who’s logged on to the box? Run that SSIS package.
  2. You can view results from a different security context which is often the key for successfully debugging connectivity issues.

I always keep a script just for the purpose which I consider to be an essential part of any database developer/DBA toolkit. This is a selection of tasks that crop up again and again and will often save you a lot of time. Especially if you use xp_cmdshell.

This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!
Continue reading “The Developer DBA toolkit”

A simple restore script

This is a simple script that finds all (backup) files in a directory that match a pattern specified. It then derives the database name from the filepath and attempts to do a full restore. It should be executed in SQLCMD mode.

This is a script I created purely for a continuous integration setup on my local machine. Continue reading “A simple restore script”

Isn’t it ironic

It’s quite ironic that being an ETL and database developer by trade, it has actually been easier to upgrade this website manually, literally copying and pasting each of the blog entries.

SSIS which is the integration tool I have on my current machine, has no easy way to insert data into a MySQL database. It can be done, but it means setting up a foreach container and mapping recordset parameters to an Execute SQL task. Far too much effort for a one off upgrade.

ASP.Net – Sending Email Asynchronously

I’ve spent a lot of time over the last few months building a website, and as always, there is much help and information available on the internet. Something I struggled with however was the sending of emails… How can I send an email from my site without blocking the calling process? “Use sendAsync!” I hear people shouting. Having tried this I can say that it doesn’t help where is concerned. I implemented it myself, and whilst it is almost seamless on a web server with fast connectivity to the smtp box, what I found is there are occasions where this just isn’t fast enough, and the user experience suffers. Here’s what I have done to resolve this… I hope it saves someone the head scratching that I went through 😉

Continue reading “ASP.Net – Sending Email Asynchronously”

Get the query and execution plan for a session

A simple piece of code to help you get the execution plan and the query text for a currently running query. This is especially useful when dynamic sql is being run against your database or if DBCC InputBuffer reports that a stored procedure is being run. You can actually view the execution plan for the batch that it’s running. It works using the 2005 management views and functions.

Continue reading “Get the query and execution plan for a session”