Geek Answers Handbook

How to create a dataset in VS for an SSRS 2008 report with MySQL data, through openquery and a linked server

I have successfully created a linked server in a SQL Server 2005 database for MySQL. I need to call / execute MySQL stored procedure from VS (update table data in MySQL) and then create a data set with this data for reporting in SSRS 2008.

I can run the following from the SSMS request window without problems:

select * from openquery(myLinkedSrvrname,'call myMySQLprocname')

However, I cannot get this statement to run from the Query Designer window in VS to create a dataset. It creates a syntax error. Can someone suggest a fix for the above openquery statement, or learn how to execute a MySQL stored procedure from Reporting Services to create a dataset?

Error code

An error occurred while executing the request. ERROR [42000] [MySQL] [ODBC 5.1 driver] [mysqld-5.5.16]. You have an error in the SQL syntax; the manual that matches your version of MySQL Server for the correct syntax to use next ('myLinkedSrvrname,' myMySQLprocname ')' on line 1

+4
source share
1 answer

You can either 1) create a connection directly from SSRS (an ODBC connection is required on the SSRS server, as well as on your dev machine) http://dev.mysql.com/downloads/connector/odbc/

or

2) you can wrap this openquery MySQL call inside a SQL Server stored procedure. This will allow you to pass the parameter from the report to the stored SQL Server process, and then to the processed MySQL. However, this requires the use of dynamic SQL inside the SQL Server process.

If you go to the first option, you can call Stored Proc right away, but I just did it with an Oracle connection that is not ODBC-related, so SSRS can disable this option.

Using the second option, you may encounter additional problems when authenticating using two hosts, depending on which server operating systems you are using. Calling MySQL proc from SSMS is only 1 xp, so this problem did not occur.

Edit: Apparently, you can add the ADO.NET MySQL connector to SSRS: http://www.mazsoft.com/blog/post/2010/11/04/Using-SQL-Server-2008-Reporting-Services- (SSRS ) -c-mysql.aspx

It sounds complex, but if you do a lot of MySQL, it will be better than using ODBC.

+1
source

More articles:


All Articles