Avatar of akivashapiro
akivashapiroFlag for Afghanistan asked on

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

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.
Microsoft SQL ServerMySQL Server

Avatar of undefined
Last Comment
akivashapiro

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Carl Tawn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
akivashapiro

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.
Carl Tawn

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?
ASKER
akivashapiro

Same. I am running the query from within SQL Server Management Studio 2012 on the machine with the DB.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Carl Tawn

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.
ASKER
akivashapiro

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?
Carl Tawn

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
akivashapiro

It seems I had the 32 bit driver. I installed the 64 bit driver and it worked. Thanks.
ASKER
akivashapiro

Hey Carl,

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

Thanks!
Carl Tawn

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
akivashapiro

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.
Carl Tawn

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.
ASKER
akivashapiro

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)".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Carl Tawn

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?
ASKER
akivashapiro

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.