Click here to Skip to main content
15,861,125 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am having a huge issue here! I have a MySQL database that exists on Godaddy. I am developing a .Net application that works off of a MSSQL database. The mssql database is built and the application coming along, however I need a way to merge the MySQL database to MSSQL. Normally this would be a simple task, but the original information contained in the MySQL database is bloated, incomplete, and inconsistent.

In turn, I cannot do a strait conversion. I need a way of producing a select statement from the MySQL database and insert information into MSSQL. Is there a way to communicate between the two data sources in this way?

The original database is still being used, so I need a process that can be tested now, but duplicated later when the application is complete and ready to implement.

In addition, GoDaddy will not allow me to commit stored procedures to the MySql database.
Posted
Updated 14-Jan-10 10:01am
v2

Well, you create a webservice, which exposes a method, something like:

[WebMethod]
public DataSet RunSQL(string sql)
{
// code to run the SQL and return the data

}

Then you create a reference to that webservice in your new project. If you're worried about security, work out your IP address and make the web service only respond to that. Then, you write code that runs SQL to request the content of each table. Then you iterate over that content and sanitise it before inserting it into your SQL Server database locally.
 
Share this answer
 
You could put a webservice on your godaddy site ( still the worst web host on earth BTW ), which allows you to request data from your MySQL. I am thinking of something that takes a SQL string and returns a dataset. Then, you could write a program which calls the webservice, filters the data and inserts it. Sounds messy, but it seems like the logical path.
 
Share this answer
 
Can you provide a example or rundown for this? I am still pretty new to the whole thing, and this is my first time having to deal with something like this.
 
Share this answer
 
I've had that same issue before.

What I've done is converted from the MySQL database to a second MSSQL database.

So you have:

originalMySQL on MySQL, and originalMSSQL on MSSQL. First, simply port the MySQL (originalMySQL on MySQL) to newMSSQL on MSSQL. It may not even have the same table structure as the originalMSSQL database, but that's fine. Just do a direct port as if you were building it from scratch to get the data into MSSQL.

Then, you can write your specific queries to migrate it from one database to the other as a MSSQL problem and factory MySQL out of the equation.

For me, it was as simple as installed MySQL on my local machine, going into GoDaddy and backing up/exporting the database, importing it on my local machine, then using DTS to copy the tables over. I've also used linked servers to do the same thing in the past.
 
Share this answer
 
Hey Guys,
Thank you both for your responses, but I still cannot get this to work!
I have attempted to run this through .net as well as porting the mysql to mssql, but either Godaddy will not allow the accesses, or the data/syntax are incorrect for transfer.

I just attempted to export from godaddy to a .csv and use batch insert, but I either get errors, or buffer overflow.

I am in desperate need of a solution here. Can anyone provide me with something? The tools/methods that I need to use for this.

Thank you for your continuing help
 
Share this answer
 
Thank you both for helping with this. I FINALLY figured it out. In all of my attempts to manipulate the information, I was using a generic SQL connection, where I needed to download a MySQL connection manager and add the reference within the code. Rookie mistake, but that's what I am... :)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900