CODEWITHSUNDEEP
sqlt-sqlreporting-servicesssrs-2008
Michael A
Michael A

Reputation: 9900

SQL works in SSMS but not on SSRS Report?

I have the following piece of SQL:

select 
    l.lease_id ,
    l.tenant_trading_name ,
    p.building_name ,
    l.negotiation_type ,
    l.net_area ,
    l.lease_source ,
    l.deal_approved_date ,
    l.building_id ,
    p.building_id as building_id_2
from 
    lease_deal.lease l
    inner join property.property p
        on p.building_id = l.building_id
where 
    l.deal_approved_date >= @dateFrom
    and l.deal_approved_date  <= @dateTo
    and l.lease_status = @leaseStatus

This works perfectly fine in SSMS (providing I declare the variables) however when I've added it as a dataset on a report (called dsDetail) I'm receiving the following error when the report tries to run:

An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'dsDetail'. Must declare the scalar variable "@building_id". Incorrect syntax near the keyword 'and'.

I don't understand what the error is indicating and after Googling the issue for the past half hour feel no closer to a resolution. Please help!

Upvotes: 0

Views: 2141

Answers (2)

Michael A
Michael A

Reputation: 9900

Resolved: Unsure what caused this but appears to be a strange caching issue. Completely removed the report from SSRS and re-uploaded it.

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

The queries you use differ. Since here is no such a variable - check the query used in SSRS.

Upvotes: 1

Related Questions