do i need a driver for mysql?

Question

do i need a driver for mysql?

stan

Ten Centuries

Points: 1189
More actions
January 21, 2025 at 7:45 pm
Go to Answer
#4520203
hi we'll want to communicate with a mysql server at one of our locations from our sql dw server for warehouse extracts daily.
i thought i saw everything from oracle sources to sql sources to oracle's netsuite as a source to native sql drivers etc etc but do not believe i've ever seen a sql server communicate with a mysql server source.
do i need a special driver on my dw sql server to communicate with the mysql server? and will our pattern of simply using our service acct to login to our normal sql server sources work here or are there special considerations?
- This topic was modified 7 months, 2 weeks ago by stan.
- This topic was modified 7 months, 2 weeks ago by stan.
- This topic was modified 7 months, 2 weeks ago by stan.
kaj

SSCommitted

Points: 1616
More actions
January 30, 2025 at 2:48 pm
Answer
#4526207

You will have to install the mySQL ODBC driver on your SQL Server and configure it.
Then you can set your remote mySQL server up as a Linked Server. Use the MSDASQL provider.
Best use Google or Bing to search for instructions on how to set this up. For example with this search phrase:
sql server linked server mysql.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stan Ten Centuries Points: 1189 More actions · Answer 1

i see an odbc driver at this site but still wonder if our existing sql drivers cant establish this kind of communication? this looks like an open source product.

https://www.mysql.com/products/connector/

stan Ten Centuries Points: 1189 More actions · Answer 2

a few words of caution to the community...

use this link as a guide, it has info you wont figure out yourself https://www.sqlshack.com/how-to-create-and-configure-a-linked-server-to-connect-to-mysql-in-sql-server-management-studio/ . I think that nested setting has to do with algorithms (maybe query plans?) run by the connector software itself but dont quote me on that. We've seen bugs in other products whose errors reference nested queries that presumably they dynamically generate and run to mitigate issues inherent in using the "product" being linked to.
one thing i think is wrong at that link is that you have to put in the provider connection string in spite of the fact that you're putting remote user creds in i think in at least 2 other places (one being the dsn creation).
I've seen 2 stories on what to put in the "product" text field when setting up the linked server. The evidence suggests to me it can be anything but there is an author out there who claims it MUST be MSDASQL. I disagree.