Extracting Data From a MySQL Linked Server

Question

Post reply

Extracting Data From a MySQL Linked Server

bshirer

Old Hand

Points: 365
More actions
December 17, 2010 at 8:53 am

#244492

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Have you tried OPENQUERY?

SELECT *

FROM OPENQUERY(LinkedServerName, 'SELECT * FROM SomeRemoteTable')

-- Gianluca Sartori

bshirer Old Hand Points: 365 More actions · Answer 2

SSCRAZY,

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

Thanks so much,

Bill

Ralph Thomas Mr or Mrs. 500 Points: 522 More actions · Answer 3

Thank you, I just had to do that, it worked for me as well.

heinrich.angela SSC Enthusiast Points: 120 More actions · Answer 4

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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

bshirer Old Hand Points: 365 More actions · Answer 6

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.

wonderwoman SSC Enthusiast Points: 102 More actions · Answer 7

That open query worked like a champ, thanks dear!:-D

I think the error "...contains no columns that can be selected or the current user does not have permissions on that object"

is a common issue with MSSQL server 2008 http://support.microsoft.com/kb/971261

Lowell SSC Guru Points: 323504 More actions · Answer 8

in my snippets, where i have some mySQL linked server example,s the database and schema are left blank for a typical user.

Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:
Select Statements:
select * from mysql5...country
select * from OPENQUERY(mysql5, 'select * from country')
Insert Statements:
insert mysql5...country(code,name)
values ('US', 'USA')
insert OPENQUERY(mysql5, 'select code,name from country;')
values ('US', 'USA')
Other Statements:
EXEC('truncate table country') AT mysql5;
Resources:
http://213.136.52.24/bug.php?id=39965

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!