Tuesday, May 27, 2008

The first date... ever.


I've been working on the third version of Vend-Trak for the past couple of weeks. We're going to incorporate Google Maps with directions, Google Charts (faster than the Java charts we've been using) and some other neat features. But really the biggest changes are all "behind the scenes".
Currently the application is written in ASP Classic (3.0) and is supported by a MySQL database.  These choices were great at the time, and they served us well.  But Vend-Trak has grown so much in the past few months that it's almost grown "too big for it's britches", as we say down here in Arkansas.
So that lead us to the decision that it is time for Vend-Trak to make the jump to ASP.Net.  And we figured we might as well migrate to a full fledged Microsoft SQL Server while we were at it.  This system-based relational database, along with the compiled ASP.Net application, should result in a MUCH faster application for the end-users. (I'll post the results when we're done.)
So that brings me to this blog.  I knew that migrating from MySQL to SQL Server wouldn't be easy.  But after some Googling I came across some helpful information.  First, I needed to install the MySQL ODBC driver on my SQL Server so that I could pull the information from the old server.  At that point I could create a "linked server" on the SQL Server by using a command similar to this:
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=ORIGINAL_DATABASE_NAME; USER=your_username; PASSWORD=your_password; OPTION=3'
Now that the MySQL server was linked from the SQL Server, I could copy each table from the original server to the new one with a single line:
Select * into newTable from openquery(MySQLServerName, 'select * from oldTable')
Up until this point it looks that maybe this won't be so bad after all. However, I hit a snag.  After importing two of the 33 tables, I received this message:
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
It took me several cups of coffee and lots of research to finally discover that MySQL's datetime datatype has a minimum value of January 1, 1000, while Microsoft SQL Server's datetime datatype begins with the first day of the year after the Gregorian calendar was put into use in British and American records: January 1, 1753.  (Thanks to Peter Gulutzan and Trudy Pelzer for that tidbit of information.)
Now you might be thinking, "Josh, why is the world would you have dates in this database that are before January 1, 1753?"  Well I'll tell you: inadequate error checking.  When we provided inputs for users to be able to enter dates, we didn't consider that they would enter "8" as a year.  Of course, when the users entered such a date and they didn't see the item that they just created on the calendar, then they created it a second time using VALID dates and got the expected result.
So, while there was a valid problem occuring, the end-users probably never realized that this was indeed a valid issue that needed to be reported, and we never considered that such a scenario was taking place.
I hope that this is useful to someone else and saves them from countless hours of frustration.