How to Create Linked Server for a MySQL database using SQL Server Management Studio

Question

How to Create Linked Server for a MySQL database using SQL Server Management Studio

Viewing 9 posts - 121 through 128 (of 128 total)

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

orniani SSC Rookie Points: 35 More actions · Answer 1

I am having some problems related with this issue. I have followed the steps to create a MYSQL linked server in SQL 2003 R2.

I have make a report that does a, select * from openquery(MYSQL TABLE), and till here everything is ok because the query runs in Managment Studio also it runs in Business Inteligence Visual Studio but when i deploy it in SSRS then I get errors. Please can anyone help me on this.

Thanks a lot

marcus_lashley SSC Rookie Points: 41 More actions · Answer 2

Brilliant Thanks. I tried recreating a connection that I had to a different MySQL box and it wouldn't do it, so best to start from scratch thanks.

James Anderson - The Database Avenger Grasshopper Points: 23 More actions · Answer 3

For anyone still trying to do this I found that OPENQUERY is not needed and you can query the data with:

LinkedServerName...TableName

James Anderson
TheDatabaseAvenger.com

homebrew01 SSC Guru Points: 55539 More actions · Answer 4

jimbojr (1/26/2015)
For anyone still trying to do this I found that OPENQUERY is not needed and you can query the data with:
LinkedServerName...TableName

In my recent case, OPENQUERY is better because when reading from MySQL, it puts the processing on the MySQL server before bringing over the data. So, for instance, ORDER BY is taken care of at MySQL, then data imported.

I was getting strange "MySQL out of memory" errors, when in fact it seems MSSQL was having the problem.

So OPENQUERY was the solution in my particular case.

http://www.sqlservercentral.com/Forums/Topic1655550-1550-1.aspx?Update=1

JRoughgarden Ten Centuries Points: 1119 More actions · Answer 5

Since the SQL Server Surface Area configuration tool is deprecated for SQL 2008 and later, you need to enable OPENROWSET and OPENDATASOURCE as below:

sp_configure 'show advanced options',1

reconfigure

go

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

go

sp_configure 'show advanced options',0

reconfigure

go

https://bimoss.wordpress.com/2010/01/07/how-to-enable-openrowset-opendatasource-in-sql-server-2008/

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 6

Thank you Jim for this detail steps procedure, it helped me to resolve my MySQL Migration to SQL Server.

Thank you again, great help.

Thanks

dvprao Ten Centuries Points: 1311 More actions · Answer 7

This was very helpful.

A rider question ( 2 in fact ) is why this odbc connection will not work in 2 scenarios ?

1. If I use openrowset , i am able to query a table or view but not if i script our select form the table in SSMS. Says you do not have permission. Linked server exists without issues and able to test connection ok in control panel ( ODBC conn manager applet ).

2. In SSRS 2012 , create a datasource and test the connection , it fails saying no default source specified. Where does one set this ?

-----------------

Note :The DEV who granted access to the MySQL Database set a user id with blank password . Could this be causing issues ?

Anyway , thanks for the post. It was helpful.

vprasad-959856 SSC Rookie Points: 33 More actions · Answer 8

i know this is an old post, btw great detailed instructions on how to setup mySQL linked server on MS SQL server. I have a question though, right now i am using

Select field1,field2 from [Linked Server]...

and it retrieves the rows from the table that i specified in the ODBC connection, how would i do it if i want to retrieve data from a different db , i tried leaving the database drop down blank in the ODBC and tried Select field1,field2 from [Linked Server].[db name]..

and i get an error 'A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.'

Any idea how to do this ? thank you in advance.

kramaswamy SSCoach Points: 18181 More actions · Answer 9

Old post I know, but bumping this just to give my thanks - setting the user and password in the linked server on SQL Server did the trick for me. Not really sure why that was necessary, since I was using an ODBC connection that had the user and password set, but either way, it worked. Cheers!