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.