Skip to main content

Creating a Link Server to MYSQL from MSSQL

There might be situations where you need to apply CRUD  operations (
Create, Read, Update, Delete) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below:

Steps:
1. Install the MYSQL ODBC driver on the server with the MSSQL server installation.
2. Enter details for the MYSQL ODBC settings.
3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio)
4. You will need an user account on the mysql server which can perform CRUD operations
 along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below)
4. Test the connection.


Install MYSQL ODBC Driver Download  (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This needs to be done on the server with the MSSQL installed instance.

Once installed open Windows->Control Panel\All Control Panel Items\Administrative Tools\
Look for the ODBC  Datasource and double click. Verify that the driver is installed.


Click on the system tab and click Add

Select MYSQL ODBC  x.x Driver


Enter the details of your MYSQL installation. Pay attention to the name given to the data source name as this would be the name used under link services. Enter the IP Address of the MYSQL server, Username and password. Test the connection. If all goes well the allowed schemas would appear in the list of databases as shown below. Tick the boxes for multiple queries. Leave the rest of the tabs default.Could experiment later based on additional needs.

Troubleshooting Steps
If the above did not work. Verify that you have a telnet client installed on your computer.(Google this one  if needed as this is one of the setting under windows programs). Please verify that remote connection is enabled on the mysql server. To do this open a command prompt using cmd. (Open with Administrator rights). enter

c:\>telnet  x.x.x.x 3308

where the x.x.x.x is the IP address and 3308 is the default port MySQL listens too for requests. Also check any firewall settings.

You are now done with the ODBC setup.


Create a Link Service on your MSSQL

To do this open my SSMSO with sa privileges and click on Server Objects - > Linked Servers
Right click to create a new Link service.


Enter the following information. Note the data source name is the ODBC name given during driver setup. Verify the provider matches as below:

Select the security tab and enter the MYSQL user name and password.

 Server option as below


You are done.  If the setup was successful, refresh the screen F5 then expand the MYSQL object which should display the database and table names as below:


Writing a query to access the MYSQL Data. Note some fields are not compatible with MSSQL and will need a cast to convert this to a varchar type. I will explain this later in another post. For now, open a New Query window and enter the following select statement.
select * from openquery(MYSQL,'select * from schoolboxdev.chat')

There should be a result similar to the following



Your Link server is up and running. See My next post on doing insert, update and delete statements.


Comments

Popular posts from this blog

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using the Premium edition)   Right click on your project and add a service reference to the above web serv

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it. First things first you will need a merchant account with ANZ who will give you the following ·          Merchant Account ·          User name ·          password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details. Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide. What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps The site has numerous

Read any Json data into a table structure using Dynamic SQL and save time write long queries.

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works: First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this: sql /****** Object: Table [dbo].[tbljson] Script Date: 29/03/2023 10:37:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[#tbljson]( [ID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [form_id] [ int ] NOT NULL , [params] [nvarchar](max) NULL , CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ] GO Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script: insert into #tbljson s