Re: How to connect Reporting Services to MySQL Databases

[Robyn Farley]

I use the MySQL ODBC 3.5 connector to create a linked server to connect to my MySQL servers. I then created views for the not only the tables in MySQL but also created views using join statements on the tables from MySQL. Once you have views created you can then use these views in place of tables when running queries or in creating more views with more complex join statements.

Here is one query string for joining multiple linked server tables using aliases in a view that then can be used in reporting or most other integrations.

SELECT A.FIELD1, U.FIELD2, A.FIELD3, '00' AS FIELD4
FROM OPENQUERY(LINKEDSERVER, 'select * from TBL1') AS U LEFT OUTER JOIN
OPENQUERY(LINKEDSERVER, 'select * from TBL1') AS A ON
U.id_field = A.id_field


I hope this helps....