Query execution failed for dataset 'DataSet1'

Dear SSRS Masters,

I have a very basic report that I can't seem to get to run. The SQL excecutes on the server and within the query builder of the report. Here is the query:

select v.VEND_ID
, v.vend_notes
, v.vend_name as VEND_NAME
, v.vend_name_ext, l.Plat_Vendorkey
, a.LN_1_ADR + ' '+a.LN_2_ADR as Address
, a.City_Name
, a.Mail_State_DC
, a.Postal_CD

from WEBAPP_CP.DELTEK.V_VEND v
left join CP_Migration.dbo.L_CP_Plat_Vendorkey l
on v.VEND_ID = l.CP_Vendorkey
join WEBAPP_CP.DELTEK.V_VEND_ADDR a
on a.vend_id = v.vend_id
where VEND_NAME_EXT like @Site+'%'
order by VEND_NAME_EXT

If I run the query within SQLServer report builder and enter a parameter, it will pull lots of records. However when i go to run the actual report, I get the error above. ANy help is always appreciated. Thanks

when you run the actual report, are you prompted for the variable @Site ?? Also, does it have a default value?

Hi, yes, I am prompted for the variable. When I put in variable, I get that error. If I declare the variable in the query and run it in SQL, it pulls data correctly. There is no default value assigned. Let me know if this makes sense.

at this point, I would use sql server profiler to see the actual queries being sent by the report at run time. Something is wonky, but it's not obvious

1 Like

This turned out to be a permissions issues. The service account running the reports did not have the correct security.

that'll do it!