SQLTeam.com | Weblogs | Forums

Query execution failed for dataset 'DataSet1'


#1

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


#2

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


#3

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.


#4

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


#5

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


#6

that'll do it!