Solved

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

Posted on 2014-01-06
15
7,242 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
  • 8
  • 7
15 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
SQL Server Configuration Manager WMI Error 11 21
too many installs coming along with SQL 2016? 1 17
question about results where i dont have a match 3 23
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 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