Linking to mySQL from MS SQL 2008

I'm having problems accessing mySQL data from  SQL 2008 database.
I created a linked server through ODBC DSN.
When I expand mySQL linked server node in SQL Management studio I can see the list  of all tables in mySQL db. But I cannot access the data -  when I right click on a table and try to script it I get
" [LINKED_MYSQL].[db]..[tbl] contains no columns that can be selected or the current user does not have permissions on that object."
When I try "SELECT * FROM [LINKED_MYSQL].[db]..[tbl]"
I get the following error -
"Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "LINKED_MYSQL". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema."

Please help.
Thank you.
larisa1970Asked:
Who is Participating?
 
St3veMaxConnect With a Mentor Commented:
Try using OPENQUERY...

This is a working sample from me querying our IP Phone Server for reporting purposes.

"TRIXBOX_MYSQL" is the ODBC name for the MySQL Server.

HTH
SELECT
	 calldate
	,DATEPART(HOUR, calldate) AS HourReceived
	,disposition
	,duration - billsec AS TimeToAnswer
	,duration
	,billsec
FROM 
	OPENQUERY(TRIXBOX_MYSQL, '
		Select 
			 calldate
			,channel
			,src
			,disposition
			,duration
			,billsec 
		from 
			asteriskcdrdb.cdr
		WHERE
			dst = ''601''
				and
			duration > 0
				and
			disposition = ''ANSWERED''
			
			')
WHERE
	calldate BETWEEN @DateFrom AND @DateTo

Open in new window

0
 
MohammedUCommented:
Make sure DataAccess is enabled for linked server configuration and login has the access...

EXEC master.dbo.sp_serveroption @server = <>, @optname = N'data access',
@optvalue = N'true'

0
 
MohammedUCommented:
0
 
larisa1970Author Commented:
MohammedU - that's the link that I used to set up the linked server
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.