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

Extracting Data From a MySQL Linked Server Expand / Collapse
Author
Message
Posted Friday, December 17, 2010 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 12:35 PM
Points: 2, Visits: 10
Hi all,

I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need, and I was unable to find a post where someone had a similar issue.

In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.

When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.

When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'

This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.

I have read articles on this forum about people who couldn't see the tables in the explorer, but could access them in a query. I've read articles where people could see some system tables, but not user tables. I find it weird that I can see them ALL in the explorer, but can't access any of them.

Any help would be greatly appreciated.

Thanks,
Bill
Post #1036585
Posted Friday, December 17, 2010 8:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 4,930, Visits: 8,744
Have you tried OPENQUERY?
SELECT *
FROM OPENQUERY(LinkedServerName, 'SELECT * FROM SomeRemoteTable')



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1036586
Posted Friday, December 17, 2010 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 12:35 PM
Points: 2, Visits: 10
SSCRAZY,

Thanks for the fast reply. I feel a little embarrassed. That worked fine.

Thanks so much,
Bill
Post #1036597
Posted Thursday, September 12, 2013 5:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 17, 2014 1:13 PM
Points: 16, Visits: 428
Thank you, I just had to do that, it worked for me as well.
Post #1494105
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse