Category Archives: SQL Server

Using SQL Spatial to find waypoints close to a route

I’m going away on another cycling adventure soon, riding north through France and Germany before a short stint in both The Netherlands and Belgium. No opportunity for a long trip this time so the ride is just over 500 miles … Continue reading

Posted in PowerShell, Scripting, Spatial, SQL Server | Leave a comment

Debugging Long-Running SQL Queries

The T-SQL PRINT statement lets you write to the Messages window in SQL Server Management Studio, which makes it a helpful aid when debugging SQL scripts and stored procedures. Unfortunately there is one major issue with using PRINT. The messages … Continue reading

Posted in SQL Server | 1 Comment

Database Corruption Challenge Part 1

Last weekend I saw a tweet mentioning Steve Stedman’s DataBase Corruption Challenge (DBCC) where he publishes a database that’s been corrupted and offers a challenge to the community to recover the database (hopefully) without any data loss. What a great … Continue reading

Posted in SQL Server | 1 Comment

Bike Tour Business Intelligence: Part 6 [Building the Fact Table]

At this stage the BI database contains the Date, Time and Location Dimension tables as well as a staging table with all the points captured by my GPS. This post covers creating and populating the Fact table, as well as … Continue reading

Posted in SQL Server, SSIS | 2 Comments

Bike Tour Business Intelligence: Part 5 [Dimensions]

Our star schema has three dimensions – Date, Time and Location. This post will cover populating the dimension tables in preparation for cube generation. Date Dimension There are lots of date dimension generation scripts online, Mike Davies’ script is good, … Continue reading

Posted in SQL Server, SSIS | Leave a comment

Bike Tour Business Intelligence: Part 4 [Populating a staging table]

A quick recap of the series so far: I’m building a cube using the data recorded by my Garmin Edge GPS during a 4 month bike tour last across Europe last year. So far we’ve analysed a single GPX file … Continue reading

Posted in SQL Server, SSIS | 1 Comment

Bike tour Business Intelligence: Part 3 [Reverse Geocoding]

In Part 2 I modified the SSIS data flow to add a column containing the position of each trackpoint read from the GPX file in the SQL Server spatial type geography . Part three investigates SQL spatial types further and … Continue reading

Posted in Spatial, SQL Server, SSIS | Tagged , , | 1 Comment

Bike Tour Business Intelligence: Part 2 [Geography]

My previous post covered using SSIS to read XML from a GPX file saving the data to a new table in a SQL Server Destination. This post will cover extending the SSIS workflow to create spatial data; the next on … Continue reading

Posted in Spatial, SQL Server, SSIS | Tagged , , , | 1 Comment