Porting MS SQL stored procedures to MySQL

i have about 140 - 150 stored procedures that I need to migrate from MS SQL 2005 to MySQL 5.1

After succesfully porting 0 of them in the last eight hours I decied to drop by and ask if anyone has any experience, or tips, or knows some util app that can help.

MySQL Administrator is not very helpfull, with a frustrating "MySQL Error number 1064 You have an error in your SQL Syntax; check the manual that corresponds to your MySQL server version for the right syntax". Given that an average SP is +100 lines long, simply GUESSING what the problem is is not an option.

Eg. This i开发者_如何学JAVAs the exact error that I get :

  Script line: 1    You have an error in your SQL syntax; check the manual that
   corresponds to your MySQL server version for the right syntax to use near '' at line 5

Then at line 5 all I have is:

declare PropID float;

Gartner Group reports that manual conversion between two similar langauges occur at the rate of about 150 lines per day. According to them, your rate of progress is just a little bit lower than average :-} Worse, it predicts you have 150 man-days of effort in front of you.

This is the reason that people build automated migration tools, that can apply conversion rules in a regular and trustworthy way. If you are lucky, somebody has done this before you. The number of pairs of from-to conversions people might want to do is huge, much larger than the set people have done, so typically you're out of luck.

The next thing somebody will tell is "use regular expressions". That probably won't work, because you have to find programmiong language constructs, which are not "regular" but rather "context free".

One way to get a tool to do this is use program transformations. These are programmed actions that replace one code fragment by another. They typically require a full language parser so that actions can operate on program elements like compilers do, rather than on text strings.

A nice way to write program transformations uses the surface syntax of the language as patterns, essentially of "if you see this, replace it by that". Think of these as "regular expressions" beefed up to handle "context free" issues.

A tool that can accept such surface-syntax program transformations, is our DMS Software Reengineering Toolkit.

It takes some effort to code trustworthy transformations. You have some 15,000 lines of code to convert. The tradeoff between coding the transformations vs. simply biting the bullet and doing the work is tough when the amount of code to transform is this small.

You might try MySQL Query Browser instead of MySQL administrator:

Where the MySQL Administrator is designed to administer a MySQL server, the MySQL Query Browser is designed to help you query and analyze data stored within your MySQL database.

You're porting from a powerful tool to a less powerful tool. If the old procedures use features that MySQL does not support, this can make porting practically impossible.

One thing that might help is that most SQL Server procedures implement business logic of some sort. The good news is that often only a small part of them is used. Filtering out unused procedures can save you a lot of work.

The bad news is that business procedures have to be tested after porting; not even the best programmer can rewrite them in a different language and be sure that they work. This means a lot of testing effort, and a lot of support work after the ported procedures have gone into production.