MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Microsoft Reporting Services
Posted by: Rob Sykes
Date: December 13, 2006 11:21AM

This is how I've connected Reporting Services to MySQL. I hope it is useful!

For reference I'm using SQL Server 2005 with MySQL 5 but it also works with MySQL 4.1. It is all sitting on Windows Server 2003 (32 bit). I had HUGE problems with 64 bit Windows and simply gave up trying! Also, this solution is based on the 3.51 ODBC drivers.

The most important thing to note is that if you use the ODBC drivers directly within SSRS to create a dataset, then several basic features of SSRS won't work. You won't be able to use parameters, for example.

Instead, you should create a linked server in SQL Server that connects to MySQL and use this linked server in Reporting Services. That way, you should have full functionality of Reporting Services.

To create the linked server, use the following SQL code in SQL Server Management Studio:


-- Add Linked Server
EXEC sp_addlinkedserver 'mysqlDB', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=[DB_NAME];SERVER=[HOSTNAME];uid=[USER];pwd=[PASSWWORD]'


-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'mysqlDB',
@useself = 'TRUE',
@locallogin = NULL
GO

-- List the tables on the linked server
EXEC sp_tables_ex 'mysqlDB'
GO


There are three sections to the code above. The first sets up the linked server. Obviously, replace [DB_NAME], [HOSTNAME], [USER], [PASSWORD] with your own MySQL connection settings. The second part of the code sorts out user access (I think!) and the thrid part tests the connection by listing all the tables in your database. If you see the tables, your linked server is working.

What I did next was to create a new SQL Server database (I gave it the same name as my original MySQL database just for clarity!) and create a new view in this database for every table in my MySQL database. The view are what are then accessed by Reporting Services. To create a view I used the following SQL code in the create view window:

SELECT *
FROM OPENQUERY(mysqlDB, 'SELECT * FROM [TABLE_NAME]') AS derivedtbl_1

Replace [TABLE_NAME] with the name of your table!

NB 'mysqlDB' is the name of the linked server created above. If you used a different name for the linked server, use a different name in the query above.

Repeat this for all your MySQL tables and you then have a SQL Server database that reads your MySQL database.

Use this database and these views to create Reporting Services reports and you will have full Reporting Services functionality.

One note on dates. Sadly, the 0000-00-00 blank date in MySQL causes all of this to fail miserably. NULL dates are fine so I had a bit of work to do to ensure that MySQL never put 0000-00-00 in any date field.



Edited 2 time(s). Last edit at 12/13/2006 11:25AM by Rob Sykes.

Options: ReplyQuote


Subject
Written By
Posted
January 27, 2005 04:45PM
Re: Microsoft Reporting Services
December 13, 2006 11:21AM
November 07, 2007 10:25AM
November 07, 2007 12:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.