?
Solved

Connect to MYSQL/ODBC Table from SQL Server Management Studio 2012

Posted on 2014-01-06
15
Medium Priority
?
8,074 Views
Last Modified: 2014-01-07
Please let me know how to create a connection to a table via ODBC (or native if possible) from within SQL Server Management Studio 2012.

I want to be able to create a native SQL query in SQL Server that joins two tables, one from SQL Server and one from MYSQL without using a front end tool such as MS Access.

Thanks.
0
Comment
Question by:akivashapiro
[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
  • 8
  • 7
15 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 39760259
You should be able to query MySQL using the OPENROWSET command:
SELECT <column_list> 
    FROM OPENROWSET(N'MSDASQL',N'Driver={MySQL ODBC 5.1 Driver};Server=myServer;Database=YourDB;User=YourUser;Password=xxxx;Option=3;', N'SELECT <column_list> FROM YourDB.YourTable LIMIT 1000')

Open in new window

You may have to modify the driver part to suit what you have installed on your system.
0
 

Author Comment

by:akivashapiro
ID: 39760413
Thanks very much. It looks like this has set me on the right track. I'm getting the following error when trying to connect:

SELECT *
    FROM OPENROWSET(N'MSDASQL',N'Driver={MySQL ODBC 5.1 Driver};Server=192.xxx.xx.xxx;Database=xxx_view;User=xxx_user27;Password=xxxx;Option=3;', N'SELECT * FROM xxx_view.abc LIMIT 1000')

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

I also tried MySQL ODBC 3.51 Driver.

Both are on my system under ODBC when I try to connect via MS Access.

Please let me know your thoughts.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39760439
This might be a daft question, but is SQL Server on the same machine you are running the query from, or is it on a separate server?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:akivashapiro
ID: 39760445
Same. I am running the query from within SQL Server Management Studio 2012 on the machine with the DB.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39760448
OK, as a test try adding your MySQL server as a linked server and see if it works that way. At least then you will be able to see what driver/provider details you need.
0
 

Author Comment

by:akivashapiro
ID: 39760461
I've tried that as well, both through code and the interface. I've used the same string as above in Data Source field , and the error message returned is the same. Any other thoughts? Perhaps you know all the correct parameters for the interface?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39760497
I don't have a MySQL install to test this with at the moment - but try the provider as "MySQLProv" and see if that gets you any further.
0
 

Author Comment

by:akivashapiro
ID: 39760539
It seems I had the 32 bit driver. I installed the 64 bit driver and it worked. Thanks.
0
 

Author Comment

by:akivashapiro
ID: 39763183
Hey Carl,

Can you give me the OPENROWSET parameters for a local MSAccess accdb file please.

Thanks!
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39763215
It should be something along the lines of:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'c:\path_to_db\DbName.mdb';'username';'password', table_name) 

Open in new window

0
 

Author Comment

by:akivashapiro
ID: 39763339
Here's what I get:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I have MS Access 2010 64-bit installed on the same system as sql server (and all of Office 2010 64-bit)

Should I open another question so you get credit for this additional work? If so, how I should I title it so you receive notification.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39763402
If it's 64-bit then try this as the provider instead 'Microsoft.ACE.OLEDB.12.0'  - I don't think there is a 64-bit Jet driver available.
0
 

Author Comment

by:akivashapiro
ID: 39763436
Getting closer, I think. Here's what I get now:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39763477
Can you post the connection as you currently have it? Also, did you try this through the linked server manager? Finally, is your Access DB a native 2010 one, or is it from an earlier version?
0
 

Author Comment

by:akivashapiro
ID: 39763601
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','D:\EECO\E2T2.accdb', T1)

I was unable to properly configure linked server manager. It takes longer to troubleshoot, so I figured I would get OPENROWSET to work first, then use that to setup a linked server.

I created a new db "E2T2.accdb" in Access 2010 64 bit to be sure. But the only options under Access Options/General/Creating databases/Default file format for Blank Database are Access 2000, Access 2002 - 2003, Access 2007. I chose, of course, 2007, but I don't see how to create a "native 2010" db.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

770 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