SSRS - Linked Server SQL 2008 and MySQL

  • MarksmanWaugh

    SSC Enthusiast

    Points: 160

    Hello,

    Referencing an article very similar to this: http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql

    I setup a SQL Server 2008 linked server environment to MySQL with MySQL Connector 5.1.6 and a System DSN ODBC connection.

    - Connection works great.

    - Queries work great.

    - Setup a Stored Procedure that populates a temporary table for reporting, works great.

    - Running reports from dev environment in Visual Studio works great.

    - Deploying and executing the report from Report Manager on the Report Server returns a generic message:

    An error occurred during client rendering.

    An error has occurred during report processing.

    Query execution failed for dataset ‘spMyDataSet’.

    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Running the report from the Web Service gives a little more info:

    An error occurred during client rendering.

    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset ‘spMyDataSet’. (rsErrorExecutingCommand)

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “MYSQLPROD”.

    Any ideas? I’ve got the MySQL Connector installed and the System DSN configured on both the SQL Server and Web Server. I am using the 64 bit version of the MySQL Connector as well as the patch referenced on http://bugs.mysql.com/bug.php?id=56233. Testing the connection works fine in both places.

    The only place I have issue is trying to run a report from Reporting Services in Report Manager. Everywhere else (SSMS local, SSMS on the server, Visual Studio) all execute the PROC just fine. Permissions are set the same as all other reports I am able to execute.

    I'm banging my head now!

    Cheers for any feedback!

  • JeffRush

    SSC Eights!

    Points: 834

    Hey Michael.

    Is it possible SSRS is intalled on x64 and MySQL is on x86?

    If so, have you tried creating the DSN in using ODBC admin tool in System32 -and- SysWoW64?

  • MarksmanWaugh

    SSC Enthusiast

    Points: 160

    Great thought!

    But my God... I had failed to add the Reporting Services Browser User to the Linked Server Remote Server Login Mappings.

    In the end I still deserve a head banging. Thanks for your ear though.

  • JeffRush

    SSC Eights!

    Points: 834

    Good times 🙂

    Glad you were able to get it working.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply