Import Data into Sql Server 2005 from MySql
-
In Sql Server 2000 I was able to use an ODBC Connection using MySql ODBC 3.51 driver to import data from a MySql database...I have used the same ODBC connection in Sql Server 2005 but when I get to the "Specify Table Copy or Query" Screen the First Selection is greyed out and I have to enter a select statement to transfer data. So I write a simple Select * from MySqlTableA...I always get an error when I execute the package.
I wanted to know if anyone has been successful in importing data from a Mysql database into a Sql Server 2005 database and what steps were taken to do this. Since we now have to use the .Net Framework I am guessing that is where I am getting a disconnect from from the success I had in Sql Server 2000 using the same ODBC driver...
Any advice or help would be greatly appreciated...
-
I can get data into both SQL Server 2005 and MS ACCESS.
For SQL Server 2005:
1. On SQL Server computer install the MySQL ODBC driver from:
ODBC and ADO.NET Driver Download page:
http://www.mysql.com/products/connector/
2. Create a new Data Source with this driver for your MySQL database. Make sure it is a System DSN. Test the connection using Test button in the Data Source. Specify MySQL User Name and Password and a default database. Assume the name of your Data Source is MySQL_DSN
3. Create a Linked Server in SQL Server 2005. Fill out the following fields:
Linked Server: MySQL_LS (this is the name I want for my Linked Server)
Provider: Microsoft OLEDB Provider for ODBC Drivers
Product Name MySQL_DSN
Data Source MySQL_DSN
Why did I have to enter something into Product Name, I am not sure, but it did not let me to save without it and the tooltip was about the Data Source, not a product name. My SQL Server 2005 does not have service packs yet and it may be a bug
4. Add Linked Server Logins on the security tab of Linked Server properties. Local Login would be the login you use to connect your Management Studio to SQL Server in this example. The remote user and remote password will be your MySQL login and password.
5. Run a query. In my case it was selecting from table Test and MySQL_LSw was the linked server name that I created in step 3
select
* from OPENQUERY(MYSQL_LS,'select * from test')
I could not run a normal query with 4-part name because it said that the provider does not support 4-part names.
Regards,Yelena Varsha
-
Thanks Yelena to the quick reply...
I will try your solution this afternoon and let you know how it worked...Thanks again
Yelena,
I have implemented your solution for importing data from a MySql 5.0 database into Sql Server 2005 and was successful...I spent all day yesterday looking for examples on how to set this up so your reponse was a godsend...Thanks again
-
We are able to get data from a MySQL database 3.5.1 into SQL 2000 too. We haven't had the need to get it into SQL 2005.
Our question is this:
Have you had any success updating a MySQL 3.5.1 database from SQL 2000. Our salesreps will enter orders into a MySQL 3.5.1 database, which exists outside of our firewall. We can use query analyzer to read info from the MySQL side and update it into our SQL 2000 tables. We then want to delete this info on the MySQL database. Do you know of a way?
Thanks, Al Cook
-
I have no problems updating MySQL database using MS ACCESS Linked Tables when I use MySQL Login that have rights to delete from or to write to the database.
Regards,Yelena Varsha
-
So, you use MSAccess to insert and delete in the MySQL database?
Have you tried using SQL Server 2000 to do so?
-
Al,
I don't use MySQL for production, I am only testing for one of the future projects. I can test with SQL 2000, will let you know
Regards,Yelena Varsha
-
Sure, let me know how you make out with it.
Thank you,
Al
-
I had to trick it.
In SQL Server 2000 I first used Import/Export wizard to copy data from MySQL table to SQL Server table. I changed data in SQL Server, so I had IDs in the ID field in MySQL from 1 to 4 and IDs in SQL Server table from 5 to 8. ID is one of the fields in my table. You are already doing that.
To delete rows from MySQL table from SQL server I used Export Wizard, specified Query for the source (SQL Server table, has IDs from 5 to 8) and specified MySQL table for the destination. Then on the Select Source Tables and Views screen made sure my destination was MySQL table and clicked Transform button. That is where "Delete rows in the destination table" selection is available! Select that and click OKs and Next. It deleted rows in MySQL table with IDs from 1 to 4 and inserted rows with IDs from 5. to 8.
The reason I selected a query, because my table contains Text field and when I tried to import all fields including Text field from SQL server to MySQL database it said, not supported with the query. Maybe it means a query in the error message because I selected Delete Rows while telling the Wizard to copy the table. But... on this try when I tried to import everything including the text field, it DID delete MySQL data too, it just did not do a second step of importing text data. So if you want to use DTS to delete rows in MySQL either try to export/import an empty table or something with a text field. Just select "Delete Rows in the Destination Table" when running the wizard.
Regards,Yelena Varsha
-
Our issue is going to be more clouded than that, I think.
In more detail...
After the user(s) enter orders into MySQL outside of our firewall, we want to run a process periodically that does the following;
1. Copy the orders to SQL Server (inside the firewall)
2. Archive these orders within MySQL to an archive table with limited information. (The details will now be in SQL Server.)
3. Delete these orders in MySQL that were already copied to SQL Server.
We can do step 1. But we can not just delete everything from the MySQL order tables, because our process could be running as orders are being entered. We can perform "select openquery..." statements, but "insert openquery" and "delete openquery" do not work.
Does this sound different from what you are doing?
Al
-
Maybe I would use MS ACCESS for that. I was participating in the projects where we used MS ACCESS to copy from different sources into SQL Server with processing. The only time somebody said No was when consultants wanted to migrate data from Oracle to Oracle using MS ACCESS.
In your case I would link 2 MySQL Tables -Orders and Archive and also 1 SQL Server table. I tested that deleting certain rows from MySQL table work with ACCESS queries. Access has VBA modules that could be used for complicated logic and then used in queries as functions. Or you can modules and run queries from modules. You can even schedule running MS ACCESS modules if you use MS ACCESS Object Model, the script creates an instance of MS ACCESS and runs its procedures.
Regards,Yelena Varsha
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply