Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 29th, 2006, 10:42 PM
Registered User
Join Date: May 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Microsoft Reporting Services and mySQL

    Due to a lack of a powerful open source reporting solution, I'm trying to get MS SQL server 2005 reporting services to work with MySQL database. from the datasource dialogue, I've tried to use the following connection string

"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER= myUsername;PASSWORD=myPassword;OPTION=3;"

to try to connect to my local mysql database but this gives me an error. I've installed the my ODBC drivers from mySQL website. Is there anything I must do in order to register the .dll files which comes with my ODBC with the reporting services? Is is possible that I can use the mySQL connector.net with reporting services instead of ODBC due to performance concersn?

Has someone done this and could you tell me the exact steps I need to take in order to make this work. Thanks for your attention

Old December 13th, 2006, 01:58 PM
Registered User
Join Date: Dec 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

I've spent a few days figuring this out from a position of general cluelessness and I'm pleased to say that I've got it all working perfectly!

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 driver.

The most important thing to note is that if you use the ODBC driver 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, 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

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

There are three sections to the code above. The first sets up the linked server. Obviously, replace [DB_NAME], [HOSTNAME], [USER] and [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 created a new View in this database for every table in my MySQL database. The views are what are then accessed by Reporting Services. To create a view I used the following SQL code in the 'create view' window:


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.

By using 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 I MySQL never put 0000-00-00 in any date field.

Old April 23rd, 2009, 11:38 AM
Registered User
Join Date: Apr 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server express edition to access MYSQL database

Thanks a lot for such a nice article, this article helped me a lot.
I am using SSRS that comes with SQL Server express edition to access database hosted on MYSQL. Do u have any idea that is there any specific license requirement for this?
Old May 23rd, 2013, 04:03 AM
Registered User
Join Date: May 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SSRS with mysql connector

i think you can install mysql connector ,after installing mysql connector you can able to add new data source in reporting server ,

Open SQL server Reporting server Web ,click New Data Source ,on data source type you can select "MYSQL" on connection string column you can add

SERVER=mysql server;DATABASE=database name;USER=username;PASSWORD=password;
Old June 14th, 2018, 02:22 AM
Registered User
Join Date: Jun 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

thx @sykesr you help me a lot

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reporting Services and MySQL -- please help? MotorolanSummer SQL Server 2005 0 July 17th, 2006 04:13 PM
Reporting Services with MySQL exkgb SQL Server 2005 0 May 29th, 2006 10:39 PM
New to Reporting Services, please help cmreis BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 December 30th, 2005 02:07 AM
Reporting Services Ben SQL Server 2000 3 February 8th, 2005 10:16 AM
Need your help with Microsoft Reporting Services rattanan BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 September 19th, 2004 01:40 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.