I was forced recently to do some maintenance and bug fixing on an aging .NET-based CMS. Most of the problems were in the SQL Server database, with lots of corrupted entries. The most frustrating thing however was that at times I didn’t even know where to find the entries. Try finding some specific strings in a database with a hundred tables, each with many columns (poorly named, obviously) and tens of thousands of records — it’s like finding the proverbial needle in the haystack.
Every now and then I come across some bad practices that needs repairing. This time the “no-no” was in a database tables. I had to change hard-coded links in quite a few tables. Replacing 10,000 absolute URLs spread across the database can be a huge headache, so I decided to work smart, not hard.
MS SQL has the ability to retrieve data as XML by using the “FOR XML [RAW | AUTO [ELEMENTS] | EXPLICIT]” parameters, which is very useful especially when used in a web service that communicates with a Flash / Flex client as the results don’t have to be converted, but just output to the client.
Here’s a neat thing to remember if you’re using MSSQL:
I started to work on a pet project for tracking deleted/pending domains and after starting development I was facing a big problem: How to do the data validation directly from MySQL ?
This is not really a post, just a small note on an item that can make you lose precious time hunting for…