Share via

Converting MySQL Queries to MS SQL Server Queries

  • Article

At the JumpIn! Camp that took place a few weeks ago, PHP developers from several countries were invited to come to Redmond and work closely with Microsoft developers to build support for SQL Server and SQL Azure into their applications.  (As expected, the camp was more than just that. It was also an excellent two-way learning experience…you can read more about the camp here). One of the challenges that these PHP developers faced was in migrating their MySQL schemas and data to SQL Server. The tool that helped in doing this was the SQL Server Migration Assistant for MySQL 1.0 (SSMA), which, by most accounts, did a good job at converting databases. (For a quick tutorial on how to use this tool to migrate a database, see this post.) However, after converting databases, developers still had to convert individual queries from MySQL syntax to SQL Server syntax in order to build SQL Server support into their applications. For most simple queries, this isn’t terribly difficult. However, for some queries, the conversion is not at all straight forward. What I didn’t know (and was happy to learn) was that SSMA can also help convert queries. So, in this post I’ll show you how to use SSMA to convert a single MySQL query to a SQL Server query.

1. Create a Project: All the information you need for downloading SSMA, creating a project and connecting to databases is in steps 1-6 of this blog post. In the remainder of this post (as in the linked-to post), I’ll use the WordPress database as my example database.

2. In the MySQL Metadata Explorer, navigate to the Statements directory of your MySQL database:

image

 

3. Paste the query you want to convert into the query editor window. This is the query I’ll convert:

SELECT post_title, post_date FROM wp_posts ORDER BY post_date LIMIT 5 OFFSET 5;

image

4. Back in the MySQL metadata explorer, right-click Statements and select Convert Schema:

image

5. When prompted to save changes, select Yes:

image

6. Copy the converted query from the SQL Server query editor window:

image

That’s it…repeat for other queries as necessary. Note that SSMA will not successfully translate all MySQL queries, but it does for most. It does not translate some MySQL-specific functions (for example FOUND_ROW()). However, I have found that if it doesn’t translate a query successfully, it often provides enough of a clue that I can figure out ways to manually write the translation. Try it out and let me know what you think.

Thanks.

-Brian

Share this on Twitter

Comments

  • Anonymous
    December 06, 2010
    This is actually some really good information that I will be able to use thanks!<a href="www.ucvhost.com/.../EntryLevelVps.aspx">windows vps</a>
  • Anonymous
    December 07, 2010
    Glad it was helpful. I'd be interested in understanding any issues you run into when using the tool.Thanks.-Brian
  • Anonymous
    July 01, 2011
    The comment has been removed
  • Anonymous
    January 27, 2015
    Hi All,I'm tried with what are all the steps given above, I can create statement but I couldn't convert schema becoz of convert schema always in disable mode. Is it possible to enable convert schema OR Is anything wrong I did.
  • Anonymous
    September 07, 2015
    good day just like prakash said l tried follwing all the steps outlined above but l couldn't convert schema coz of convert schema wa in disable mode.please what can l do.