I am using SSRS 2008 and am trying to add the following dataset to my report. However, whether I add this as a stored procedure or text, it does not display any fields after adding it. And there are no error messages. What could the workarounds and causes be? I have 6 report parameters (in this order): @END_YEAR VARCHAR(10), @END_MONTH VARCHAR(10), @END_WEEK VARCHAR(10), @TheTYPE VARCHAR(MAX), @IssueType VARCHAR(MAX), @Manager VARCHAR(MAX) I did make sure these parameters were linked up properly to my stored proc. I was able to add other datasets of mine which were not dynamic SQL for this same report. And my stored procedure is: ALTER PROCEDURE ActionMetricsPageData --DECLARE @END_YEAR VARCHAR(10), @END_MONTH VARCHAR(10), @END_WEEK VARCHAR(10), @TheTYPE VARCHAR(MAX), @IssueType VARCHAR(MAX), @Manager VARCHAR(MAX) --SET @TheTYPE = 'All' --SET @IssueType = '1' --SET @Manager = 'All' AS --ActionMetricsPageData '2011', '3', '0', 'All', '1', 'All' DECLARE @MainSQL VARCHAR(MAX) DECLARE @START_DATE DATETIME DECLARE @END_DATE DATETIME IF @END_WEEK = '0' AND @END_MONTH = '12' BEGIN SET @START_DATE = CAST(('1/1/' + @END_YEAR) AS DATETIME) SET @END_YEAR = CAST((CAST(@END_YEAR AS INT) + 1) AS VARCHAR(10)) SET @END_DATE = CAST(('1/1/' + (@END_YEAR)) AS DATETIME) END IF @END_WEEK = '0' AND @END_MONTH <> '12' BEGIN SET @END_MONTH = CAST((CAST(@END_MONTH AS INT) + 1) AS VARCHAR(10)) SET @END_DATE = CAST(((@END_MONTH) + '/1/' + @END_YEAR) AS DATETIME) END IF @END_WEEK <> '0' BEGIN SET @START_DATE = DATEADD(wk, @END_WEEK - 1, DATEADD(dd, -1, DATEADD(wk, DATEDIFF(wk,0,Convert(varchar(4), @END_YEAR) + '-01-01'), 0))) SET @END_DATE = DATEADD(DAY, 7, @START_DATE) END IF @TheTYPE = 'All' SET @TheTYPE = ' (IAT_PLAN_TYPE = ''Correction'' OR IAT_PLAN_TYPE = ''Corrective'' OR IAT_PLAN_TYPE = ''Preventive'' OR IAT_PLAN_TYPE = ''QSI - Corrective'' OR IAT_PLAN_TYPE = ''QSI - Correction'' OR IAT_PLAN_TYPE = ''QSI - Preventive'' ) AND SGD_SIGNOFF_DATE >= ' + @START_DATE + ' AND SGD_SIGNOFF_DATE < ' + @END_DATE ELSE IF @TheTYPE = 'Corrective and Preventive' SET @TheTYPE = ' (IAT_PLAN_TYPE = ''Corrective'' OR IAT_PLAN_TYPE = ''Preventive'') AND SGD_SIGNOFF_DATE >= ' + @START_DATE + ' AND SGD_SIGNOFF_DATE < ' + @END_DATE ELSE IF @TheTYPE = 'All QSI' SET @TheTYPE = ' (IAT_PLAN_TYPE = ''QSI - Corrective'' OR IAT_PLAN_TYPE = ''QSI - Correction'' OR IAT_PLAN_TYPE = ''QSI - Preventive'') AND SGD_SIGNOFF_DATE >= ' + @START_DATE + ' AND SGD_SIGNOFF_DATE < ' + @END_DATE ELSE SET @TheTYPE = ' (IAT_PLAN_TYPE = ' + @TheTYPE + ') AND SGD_SIGNOFF_DATE >= ' + @START_DATE + ' AND SGD_SIGNOFF_DATE < ' + @END_DATE IF @IssueType = '1' SET @IssueType = ' (QEI_ITY_NAME = ''Correction (8.1)'' OR QEI_ITY_NAME = ''Corrective Action/Preventive Action'' OR QEI_ITY_NAME = ''Corrective | Preventive Action (8.1)'' OR QEI_ITY_NAME = ''Investigation (8.1)'' OR QEI_ITY_NAME = ''Root Cause Investigation (8.1)'' OR QEI_ITY_NAME = ''Audit'' OR QEI_ITY_NAME = ''Supplier Corrective | Preventive Action (8.1)'' OR QEI_ITY_NAME = ''PXP_PROCESS_QXP'' OR QEI_ITY_NAME = ''PXP_PRODUCT_QXP'') ' ELSE IF @IssueType = '2' SET @IssueType = ' (QEI_ITY_NAME = ''Audit'') ' ELSE IF @IssueType = '3' SET @IssueType = ' (QEI_ITY_NAME = ''Correction (8.1)'' OR QEI_ITY_NAME = ''Corrective Action/Preventive Action'' OR QEI_ITY_NAME = ''Corrective | Preventive Action (8.1)'' OR QEI_ITY_NAME = ''Investigation (8.1)'' OR QEI_ITY_NAME = ''Root Cause Investigation (8.1)'') ' ELSE IF @IssueType = '4' SET @IssueType = ' (QEI_ITY_NAME = ''PXP_PROCESS_QXP'' OR QEI_ITY_NAME = ''PXP_PRODUCT_QXP'') ' ELSE IF @IssueType = '5' SET @IssueType = ' (QEI_ITY_NAME = ''Supplier Corrective | Preventive Action (8.1)'') ' IF @Manager = 'All' SET @Manager = ' <> ''All'' ' ELSE SET @Manager = '= '' REPLACE(''' + @Manager + ''','','''') ' --SELECT @Manager SET @MainSQL = 'SELECT DISTINCT IAT_ID, DOC_DOCUMENT_NO, SGD_SIGNOFF_DATE, BTK_NAME, BTK_CLOSED_DATE, TSK_ANT_COMPL_DATE, IAT_PLAN_TYPE, ACT_MANAGER_NAME, QEI_ITY_NAME FROM (SELECT SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.IAT_ID, SMARTSOLVE.V_QEI_QEX_ISSUE.DOC_DOCUMENT_NO, CASE VH_QEI_QEX_ISSUE.QEI_ITY_NAME WHEN ''Audit'' THEN (SELECT TOP (1) SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SIGNOFF_DATE FROM SMARTSOLVE.V_RVW_REVIEW_TSK INNER JOIN SMARTSOLVE.V_SGD_SIG_DETAIL_TSK ON SMARTSOLVE.V_RVW_REVIEW_TSK.RVW_SGN_id = SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SGN_id WHERE (SMARTSOLVE.V_RVW_REVIEW_TSK.RVW_id = SMARTSOLVE.V_IMT_IMPLEMENT_TSK.STK_CURRENT_REV_id) ORDER BY SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SIGNOFF_DATE DESC) ELSE [PROC].FINAL_APPROVAL.SGD_SIGNOFF_DATE END AS SGD_SIGNOFF_DATE, SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.BTK_NAME, SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.BTK_CLOSED_DATE, SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.TSK_ANT_COMPL_DATE, SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.IAT_PLAN_TYPE, SMARTSOLVE.V_PSN_PERSON.ACT_MANAGER_NAME, SMARTSOLVE.VH_QEI_QEX_ISSUE.QEI_ITY_NAME FROM SMARTSOLVE.VH_QEI_QEX_ISSUE INNER JOIN SMARTSOLVE.V_QEI_QEX_ISSUE ON SMARTSOLVE.VH_QEI_QEX_ISSUE.QEI_id = SMARTSOLVE.V_QEI_QEX_ISSUE.QEI_id INNER JOIN SMARTSOLVE.V_IMT_IMPLEMENT_TSK ON SMARTSOLVE.V_QEI_QEX_ISSUE.QEI_id = SMARTSOLVE.V_IMT_IMPLEMENT_TSK.BTK_DOC_id LEFT OUTER JOIN [PROC].FINAL_APPROVAL ON SMARTSOLVE.V_IMT_IMPLEMENT_TSK.STK_CURRENT_REV_id = [PROC].FINAL_APPROVAL.RVW_id INNER JOIN SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK ON SMARTSOLVE.V_IMT_IMPLEMENT_TSK.IMT_id = SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.BTK_PARENT_id LEFT OUTER JOIN SMARTSOLVE.V_PSN_PERSON ON SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.BTK_OWNER_id = SMARTSOLVE.V_PSN_PERSON.ACT_id WHERE (SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.BTK_STATUS <> ''ABR'') AND (CASE VH_QEI_QEX_ISSUE.QEI_ITY_NAME WHEN ''Audit'' THEN (SELECT TOP (1) SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SIGNOFF_DATE FROM SMARTSOLVE.V_RVW_REVIEW_TSK INNER JOIN SMARTSOLVE.V_SGD_SIG_DETAIL_TSK ON SMARTSOLVE.V_RVW_REVIEW_TSK.RVW_SGN_id = SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SGN_id WHERE (SMARTSOLVE.V_RVW_REVIEW_TSK.RVW_id = SMARTSOLVE.V_IMT_IMPLEMENT_TSK.STK_CURRENT_REV_id) ORDER BY SMARTSOLVE.V_SGD_SIG_DETAIL_TSK.SGD_SIGNOFF_DATE DESC) ELSE [PROC].FINAL_APPROVAL.SGD_SIGNOFF_DATE END IS NOT NULL) AND (SMARTSOLVE.V_IMT_IMPLEMENT_TSK.BTK_NAME <> ''PQRE and /or Action Plan'') AND (SMARTSOLVE.V_IMT_IMPLEMENT_TSK.BTK_NAME <> ''Implement Required Actions'') AND (SMARTSOLVE.V_IAT_IMPL_ACTPLAN_TASK.IAT_PLAN_TYPE <> ''Effectivity Check'') UNION SELECT SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_ID, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_EXCEPTION_NO, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_CREATED_DATE, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_XRS_DESCRIPTION, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_CLOSED_DATE, NULL, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_XRS_DESCRIPTION, SMARTSOLVE.V_PSN_PERSON.ACT_MANAGER_NAME, SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_BASE_EXCEPTION FROM SMARTSOLVE.V_QXP_ALL_EXCEPTION INNER JOIN SMARTSOLVE.V_PSN_PERSON ON SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_CLOSED_BY = SMARTSOLVE.V_PSN_PERSON.ACT_LOGONUSER WHERE (SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_XRS_DESCRIPTION = ''Correction'') AND (SMARTSOLVE.V_QXP_ALL_EXCEPTION.QXP_RESOLUTION <> ''300'') )B WHERE ' SET @MainSQL = @MainSQL + @TheTYPE + ' AND ' + @IssueType + ' AND ACT_MANAGER_NAME ' + @Manager EXEC (@MainSQL)

nevermind. I found source of this error: date parameters are encapsulated in dynamic portion of sql. I fixed this. But now I get this error: Conversion failed when converting datetime from character string. However, the signoff_date field is datetime and so is start_date and end_date. Ryan Daulton

Hi, I’m glad to hear you have figured out the original issue. For the conversion error, you may run into the problem that use parameterized query statement in dynamic SQL. Instead of embedding datetime parameters into the string, I would suggest you keep them as parameters. As an example, please take a look at the similar thread http://social.msdn.microsoft.com/Forums/en/transactsql/thread/baeac539-5015-4f5b-b18a-2026e8232b96 For more information about sp_executesql, please refer to http://msdn.microsoft.com/en-us/library/ms188001.aspx Thanks, Tony Chain Tony Chain [MSFT CSG] | Microsoft Community Support Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.