Adding Mysql As a MS SQL Linked Server

Adding Mysql As a MS SQL Linked Server

Adding MySQL as a MS SQL Linked Server

Download the MySQL OLEDB Provider from Cherry City Software from the following URL.

Install the MySQL OLEDB on your MS SQL Server.

Provider Activation

All data providers on this download page must be activated before you can take advantage of them.

To activate a provider:

  • Download the provider installation package, unzip it and run setup to install the provider
  • Find Activator.exe that is located at wherever the provider installed
  • Run Activator.exe and select the provider that is installed from the drop down list
  • Take the computer specific passcode to the activation page to generate an activation code
  • Enter the activation code into Activation Code field on the Activator and click Activate Provider button

Setup Linked Server

If you have done some kind of linked server setup, it shall be easy for you. Here are some steps you need to follow in order to setup MySQL as Linked Server using MySQL OLE DB Provider.
  • Install MySQL OLE DB Provider on SQL Server 2005 database server
  • Activate the provider you just installed
  • Open Microsft SQL Server Management Studio and connect to SQL Server 2005
  • In the Object Explorer, expend Server Objects (F1)
  • Right click on Linked Servers, select New Linked Server, you see the New Linked Server form (F2)
/
F1. Microsoft SQL Server Management Studio
New Linked Server
F2 New Linked Server form
  • Fill in Linked Server name with 'MYLINKEDSVR'
  • Select Other data source as Server Type
  • Select 'MySQL Provider' as Provider
  • Fill in MySQL Server Name or IP address as Data Source
  • Fill Provider String with 'User ID=root;Password=mySecret;'
  • Fill Catalog with 'test' - the database name in MySQL
  • On upper left conner of this form (F2), click Security, you will see the linked server Security form (F3)

Linked Server Security
F3 Linked Server Security
  • At the bottom of F3, select Be made using this security context
  • Fill in 'root' as remote login and 'mySecret' as passord
  • Click OK to added this new linked server
  • One last thing to do is to grant permission by running the following SQL script against SQL Server 2005:
EXEC master.dbo.sp_MSset_oledb_prop N'OleMySql.MySqlSource', 'AllowInProcess', 1
GO

Now that you have a LinkedServer you can create Views within your Cherwell Database that automatically connect to MySQL. Once you have created a view you can connect to the View using a Cherwell External Business Object.

Example of Creating a View in MS SQL pointing to MySQL

CREATE VIEW Demo

As

select * from openquery(MYLINKEDSVR, 'select * from MyTable')

Top View