Hi, I have a SSRS report using stored procedure, and it works fine in local machine, but when I upload .rdl file to report manager, it gives error message : An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'DataSet1'. For more information about this error navigate to the report server on the local server machine, or enable remote errors If I use SQL Text instead of stored procedure, and upload .rdl file to report manager, it works. Am a bit lost, could anybody help? Much appreciated ! Thank you, Ling

Check out connection to the database, it is possible that you have stored procedure under SQL command type Text ???Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Ling, Could you post script of the SQL Text and the stored proceduce here? Look into the error log to see if there are more detailed message. thanks, Jerry

Hi all, Thank you for your help, I checked connection to the DB, it is good - plain text SQL command works. But if I use stored procedure, works fine in local machine, not in report manager. Here comes plain text: declare @StartDate as nvarchar(30) declare @EndDate as nvarchar(30) set @EndDate=convert(nvarchar(30), getdate(), 112) if @rptFrequency='Daily' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -1, getdate()), 112) end else if @rptFrequency='Weekly' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -7, getdate()), 112) end else if @rptFrequency='Monthly' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -30, getdate()), 112) end else begin print 'please provide report frequency' end declare @dataTable as varchar(50) if @rptSub = 'Sub1' begin set @dataTable='Sub1_table' end else if @rptSub = 'Sub2' begin set @dataTable='Sub2_table' end else if @rptSub = 'Sub3' begin set @dataTable='Sub3_table' end else if @rptSub = 'Sub4' begin set @dataTable='Sub4_table' end else begin print 'please provide sub report name' end declare @sqlString as nvarchar(3000) set @sqlString = 'Select ORA.Server, ORA.DateTime, ORA.Service_Name, Ora_Total_Count, case when Ora_Total_Count<>0 then Total_Res_Tot_Time/Ora_Total_Count else null end as Ora_Res_Avg_Time, SAP_Total_Count, case when SAP_Total_Count<>0 then SAP_Total_Time/SAP_Total_Count else null end as SAP_Avg_Time, MF_Total_count, case when MF_Total_count<>0 then MF_Total_Time/MF_Total_count else null end as MF_Avg_Time From ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(Oracle_Time) as Total_Res_Tot_Time, Sum(Oracle_Resolved) as Ora_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and Oracle_resolved > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) ORA LEFT OUTER JOIN ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(SAP_Time) as SAP_Total_Time, SUM(SAP_resolved) as SAP_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and SAP_calls > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) SAP ON ORA.DateTime = SAP.DateTime and ORA.Service_Name = SAP.Service_Name LEFT OUTER JOIN ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(Mainframe_Time) as MF_Total_Time, SUM(Mainframe_REsolved) as MF_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and Mainframe_calls > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) MF ON ORA.DateTime = MF.DateTime and ORA.Service_Name = MF.Service_Name ' --print @sqlString exec(@sqlString) STORED PROCEDURE is as below: USE [DB_xxx] GO /****** Object: StoredProcedure [dbo].[usp_xxxxx] Script Date: 11/18/2010 14:25:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /*exec [dbo].[usp_xxxxx] 'Weekly', 'Sub1' */ ALTER procedure [dbo].[usp_xxxxx] ( @rptFrequency nvarchar(10), @rptSub nvarchar(20) ) as begin set nocount on declare @StartDate as nvarchar(30) declare @EndDate as nvarchar(30) set @EndDate=convert(nvarchar(30), getdate(), 112) if @rptFrequency='Daily' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -1, getdate()), 112) end else if @rptFrequency='Weekly' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -7, getdate()), 112) end else if @rptFrequency='Monthly' begin set @StartDate=CONVERT(nvarchar(30), DATEADD(dd, -30, getdate()), 112) end else begin print 'please provide report frequency' end declare @dataTable as varchar(50) if @rptSub = 'Sub1' begin set @dataTable=Sub1_table' end else if @rptSub = 'Sub2' begin set @dataTable='Sub2_table' end else if @rptSub = 'Sub3' begin set @dataTable='Sub3_table' end else if @rptSub = 'Sub4' begin set @dataTable='Sub4_table' end else begin print 'please provide sub report name' end declare @sqlString as nvarchar(3000) set @sqlString = 'Select ORA.Server, ORA.DateTime, ORA.Service_Name, Ora_Total_Count, case when Ora_Total_Count<>0 then Total_Res_Tot_Time/Ora_Total_Count else null end as Ora_Res_Avg_Time, SAP_Total_Count, case when SAP_Total_Count<>0 then SAP_Total_Time/SAP_Total_Count else null end as SAP_Avg_Time, MF_Total_count, case when MF_Total_count<>0 then MF_Total_Time/MF_Total_count else null end as MF_Avg_Time From ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(Oracle_Time) as Total_Res_Tot_Time, Sum(Oracle_Resolved) as Ora_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and Oracle_resolved > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) ORA LEFT OUTER JOIN ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(SAP_Time) as SAP_Total_Time, SUM(SAP_resolved) as SAP_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and SAP_calls > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) SAP ON ORA.DateTime = SAP.DateTime and ORA.Service_Name = SAP.Service_Name LEFT OUTER JOIN ( select [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)) as DateTime, Service_Name, Sum(Mainframe_Time) as MF_Total_Time, SUM(Mainframe_REsolved) as MF_Total_count from ' + @dataTable + ' Where DateTime > ''' + @StartDate + ''' and DateTime < ''' + @EndDate + ''' and Mainframe_calls > 0 Group by [Server], dateadd(ss, -datepart(ss, datetime), dateadd(mi, -datepart(mi, datetime), datetime)), Service_Name ) MF ON ORA.DateTime = MF.DateTime and ORA.Service_Name = MF.Service_Name ' --print @sqlString exec (@sqlString) end I just tried again, still SP does not work, plain text works, and SP is under SP, am a bit lost. Much appreciate if anybody can help. Thank you, Ling

Hi Jerry, could you please tell where to check the error log? in report manger? Thank you, Ling

Hi all, I still have not got a solution for this, anybody can help? Thank you, Ling

I am having the same issue, but mine won't work even if I do text it.

I have got an idea, I have feeling it is database permission problem, anybody has similar problem, please check.