Updating ASP Classic Scripts from MSSQL to MySQL

OK I had gone through all the planning and I had actually moved all my millions of rows in my databases from the MSSQL tables I’d been using for nearly twenty years over into a brand new MySQL database. Here was that process – https://aspisfun.com/blog/migrating-a-database-from-microsoft-mssql-to-mysql/

But now was to come the second half of the project.

That is, now I had to make sure all my ASP Classic scripts worked flawlessly with this new MySQL database and its data.

For me, fortunately, it wasn’t about having to learn how to use MySQL or to understand ASP Classic coding for MySQL. I’ve been using MySQL databases alongside my MSSQL database for years and years.

So the first step was to change the ODBC connector I used in my connection routines to connect to the new MySQL one rather than the MSSQL one. Since I use an include file with that information, so it’s set in one place for an entire website, that was easy to change. I changed that one line and POOF the entire website was now using the new MySQL database.

Now it was simply a matter of watching my scripts for problems.

Note one way to handle this is to edit your 500-error processing so it goes to a 500.asp file and that file emails you a message every time it triggers, with the error.

Most of the scripts continued to run just as they always had.

One situation which caused a problem was sending in a date-related query. MSSQL expects a date in the format of 12-31-2019. MySQL expects a date in the format of 2019-12-31. So I had to do some rearranging in my scripts which involve dates, to make sure the dates were fed in the way MySQL prefers.

Another situation involves update scripts which rely on two tables for the data.

MySQL vs MSSQL Updating Based On Two Tables

Other than that, so far so good! I’ll update this post as I run into other anomalies!

Have you run into challenges with your conversions? Let me know!