Products
Services
Contact Us

Scripts: asp :: MS SQL and Database :: Library Article #9

Developer's Section

Migrating database data from Microsft SQL Server to MySql
By: Erobo Team Member

Hire a Developer for Related Work / Installation | $55 hr
Rating:  | Rate It:   
Average Votes: (1737)
Favorites:

Learn to migrate database data from MS SQL Server to Mysql. Includes table design structure and data insert statements. Note: Can export sql statements or insert directly to new Mysql Database.

Follow these simple steps to migrate a Microsoft SQL Server database to a Mysql Database.

Step 1: Download the file (mssql2mysql.txt) provided in the downloads section of this article.

Step 2: Copy and Paste the program to Microsoft Visual Basic Editor. If you have Microsoft Excel you can do so by:

  • Start Microsoft Excel
  • Go to Tools
  • Select Macro
  • Select Visual Basic Editor
  • Click Create new module
  • Paste the Program on the space provided

Step 3: Change you Microsoft SQL Server properties on the first lines of code:

 Code Snippet 1

         'M$ SQL Server
Const MSSQL_SECURE_LOGIN = False   'disabled as default
Const MSSQL_LOGIN_NAME = "Mylogin657"       'login name e.g: Mylogin657
Const MSSQL_PASSWORD = "def4567"         'change password here e.g: def4567
Const MSSQL_HOST = "100.34.234.457"      'change host name here e.g: 100.34.234.457
Const MSSQL_DB_NAME = "db6746" 'change database name db6746



Step 4: Run the program. The program should output the program to a file e.g(c:/export.sql) or if you choose to insert directly to your Mysql Database (not recommended unless you know the db host configuration e.g. port numbers, security add-ons, etc.)

Step 5: Copy and Paste Sql statements into your Query Box on your new MySql server.

Step 6: Additional Tips:
Once you have migrated all the data you can fix the connection string for your scripts.
If you are using ASP you can change the configuration of your connection string on the global.asa file:

 Code Snippet 2

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
'Use a mySql driver on your ASP applications
Sub Application_OnStart
Application("connectstring") = "DRIVER={MySQL ODBC 3.51 Driver};
                                        SERVER=293.345.34.23; 
                                        DATABASE=new_db6746;
                                        USER=newuser20;
                                        PASSWORD=newpass46;
                                        Option=3;"
End Sub
</SCRIPT>



if you are using PHP you can connect to mysql like this:

 Code Snippet 3

<?

//set local variables
$dbhost = "293.345.34.23"; 
$dbuser = "newuser20"; 
$dbpass = "newpass46"; 
$dbname = "new_db6746"; 

//connect 
$db = mysql_pconnect($dbhost,$dbuser,$dbpass); 
mysql_select_db("$dbname",$db); 

?>


So, That's it. Enjoy Migrating your data to Mysql!!

Downloads
mssql2mysql.txt19 KB
 

See other Scripts in MS SQL and Database

Submit Your Scripts:

If you would like to have your ASP & ASP.NET scripts published in this section please fill out
the form below:
*Your Name or Username:
Home Town:
*Email:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]