Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1366
  • Last Modified:

Trouble creating Linked mySQL Server

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
studioEtc
Asked:
studioEtc
  • 3
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
studioEtcAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
studioEtcAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
studioEtcAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now