Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213

How to Create Linked Server for a MySQL database using SQL Server Management Studio Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 7:34 AM
Points: 1, Visits: 7
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
Post #1461905
Posted Thursday, September 19, 2013 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 9:37 AM
Points: 1, Visits: 5
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.
Post #1496428
Posted Monday, January 26, 2015 3:02 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 14, 2016 6:00 AM
Points: 3, Visits: 155
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
http://www.TheDatabaseAvenger.com
Post #1654612
Posted Friday, January 30, 2015 4:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:41 AM
Points: 2,918, Visits: 8,950
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



Post #1656435
Posted Tuesday, June 9, 2015 4:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 16, 2016 12:53 PM
Points: 39, Visits: 190
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/



Post #1693044
Posted Friday, December 25, 2015 1:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 28, 2016 6:21 PM
Points: 216, Visits: 396
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
Post #1747770
Posted Thursday, February 11, 2016 5:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 15, 2016 4:00 PM
Points: 102, Visits: 129
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.
Post #1760668
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse