Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trouble creating Linked mySQL Server

Posted on 2009-04-04
6
Medium Priority
?
1,353 Views
Last Modified: 2012-05-06
I am having trouble creating a linked Server.
I set a system ODBC to a mySQL server in the ODBC Data Source Administrator. It test ok.
But I can't figure out to make it a linked server in the Management Studio. I have:
PROVIDER: MediaCatalogDB OLE DB Provider.
DATASOURCE: myODBCname
What's PRODUCT NAME?

ERROR:
Cannot initialize the data source object of OLE DB provider "MediaCatalogDB" for linked server "myTest".
0
Comment
Question by:studioEtc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24067979
Try using sp_addlinkedserver in the format specified below

exec sp_addlinkedserver
     @server='MySQLServer',  -- This could be anything
     @srvproduct='MySQL',  -- This could be anything
     @provider='MSDASQL',  -- This MUST be MSDASQL for ODBC links
     @datasrc='MySqlODBC',  -- This should be the ODBC System DSN.
@provstr='DATABASE=mySql;DSN=MySqlODBC;OPTION=0;PWD=;SERVER=localhost;UID=root'
0
 

Author Comment

by:studioEtc
ID: 24068000
It created the linked Server but when I attempted top expand the tables folder I got:
The OLEDB provider "MSDASQL" has not been registered.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24068054
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:studioEtc
ID: 24068094
I just figured out what's wrong (dummy me).
I created the ODBC on my local machine, but I want the linked server on the machine in our office. I'm working from home.  Is there anyway to do it without an odbc?
Jim
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24068118
Yes.. you can..
If you are using SQL Server 2000 SP3 or above, then try this one and this works fine

http://msdn.microsoft.com/en-us/library/aa276850(SQL.80).aspx
0
 

Author Comment

by:studioEtc
ID: 24068212
What I need to do is port  tables to a mySQL database.  OpenRowSet seems to be only for SELECT... But I did some looking around and found OpenDataSource which might do the trick.
 
Thanks form your help
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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