October 27, 2010 at 7:39 pm
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!
October 27, 2010 at 8:12 pm
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?
October 27, 2010 at 8:50 pm
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.
October 27, 2010 at 9:06 pm
Good times 🙂
Glad you were able to get it working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply