Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 2, 2015 10:07 AM
Points: 1, Visits: 74
For anyone still trying to do this I found that OPENQUERY is not needed and you can query the data with:

LinkedServerName...TableName
Post #1654612
Posted Friday, January 30, 2015 4:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 12, 2015 3:39 PM
Points: 2,867, Visits: 8,722
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: Tuesday, June 30, 2015 3:16 PM
Points: 38, Visits: 185
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
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse