I Always Enjoy Reminiscing
And that header is a lie. I do enjoy thinking about stuff I used to have to do at work, because I don’t have to do it anymore. While we mostly gripe about the on-call stuff, the late night maintenance windows, etc., we don’t usually talk about the goofy stuff we’ve done. What follows is a non-comprehensive (and embarrassing code free) list of things I’ve done with SQL that I really should have used something else to do.
The problem is usually this: when you have to do something on the fly (and it’s expected sooner than later), you don’t have time to learn a whole new method. There were plenty of times when I looked at something and said “this would be perfect for PowerShell!” but then, three hours later, while standing atop a pile of smashed keyboards and monitors and disemboweled mice, I usually just gave up and renamed the damn files myself.
Without further ado, here’s some dumb stuff I’ve done!
I know, I know. The answer is to just send the email. But, project managers. That’s all. So there I went, writing a smorgasbord of ridiculous checks to be reasonably sure that every email we sent out was going to a valid address. The real fun here was trying to screen out people who put in obviously fake email addresses, like firstname.lastname@example.org, or email@example.com. This was one of the first times I got angry at the SQL Server development team for not having anything close to a decent RegEx implementation, but far from the last.
I don’t just mean put them in proper case. No, that would be fairly easy. This also involved guessing at first, middle, and last names, titles, and everything else, and putting them into the correct column. When you have no control over the source data, this is a nightmare. Oh, and some of them might be business names, so can you check that, too?
Get Text Between Dynamic Delimiters
Imagine you got a book, and someone wanted to get all the text that occurred between N patterns. Yeah. Keep thinking about that while you head to the liquor cabinet. Of course the field to search was NVARCHAR(MAX). Of course. Thanks.
Check An FTP Site For A File
“SSIS”, you scream. And rightly so. That would have been smart, if I had time to figure out how to open SSIS. Not only did I have to check for the file, but I had to move it, and then load and process it. It was simple in T-SQL, but it was also very stupid. I believe I used the FileZilla command line, but can’t recall exactly.
I was moderately thankful for 7-Zip’s CLI here. I still can’t talk about this without getting a twitch. We used to get these hundreds of zipped up text files, and I’d unzip them, copy them all to one big file, and then do some stuff with it. Since it was a weekly process, it got automated.
Download A File
This was a two step process. In one, I’d generate a cookie based on user information, and then I’d call cURL with that cookie to download the file. At least it wasn’t a zip file, but it still had to get moved and loaded into SQL.
Once Upon A Dream
There’s a reason I didn’t supply code examples here. I mean, aside from the fact that it’s owned by my past employers, and that would be theft or something. You should know it’s possible, but that it’s a bad idea. Learn from my mistakes. Many of these used xp_cmdshell to interact with the file system. A lot of people will scream about using it, but I’m not one of them.
Thanks for reading!
Brent says: <cough>I generated tables in HTML with T-SQL</cough>