SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extracting Data From a MySQL Linked Server


Extracting Data From a MySQL Linked Server

Author
Message
bshirer
bshirer
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9746 Visits: 13349
Have you tried OPENQUERY?

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



--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
bshirer
bshirer
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 11
SSCRAZY,

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

Thanks so much,
Bill
Ralph Thomas
Ralph Thomas
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 508
Thank you, I just had to do that, it worked for me as well.
heinrich.angela
heinrich.angela
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 91
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.
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9746 Visits: 13349
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
bshirer
bshirer
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
wonderwoman
wonderwoman
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 64
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
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27900 Visits: 39920
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search