Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS linked server to MySQL parameter problems


SSRS linked server to MySQL parameter problems

Author
Message
kmiles
kmiles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
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.
robert.gerald.taylor
robert.gerald.taylor
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1449 Visits: 1404
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
kmiles
kmiles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
It returns no rows, with no errors, when passing the parameter. I tried it also without the quotes and nothing changed. I may have to do as you suggested and make a local SQL copy but our SugarCRM database is pretty large and I was hoping to avoid the duplication.

Thanks for input Rob.
ib.naji
ib.naji
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 164988
kmiles (10/21/2012)
It returns no rows, with no errors, when passing the parameter. I tried it also without the quotes and nothing changed. I may have to do as you suggested and make a local SQL copy but our SugarCRM database is pretty large and I was hoping to avoid the duplication.


One "work-around" I can think of is that you can build the expression for the query in SSRS dynamically (using the 'fx' button next to the expression box).

So instead of passing the parameters, you could build an expression that embeds the parameters in the query.

Would that solve your problem?

-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers.

I believe in Codd
... and Thinknook is my Chamber of Understanding
kmiles
kmiles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
That seemed like it would be a great solution but nothing is being evaluated prior to sending the query text directly to MySQL. Even typing in the expression box for the dataset query ="select..." returns a MySQL syntax error that it doesn't understand the beginning ="

This would explain why the parameters are not working becasue SSRS is not evaluation the @param, it's just sending the text "@param".

Thanks for the help, any other thoughts?
kmiles
kmiles
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
ib.naji (10/21/2012)
kmiles (10/21/2012)
It returns no rows, with no errors, when passing the parameter. I tried it also without the quotes and nothing changed. I may have to do as you suggested and make a local SQL copy but our SugarCRM database is pretty large and I was hoping to avoid the duplication.


One "work-around" I can think of is that you can build the expression for the query in SSRS dynamically (using the 'fx' button next to the expression box).

So instead of passing the parameters, you could build an expression that embeds the parameters in the query.

Would that solve your problem?



Using the filter option in the dataset worked. I was typing "@parameter" in the filter field instead of "=Parameters!State.Value". Thanks for your help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search