?
Solved

Linking to mySQL from MS SQL 2008

Posted on 2010-01-06
4
Medium Priority
?
1,522 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:larisa1970
  • 2
4 Comments
 
LVL 15

Expert Comment

by:MohammedU
ID: 26193514
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
 
LVL 15

Expert Comment

by:MohammedU
ID: 26193538
0
 

Author Comment

by:larisa1970
ID: 26194760
MohammedU - that's the link that I used to set up the linked server
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 2000 total points
ID: 26200085
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question