kmiles (10/20/2012)
In SSRS 2008, I have a linked server setup to SugarCRM using the 64 bit Mysql ODBC connector. Openquery is not needed (and does not work) when setting up the query for the dataset. The query you define is passed directly to MySQL.Example:
SELECT userid,state FROM SugarUsersTable WHERE state = 'MD'
This works fine. What doesn't work is attempting to pass any variable from SSRS to it.
This does not work:
SELECT userid,state FROM SugarUsersTable WHERE state = '@state'
This is very frustrating and I have not been able to find an alternate solution. I need this to be available via the SSRS reports menu for our Sales staff.
What is the error you're getting? Is it returning no rows? Have you tried w/o the ticks/single quotes?
SELECT userid,state FROM SugarUsersTable WHERE state = @state
At a previous company we used SugarCRM on MySQL db and our reporting came out of SQL Server. I created a nightly job that pulled the data from the production MySQL db and populated the reporting db in SQL Server. It made reporting easier.
HTH,
Rob