It’s that time of the month again and this invitation from Reitse has asked us about some fun we’ve had with T-SQL in the past.
Whilst I’ve used SQL Server over the years and it’s features as part of various solutions for others, I enjoy the time I can put it to work for solving my own challenges at work.
To cut a long story short we had a 3rd party warehouse solution which had an issue of skipping order tracking intermittently. With no sign of being resolved our team kept receiving call outs to investigate this issue.
I don’t like to be called out. I like my sleep.
We already had evidence of the issue so I started out marrying those with records in the database. Once I had a query that seemed to do the job it was wrapped into a proc to send an email alert when a possible occurrence was found and scheduled through the day.
After a few days of correlating these against call outs across the team we had alignment and even managed to proactively spot a couple of instances which had slipped through. First hurdle cleared.
With confidence in that and a better understanding of the data the next step was looking for a fix. With some trial, error, and a hefty dose of testing, it turned out that we could inject evidence of these missing statuses into the relevant tables and the tracking would spring into life. We extended the existing identification to include this fix where required.
Cautiously the fix was enabled through the day and in the evening it was flipped back to alerting rather than making changes. Again it was ran over a period to get confidence in the changes. I’d also say that we created the records in a way that they could be identified as automated in case there was a need to remove them.
The fix was stable and after extended testing it was eventually enabled around the clock. We still continued to receive the email notifications but they were more for reference of what had been fixed.
Whilst it was still reviewed, that was the last time any of us were called out for that issue thankfully. Yes it wasn’t our issue to deal with but that’s another story. Maybe the supplier fixed it eventually, maybe.
The fix itself wasn’t anything miraculous or anything stand out, it was the process of working through the issue, understanding a new set of data and developing the different sections of the solution which provided the ‘fun’ throughout.
A change of pace or focus every now and again keeps us fresh. SQL Server is versatile and being able to utilise the various facets of it’s tooling in new ways helps us better understand it’s capabilities and shortcomings. Ultimately this helps us better prepare for situations we come across in the future and provides options for how we could approach them.
I look forward to reading about the fun across the community in their responses.
Andy Brownsword 