Archive

Archive for May, 2009

Using SQL Reporting Services to report on MySQL database

So the other day I was asked to produce a phone book from a MySQL database.  I had already written a SQL Reporting Service (from now on referred to as RS) report in the format that they wanted.  I thought it would be a simple task to just point the RS at the MySQL and be done.  How wrong could I be?  After much searching and a few sneaky tricks I succeeded.   If this doesn’t interest you then stop now… I’m only going to get geeker.  I just want to be able to find this info later.

Problem… SQL Express RS only allows connections to the Local SQL server.  Upgrading SQL isn’t an option because 1. RS only talks to MS SQL servers and 2. Cost.

Solution… Linked Servers!

Linked servers are an advanced SQL feature, that in 10 years of software development have yet to use in a production environment or create a system requiring them.  However they are available in all versions of SQL server from Express all the way through to Data Centre edition.

So here is my solution with thanks to Niklas Henricson and his article on Migrating MySQL to MSSQL.

Step 1: Install MySQL ODBC client on the SQL server.

Any version of the MySQL ODBC Connector should work, but I choose the latest (v5.2) available from http://dev.mysql.com/downloads/connector/

You may want to set up a SYSTEM DSN at this point to check that it is working correctly and you can access the MySQL database, or at least maybe install one of the MySQL clients and make sure you can connect.

Step 2: Cerate a MS SQL Link to the MySQL database.

Open a query window and run the following SQL statement.

EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL',
@srvproduct=N'MySQL',
@provier=N'MSDASQL',
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=tigerdb; USER=root; PASSWORD=hejsan; OPTION=3'
 

Obviously substitute your connection information.  If you aren’t sure what it is then you can always create a .UDL file on your desktop and setup your connection and then open it in notepad to find out.

If you refresh the object explorer you will now see that the Linked server appear.  You can right click on this and set additional properties, like setting up MSSQL to MySQL user mappings.  In my case all I did was set it to always use the same credentials when connecting to MySQL and set them to a read only reporting user I had already created.

Step 3: Querying the database.

This is simple.  Basically you select from an openquery() like this…

SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM tigerdb.shoutbox')

But of course the Select statement in the openquery can be quite complex.

Step 4: Optimise

Building a phone list can be quite complex, if you have to match people into family groups and then provide the phone list based on those families.  To make the queries a little simpler I could have built a View within a database on the MSSQL side, but instead I just used a nested select statement.  It appeared to work just as well.

In so doing I found the following:

  1. The query performed faster if I kept the query on the MySQL side to a simple select and then joined on the MSSQL side.
  2. Only return the data you need.  If there are other things in the table that you don’t need, don’t return them.
  3. Return the data in the same sequence, to make matching/sorting quicker on the MSSQL side.

And that is it, the solution is so simple it is brilliant, at least IMHO.

Advertisements
Categories: Programming