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, May 5, 2014 9:23 AM
Points: 3, Visits: 11
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 @ 10:37 AM
Points: 4,421, Visits: 10,738
Have you tried OPENQUERY?
SELECT *
FROM OPENQUERY(LinkedServerName, 'SELECT * FROM SomeRemoteTable')



--
Gianluca Sartori

How to post T-SQL questions
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, May 5, 2014 9:23 AM
Points: 3, Visits: 11
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: Yesterday @ 12:41 PM
Points: 16, Visits: 434
Thank you, I just had to do that, it worked for me as well.
Post #1494105
Posted Monday, May 5, 2014 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 3:40 AM
Points: 2, Visits: 64
spaghettidba (12/17/2010)
Have you tried OPENQUERY?
SELECT *
FROM OPENQUERY(LinkedServerName, 'SELECT * FROM SomeRemoteTable')



Great I'm also getting results with OPENQUERY.
HOWEVER, i'm interested in knowing why it doesn't work with the 4-part naming convention.

I get the error: Invalid object name.
Also when I try script the , 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.'
I'm looking for a way to pull data from a MySQL instance into MSSQL. The way I want to accomplish this is by scheduling the SSIS package created with the SQL Import-Export wizard. To do so I would have to select the Tables from the LinkedServer as source.


Any and all help is welcome.
Post #1567547
Posted Monday, May 5, 2014 8:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 4,421, Visits: 10,738
I have no idea. It is maybe pointing to a different schema/database?
It's hard to know without tracing what the provider actually tries to query.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1567553
Posted Monday, May 5, 2014 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 9:23 AM
Points: 3, Visits: 11
Well, I am assuming based on the thread that you are using a lined MySQL database. The linked server already has the server information in it, and if you want to access another server, I would think you'd have to create a new linked server for that.

You are basically saying in your example:

select * from openquery(server, 'Select * from server.database..tablename') - and saying server 2x is redundant. I think you are also using the 4 point syntax that would be used in MS SQL, but you are linking to MySQL. If it was MS SQL then you could use server.database.owner.table.

Keep the server in the 1st param of the openquery function, and then just use database.table in the actual query and you should be fine.

If you do need to link to more than 1 server in a single query, then you may need to make a view on one server that accesses a second server, and then have openquery link to the server with the new view and make your call there.

Post #1567565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse