Error converting data type DBTYPE_DBTIMESTAMP to datetime2

Okay, so here is a post that has been months in the making.

Lately I’ve been working on an ETL project, mostly the E and T parts. The project involves exporting data from a database via an ODBC driver (AcuODBC to be exact). I don’t have any control over the database being accessed other than being able to extract data and neither the client nor I have much knowledge of how the database actually works. For my part I just know that I can access it via the AcuODBC driver.

I am using .NET and C# to do the data extraction and translating, but I’ve run into an issue on several tables that has perplexed me for some time. It is the error message that I’ve used as the title of this post:

Error converting data type DBTYPE_DBTIMESTAMP to datetime2.

No matter how I was trying to get at the data, I kept getting this error. I tried CAST and CONVERT SQL commands. I tried BCP. I tried using SELECT INTO. I tried SqlBulkCopy. I finally got all the way down to using an OdbcDataReader object to try to move the data, but I kept getting the error when I used reader[index].ToString() to access the data.

But then I noticed there was a method called GetString in the OdbcDataReader object. I tried it and by golly it worked! (If you have been wondering what the cause of this error has been all along, it is an invalid date that was entered into the database with the year 0000). Yes, it worked, but it worked sloooooooow. Veeeeery slow. It was processing about 2 records per second, which isn’t too bad until you scale that up to over 1.1 million records. The time to extract just that one table would be over 6 days! That would not do.

I sat on it for a few days and thought about all the potential solutions. I could bulk copy all the rows except the ones in question, but then I’d have to go through each table and find the culprit(s) and write special select statements to exclude those rows, then special select statements to grab those rows minus the offending fields. Then I’d have to update my table with the omitted records. Not something I really wanted to do.

Then today I noticed that when I used the reader[index].ToString() method to get the data, it came out at a much faster, more acceptable speed. Okay, so what if I wrap that in a try block and then in the catch block I use the slower GetString method? If that worked then only the offending data would get extracted using the slow GetString method and everything else would use the other method. And what do you know, it DID work! Here’s the code for posterity (yeah, I’m stripping out all the new lines and spaces).


Setting up Apache Virtual Hosts on FreeBSD 10.1

I’ve been messing around on DigitalOcean with a FreeBSD 10.1 droplet. First let me say that FreeBSD is awesome. The thing that I like the most about the BSDs is that they don’t beat about the bush when it comes to making an OS because the purpose of each BSD is the OS. My main comparison is to Linux based OSes, which are awesome in their own way, but feel a bit splintered. With the BSDs it’s not just a kernel with a bunch of GNU apps thrown at it to make it usable, they take great pains to produce a complete OS. So when I need to know how to do something on FreeBSD, there’s probably already a darn good document out there with detailed instructions. Hell, you rarely have to go outside the FreeBSD manual unless you’re doing something really complex.  It just works and that’s great!

Now on to why I’m writing this post. I was wanting to set up Apache on my FreeBSD 10.1 machine to serve up 3 different websites. In the Apache world this is called Virtual Hosts. So let us say that we have 3 different addresses, but only one IP, now what? Virtual Hosts, and specifically Named Virtual Hosts. Configuration is dead simple.

First, locate your httpd.conf file. The Apache configuration file has a lot of names of various different Linuxes, but on FreeBSD it’s just httpd.conf and it is located at /usr/local/etc/apache24/httpd.conf. Open that file and go all the way to the bottom.

Second you will need to add in VirtualHost entries for each of your sites. You’ll also need to include one for the default IP address of the machine, otherwise it will point to the first VirtualHost entry.

<VirtualHost *:80>
DocumentRoot /usr/local/www/apache24/data
<VirtualHost *:80>
DocumentRoot /usr/local/www/apache24/data/
<VirtualHost *:80>
DocumentRoot /usr/local/www/apache24/data/

The first line of each listing tells Apache that this is a VirtualHost entry and that it should listen to any incoming requests (the star) on port 80. The ServerName line tells Apache the name to look for for that particular entry. If a request comes in for, then the second entry above will be used. The first entry will be used if someone just types in the IP address of the server (assuming that is the IP). The DocumentRoot line tells Apache where the files for that site are located. I tend to create a new folder for each site in the main document root folder so that all my sites are in a common place. The last line closes the listing.

Once you’ve edited your file, simply write it out and restart the Apache service to pick up the new settings.

sudo service apache24 restart

And it’s that simple! For more detailed documentation on Virtual Hosts you should read the official Apache documentation. Have fun with it!

The purpose of Twitter Bootstrap

Okay, so I rarely make websites or blog anymore. I’ll admit that I’ve fallen a bit out of touch with regard to the latest and greatest web technologies. But I’ve surprised even myself in the amount of time it has taken for me to understand Bootstrap.

In case you didn’t know, Bootstrap is a web framework. No, I wouldn’t go that far. It’s more like a web layout framework. Bootstrap helps take your dowdy, old site and make it look like something a professional web designer might throw together in an afternoon (this is a compliment to web designers from someone who couldn’t design their way out of a wet paper bag).

So the thing that has really eluded me when it comes to Bootstrap is the power. What power is this? What power could there possibly be in a glorified style sheet?

The power comes not from what Bootstrap does, it comes from what I don’t have to do (and this is the part that took me so long to figure out). I don’t have to write style sheets anymore. That’s it.  But whoa, THAT’S IT! I used to spend HOURS (days even) tweaking this and nudging that, opening the Gimp and doing a little on the fly editing, reading A List Apart and Stack Overflow.

Now when I build a site I vow to do as little of that crap as possible. Now I’m going to use Bootstrap for what it was intended: to leave the style to designers and let the blogger/developer blog and develop.

Why I’m no longer a Google fanboy

I used to be a Google fanboy (my wife is still a Google fangirl). I used to sing their praises and recommend their software and services to anyone who asked for the slightest computer advice. But lately I’ve been rethinking my stance on Google and have started moving in other directions.

So what did Google do that has put me off? They started sucking at what they used to do best. We’ve known all along that Google’s products are NOT it’s services, but it’s users. Their massive user base and associated user data is where they make the big bucks. They amassed this data by tricking us users into giving it to them and the profit margins are astonishing because we give them all of that information without being compensated one red cent. It is not this fact that put me off. Quite the contrary, it is one hell of a business model. The thing that has put me off is that they aren’t able to trick me into giving them my information for free anymore.

What do I mean by this? The way they tricked me in the past was to give me some pretty great software and services. The problem now is that I can’t remember the last time they tricked me into using a new product. They have tons a new stuff coming out, but I’m not using it. Google+ is a flop and if the rumors are true they may finally be axing it. They killed the much loved Google Reader without even a reach around and that in turn has greatly reduced my blog readership (and writership for that matter). And the changes they are making in Android, Chrome, Apps, YouTube, and other services are more often than not making those services worse rather than better.

One thing I’ve noticed is that Google has become much more invasive and forceful in their efforts. In the past they were nice enough to allow us to customize their services and software and keep things the way we wanted them to be and allowed you to share content across multiple platforms. Now they are moving more towards the Apple approach where they are locking you into a certain ecosystem and locking you into the feature set they want you to have. They are changing things for the sake of their ecosystem and not for the reasons of customer feedback or improved feature sets.

My main issue lately is a growing distrust in their reliability. It really started with the aforementioned killing off of Reader. They had killed other services before Reader, but Reader was the first one that really affected me. I used to have a set of about 25 blogs that I read on a regular basis, but when Reader went, I couldn’t find a good replacement that worked like I wanted it to work. So now I no longer read blogs. The outcry was great from the community of Reader users, but those cries were left unheard. Now I hear that cry in the back of my mind every time I use a Google service. I think, “When will Google become tired of this service and remove it?”

So I’ve decided to try to become much less reliant on Google services. I’ve already given up on Chrome and replaced it with Firefox. At some point I would like to replace my Android phone with a Windows phone, but that’s probably a few years down the road (if Windows phone is still there). I now use Bing for most of my searching. The main services I use beyond that are Google Play for my music collection, Google Docs, Google Calendar, and Gmail. I’m not sure where I could put my music collection that is accessible as it is on Google Play, but there are multiple alternatives to Docs, Calendar, and Gmail and I am already exploring those. If you have any suggestions on replacements, feel free to contact me.

I don’t want this blog to become Google bashing central and my intent is not to bash Google. As a company Google is no better or worse than any other large corporation. I’m simply stating what my issues with Google are and why I am personally trying to move in another direction. The simple fact is they just aren’t capturing my enthusiasm anymore.

Web’s Dead, Baby: Part 2

Well, now that I’ve made you wait a couple of weeks to tell you why the Web is dead, I guess it’s time to disappoint (nothing can live up to that amount of hype). As my one piece of evidence I present this link. Go ahead, look at it. I’ll wait.

So, what did you think? If you were thinking, “What the hell is Emacs?” you missed the point. I wanted you to look at the design of the page. In fact, you can check out this link to see the time and effort that went into that design. I’ll wait.. again.

Okay, you done? Good. What did you think? Don’t answer, that was rhetorical (and people sitting near you will think you’re crazy for talking at the computer). I’ll tell you what I think. It’s glorious. It’s a fine piece of web design. That hand drawn look is very cool and the overall design is simple and easy to read. The fonts are great and I really admire the thought, time, and artistry that went into creating it. Now look at this image (click or touch for a larger version):


This is how that website looks on a mobile browser. Specifically, that’s how it looks on Chrome for Android on my Galaxy S3. So that great design, gone. The artistry, gone. Everything that made that sight design unique and interesting, gone.

“But Joey, the content is still there and surely that is what’s most important.” Yes, I know. Bear with me. It’s not just the mobile browsers, it’s the news aggregators that have been chipping away as well. Google Reader (now defunct), Feedly, whatever you want to use, they all strip the style of a site and just serve up the content. I’m old school and even though I’ve used Reader, even I have to admit that that it stripped away the context, the feel that the author intended to convey when they designed their site. Also, it makes it much easier to just go to your news aggregator and have things served up to you that you’ve previously subscribed to. Pretty soon, that’s all you’re looking at and the wider web is not within your walls.

Speaking of walls, here comes the real web killer. The thing that is making the web the long lost child that everyone has forgotten. Apps. APPS!!!! Apps are creating walled gardens of content. Instagram instantly comes to mind as an example. It’s pretty much a mobile only experience (at first an iPhone only experience). Sure they’ve added some web content in the past few months, but it’s just an afterthought, the only real way to see and interact fully is through an app.

Apps are putting up walls where the web had previously broke them down. Before the web, AOL, Prodigy, CompuServe, etc. all served up their own private content, and if you didn’t subscribe, you didn’t even have access to it. Apps are doing the same thing. To view and create content through certain services, you have to have their app. And some apps are iPhone only, or Android only, or only available on certain phone carriers. This isn’t progression, it’s regression. We are literally traveling back in time to the land of AOL and the private BBS.

Sure, the web is still around, and it will be for years and years to come, but it’s dead. Like magma that has cooled and solidified, the web sits and awaits its fate. And the freedom and the openness of the web? They’re slipping away too. The time when all you needed was a browser window to look into the vastness of all human intellect is fading into history. So when your grandkids ask you what the web was, you’ll just tell them, “The Web’s dead, baby. The Web’s dead.

Web’s Dead, Baby: Part 1

Twenty two years is a good run for most technology these days. In fact it’s a phenomenal run. That’s how long the World Wide Web has been in existence. But I’m here today to tell you that the web as we know it is DEAD! (The irony of making this statement in a weblog post does not escape me.) What killed the web? How did it die? Is this the end of all things? Come close and I’ll tell you the story of the end of the web, but first, a little history.

The web has become synonymous with the Internet. When people today talk about the Internet, they are talking about the World Wide Web (henceforth, I’ll just call it the web). But the Internet is actually more than just the web. It is a large network of computers that use a number of protocols and services to exchange information. Email is the prime example often given of a non-web Internet service, although it can now be accessed through the web. The Internet is more the connections between the computers than it is the content. Here are a list of some Internet services, both living and dead, that you might know.

  • Usenet – This is the original Internet discussion forum. Thousands of Usenet groups were created for topics ranging from computing to German fetish porn. In fact the web was first announced on a group called alt.hypertext.
  • Gopher – This is a (mostly) dead Internet service that was very similar to the early web. It was text based like the early web, but instead of hyperlinks, it used numbered menus for navigation. (I am old enough to remember and have used Gopher. I actually preferred it to the web in my early Internet dealings.)
  • Internet Relay Chat (IRC) – Before Facetime, Hangouts, Skype, or even AIM and Yahoo Messenger, IRC was where people came to chat with other netizens (remember when that was a word?).
  • File Transfer Protocol (FTP) – This was an early, and still in use Internet protocol for file sharing. In the days before BitTorrent and the Pirate Bay, anonymous FTP servers were where the pirates went to trade their warez.

Before the web these were the main services that were used on the Internet. They were all text based and text information was the main thing being shared on most of them. If you wanted a richer multimedia experience, early Internet consumers turned to services like America Online, CompuServe, and Prodigy. They offered walled gardens of multimedia content.

But then came the web. At first it was text based too, but multimedia was quickly added. With the advent of the graphical Mosaic web browser, then Netscape Navigator, and finally Internet Explorer, the web took off like a rocket. Standards bodies were set up and mostly agreed upon and the golden age of the web began.

From the mid-90s to about the year 2000 the web grew at a tremendous rate, but after that the newness started to wear off. People began to wonder what was next for the web. In 1999 a new term was coined, Web 2.0. This term referred to the use of dynamic content on web sites (until that point most web content was just static content on a page). Web 2.0 allowed the web consumer to interact with content. We were on the verge of a new golden age, but there was a threat. After crushing Netscape Navigator in the great browser wars, Microsoft allowed it’s browser Internet Explorer to languish. Innovation ground to a halt and web developers spent most of their time writing around rendering errors in IE’s browsing engine. Finally, in 2003 a new web browser called Firefox rose from the ashes to challenge IE and allowed Web 2.0 to reach its full potential.

Okay, now the history lesson’s over. We get to the heart of it in Part 2!