Category: Nuggets

Posted on

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”

Posted on

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">
<ConfiguredValue>
<![CDATA["
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body>
<div style="background-color:#fff;padding:20px;">
<table cellpadding="0" cellspacing="0">
<tr>{Message}</tr>
<tr>{SQLResults}</tr>
</table>
</body>
</html>
"]]>
</ConfiguredValue>
</Configuration>

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

Posted 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”

Posted on

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”

Posted on

Disabling TDE on SQL Server 2008

Or more accurately: Preparing an encrypted TDE enabled database for restore on a Standard Edition SQL Server

I recently had the challenge of restoring an encrypted database on to a standard edition server to enable further development on the database code. It took some work, but to save you some time, I’ve listed the steps (and the T-SQL) in this article that you need to take in order to accomplish this.

Continue reading “Disabling TDE on SQL Server 2008”

Posted on

What question does this answer?

I was reading a post by BI Monkey and found myself in agreement with what he says. It also got me thinking about the wider implications of the problem of not “helping you make better decisions”.

I’d add to BI Monkey’s question with the specific question I always ask business analysts as they begin to list out the attributes they want added to a dimension in a requirements meeting. “What question does this answer?”. It’s a BI specialist’s responsibility not just to resolve the technical aspects of the requirements, but also to help steer the requirements so that something useful is delivered at the end.

I think this is one of the reasons why support from the business can fade. And without champion users in the wider business, it’s very likely that a BI implementation will be paralysed. People don’t understand the information provided and so don’t use it. Why keep funding the project if nobody uses it? Or the last (over-scoped) project took so long that the team isn’t trusted to take on new projects. Here are some scenarios…

Professional Microsoft SQL Server 2008 Reporting Services (Wrox Programmer to Programmer)”

Continue reading “What question does this answer?”

Posted on

SSIS – Writing to a package variable in a dataflow

Here’s the scenario… You have a RAW file which contains data from many files.

In the subsequent dataflow you need to perform a lookup against a large reference table, however you want just a subset that reflects the period contained within your RAW file.

Question 1, how do you find out the earliest date used within your RAW file data? And question 2, how do you write it to a variable so that you can use it in the subsequent data flow?

Programming Microsoft SQL Server 2008 (PRO-Developer)

Continue reading “SSIS – Writing to a package variable in a dataflow”