locked
Different results with ssrs and sqlserver for same query RRS feed

  • Question

  • User533496661 posted

    Hi,

    I am executing the same query with parameters in sqlserver and ssrs, they were returning different results.

    I think the problem is with the parameters but i couldnt trace where the actual error is taking place.

    If anyone knows on how to solve this issue, please do give some solution to this.

     

    Thanks!

    Thursday, March 22, 2012 8:36 AM

Answers

  • User2105670541 posted

    to check what u are passing in SSRS is just drag that parameter to the report body, now run the report. Now you will have the idea what you are passing in the report and what you are passing in sql query. this will help you out.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 22, 2012 11:09 AM

All replies

  • User1849990362 posted

    You can trace parameter values passed by ssrs in sql server using "Profiler" and compare that with your sql query parameter.

    here are some links

    http://msdn.microsoft.com/en-us/library/ff650699.aspx

    http://www.sqlservercentral.com/articles/SQL+Trace/71839/

    http://stackoverflow.com/questions/357283/sql-server-profile-view-parameter-values

    Thursday, March 22, 2012 9:08 AM
  • User533496661 posted

    Have you ever faced the similar problem with reports?

    (
    (@datetype = 'all' AND SCHEDULE_ASSESSMENT.SEQ_NUMB IS NOT NULL) OR
    (@datetype = 'auddate' AND SCHEDULE_ASSESSMENT.SEQ_NUMB IS NULL AND AUDIT_DATE IS NOT NULL) OR
    (@datetype = 'scheddate' AND SCHEDULE_ASSESSMENT.SEQ_NUMB IS NOT NULL AND (Membership_Vote <> 'Accepted' OR Membership_Vote is null)) OR
    (@datetype = 'compapprove' AND SCHEDULE_ASSESSMENT.SEQ_NUMB IS NOT NULL AND Membership_Vote = 'Accepted')
    )
    This was the where constrain. In the sqlserver i am declaring datetype as varchar(20) and just passing the value.
    In the report i am passing the same value as text, but the results are differing the conditions are evaluating to true for all the rows.
     

     

    Thursday, March 22, 2012 9:18 AM
  • User1849990362 posted

    Trace the sql query generated on Sql Profiler and you will get the difference.

    Thursday, March 22, 2012 9:27 AM
  • User533496661 posted

    ok, i will try that. But in mean while when i tried to compare the queries in ssrs query builder i passed the value of @datetype = 'all'

    but @datetype = 'auddate' was evaluating to true.

    Thursday, March 22, 2012 10:08 AM
  • User2105670541 posted

    to check what u are passing in SSRS is just drag that parameter to the report body, now run the report. Now you will have the idea what you are passing in the report and what you are passing in sql query. this will help you out.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 22, 2012 11:09 AM