Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to access data on LINUX MySQL using a different SQL Server 2005 Machine...?

Posted on 2008-06-12
11
Medium Priority
?
447 Views
Last Modified: 2008-06-19
Experts:

I have a SERVER that has Ubuntu Linux OS running and MySQL.

I need to ACCESS the Data in MySQL using another SERVER that runs Windows Server 2003 and has SQL Server 2005.

I think I can use the LINKED Server process in SQL Server, but need some direction on HOW to get this done.

Your help is greatly appreciated...

THanks
M
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 2
11 Comments
 
LVL 9

Accepted Solution

by:
Rurne earned 2000 total points
ID: 21771888
1. You'll need to set up an OLE DB provider for MySQL before you can link to the server.  Download it here:

http://cherrycitysoftware.com/CCS/Download/Download.aspx

2. Activate the provider you just installed
3. Open Microsft SQL Server Management Studio and connect to SQL Server 2005
4. In the Object Explorer, expand Server Objects (F1)
5. Right click on Linked Servers, select New Linked Server.

6. Fill in Linked Server name with 'MyRemoteMySQLServer'
7. For Server Type, select "Other data source"
8. Select 'MySQL Provider' as Provider
9. Data Source should be the hostname or IP address of the remote box running MySQL
10. Fill Provider String with 'User ID=user;Password=pword;' , where 'user' and 'pword' are the actual username and password for the MySQL host.
11. Fill Catalog with  the MySQL database name
12. On upper left conner of this form, click Security.

13. At the bottom of the Security form, select "Be made using this security context"
14. Fill in the same user for "remote login", and password for "password", as you used in step 10.
15. Click OK to save.

16. Finally, in SQL Server, run this query to activate the proper permissions to use this linked server:

EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', 'AllowInProcess', 1
GO
0
 
LVL 17

Author Comment

by:MIKE
ID: 21772016
Great info...I'm trying now...and let you know how it goes.

THANKS
0
 
LVL 17

Author Comment

by:MIKE
ID: 21772810
Issue...SQL Server Can't find the Stored Proc:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'master.dbo.sp_MSset_oledb_prop'.

Thanks
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 17

Author Comment

by:MIKE
ID: 21772964
Ok I think I know what the issue is....I was setting this up using a SQL 2000 connection and DB.

Can I do this using SQL 2000 OR...will I need to switch to a SQL Server 2005 database..?
0
 
LVL 17

Author Comment

by:MIKE
ID: 21772978
More info...I'm using SQL Server 2005 Management Studio to ACCESS a SQL 2000 DB.

I do have SQL 2005 DB's but on another Server BOX. If I need to go over to that box..I will..but just wanted to doubecheck to see IF this LINKED Server can be setup using SQL 2000 or not...?

Thanks
M
0
 
LVL 9

Expert Comment

by:Rurne
ID: 21773156
Ah.  That component's built for 2005, using Visual Studio 2005... I'm not exactly sure about the backwards compatibility with 2000 as I don't have it at my disposal to test, but I'd imagine 2000 doesn't have all the ADO.net components needed.

In that case, you'd probably have to use the SQL Server OLE Provider for ODBC.  You can always download the MySQL Connector/ODBC for Windows (http://dev.mysql.com/downloads/connector/odbc/5.1.html) and set up a System DSN to the MySQL server on the SQL Server box.  Then, you can access MySQL's data over ODBC.  It's a bit more inefficient than the OLE DB provider, but it is at least an alternative.

To take advantage of the full OLE provider and setup as a Linked Server, you'll need to go with 2005.  2000 seems ODBC-only.
0
 
LVL 17

Author Comment

by:MIKE
ID: 21773186
I think I have it setup now......so next question is....how do I query the MySQL DB...from SQL 2005...???
0
 
LVL 17

Author Comment

by:MIKE
ID: 21773246
whats the basic syntax?

Can I SEE the entire list of DB Tables from my SQL 2005..or will I have to just KNOW what tables are there...?
0
 
LVL 17

Author Comment

by:MIKE
ID: 21773746
This Query:

Select * from openquery(MYREMOTESMYSQLSERVER,'select* from dm.dbo.client')

Give me this error:

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "OleMySql.MySqlSource" for linked server "MYREMOTESMYSQLSERVER".

Thanks
M
0
 
LVL 17

Author Comment

by:MIKE
ID: 21773871
Also, I notice that when I was setting up the LINKED SERVER, after I chose the "MySQL Provider",...the PROVIDER STRING, LOCATION, and CATALOG fields are all DIMMED and I cannot enter anything there.

Not sure why they get dimmed when I chose the MY SQL PROVIDER....?

By the way, this is a 64byte Server....if that matters at all.....???
0
 
LVL 17

Author Comment

by:MIKE
ID: 21823918
I'm still working through this,...but your info was most helpful...
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question