rsMissingFieldInDataSet

Question

I am modifying a report written by a previous employee. After making minor adjustments, I am not returning information from some columns that previously did. When I run view the results of the store procedure in the dataset, I get results. When I use the use Preview, it does not return data and I receive the error:

Warning     [rsMissingFieldInDataSet] The dataset ‘DataSet1’ contains a definition for the Field ‘Current_Status’. This field is missing from the returned result set from the data source. Report.rdl

and

Warning     [rsErrorReadingDataSetField] The dataset ‘DataSet1’ contains a definition for the Field ‘Current_Status’. The data extension returned an error during reading the field. There is no data for the field at position 5.      c:\users\jzayak9853\documents\visual studio 2015\Projects\TM_Report_v2\TM_Report_v2\TM_Report_v2.rdl

I have rebuilt the solution, checked to make sure the fields exist in the SP, and have combed SO but I haven't been able to find a solution.

The stored procedure:

USE [coll18_prod_sql]
GO
/****** Object:  StoredProcedure [dbo].[sp_Y16_TM_Report_WITH_HRS2_PREF_ADDRESS]    Script Date: 6/25/2018 9:21:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      
-- Create date:  12/15/16
-- Description: display Student information for entered term/year
-- =============================================
ALTER PROCEDURE  [dbo].[sp_Y16_TM_Report_WITH_HRS2_PREF_ADDRESS]
    @Term varchar (6),
    @Year varchar (4)
AS
BEGIN
   IF 1 = 2
     BEGIN
              SELECT CAST('APPLICATIONS.APPL_START_TERM' AS NVARCHAR(20)) AS [APPL_START_TERM], 
              CAST('APPLICATIONS.APPL_APPLICANT' AS NVARCHAR(20)) AS [APPL_APPLICANT],
              CAST('PERSON.LAST_NAME' AS NVARCHAR(20)) AS [LAST_NAME],
              CAST('PERSON.FIRST_NAME' AS NVARCHAR(20)) AS [FIRST_NAME],
              CAST('Y16_Appl_Statuses_Pos1_View.APPL_STATUS' AS VARCHAR(5)) AS [Current_Status],
              CAST('Y16_Appl_Statuses_Pos1_View.APPL_STATUS_DATE' AS DATETIME) AS [Current_Status_Date],
              CAST('+@ApplTableName+.APPL_STATUS' AS NVARCHAR(26)) AS [Application Status],
              CAST('+@ApplTableName+.[Latest AP Date]' AS DATETIME) AS [Application Status Date],
              CAST('APPLICATIONS.APPL_ADMIT_STATUS' AS NVARCHAR(26)) AS [APPL_ADMIT_STATUS],
              CAST('APPLICATIONS.APPL_ACAD_PROGRAM' AS NVARCHAR(26)) AS [APPL_ACAD_PROGRAM],
              CAST('Y16_Student_Active_Program_View.STUDENT_PROGRAM' AS NVARCHAR(26)) AS [STUDENT_PROGRAM],       
              CAST('Y16_Student_Active_Program_View.STPR_ADMIT_STATUS' AS NVARCHAR(20)) AS [STPR_ADMIT_STATUS],
              CAST('Y16_Student_Active_Program_View.PROG_STATUS' AS NVARCHAR(20)) AS [PROG_STATUS],
              CAST('Y16_Student_Active_Program_View.PROG_STATUS_DATE' AS DATETIME) AS [PROG_STATUS_DATE],
              CAST('+@ApplMSTableName+.APPL_STATUS' AS NVARCHAR(20)) AS [MS or CM Status],
              CAST('+@ApplMSTableName+.APPL_STATUS_DATE' AS DATETIME) AS [MS or CM Status Date],
              CAST('+@FAFSATableName+.IFAF_ISIR_TYPE AS ISIR' AS NVARCHAR(20)) AS [ISIR],
              CAST('+@FAFSATableName+.ISIR_FAFSA_ADDDATE' AS DATETIME) AS [ISIR_FAFSA_ADDDATE],
              CAST('+@CreditTableName+.Term_Credit_Hours' AS NVARCHAR(20)) AS [Term_Cr_Hrs],
              CAST('PERSON_1.LAST_NAME AS Institution' AS NVARCHAR(20)) AS [Institution],
              CAST('INSTITUTIONS_ATTEND_VIEW.INSTA_EXT_GPA' AS NVARCHAR(20)) AS [INSTITUTION_GPA],
              CAST('INSTITUTIONS_ATTEND_VIEW.INSTA_GRAD_TYPE' AS NVARCHAR(20)) AS [INSTA_GRAD_TYPE],
              CAST('DATES_ATTENDED.INSTA_END_DATES' AS DATETIME) AS [INSTA_END_DATES],
              CAST('TRANSCRIPT.INSTA_TRANSCRIPT_TYPE' AS NVARCHAR(20)) AS [INSTA_TRANSCRIPT_TYPE],
              CAST('TRANSCRIPT.INSTA_TRANSCRIPT_DATE' AS DATETIME) AS [INSTA_TRANSCRIPT_DATE],
              CAST('TRANSCRIPT.INSTA_TRANSCRIPT_STATUS' AS NVARCHAR(20)) AS [INSTA_TRANSCRIPT_STATUS],
              CAST('PERSON.BIRTH_DATE' AS DATE) AS [BIRTH_DATE],
              CAST('PERSON_LS.PER_ETHNICS' AS NVARCHAR(20)) AS [PER_ETHNICS],
              CAST('PERSON.CITIZENSHIP' AS NVARCHAR(20)) AS [CITIZENSHIP],
              CAST('Y16_PERSON_PREFERRED_ADDRESS_VIEW.ADDRESS_LINE_1' AS NVARCHAR(20)) AS [ADDRESS_LINE_1],
              CAST('Y16_PERSON_PREFERRED_ADDRESS_VIEW.CITY' AS NVARCHAR(20)) AS [CITY],
              CAST('Y16_PERSON_PREFERRED_ADDRESS_VIEW.STATE' AS NVARCHAR(20)) AS [STATE],
              CAST('Y16_PERSON_PREFERRED_ADDRESS_VIEW.ZIP' AS NVARCHAR(20)) AS [ZIP],
              CAST('Y16_PERSON_CONTACT_INFO_VIEW.HOME' AS NVARCHAR(20)) AS [HOME],
              CAST('Y16_PERSON_CONTACT_INFO_VIEW.OTHER' AS NVARCHAR(20)) AS [Other],
              CAST('Y16_PERSON_CONTACT_INFO_VIEW.INT_EMAIL' AS NVARCHAR(128)) AS [INT_EMAIL],
              CAST('Y16_PERSON_CONTACT_INFO_VIEW.YTC_EMAIL' AS NVARCHAR(128)) AS [YTC_EMAIL],
              CAST('+@FirstRegTableName+.STC_REPORTING_TERM' AS NVARCHAR(128)) AS [First Reg Term],
              CAST('+@FirstRegTableName+.[First Reg Date for Term]' AS DATETIME) AS [First_Reg_Date_for_Term],
              CAST('Y16_Student_First_Credit_Term_View.TERM_REPORTING_TERM' AS NVARCHAR(128)) AS [First YTC Credit Term],
              CAST('Y16_Student_First_Credit_Term_View.FIRST_COURSE_REG_DATE' AS DATETIME) AS [First YTC Credit Reg. Date],
              CAST('Y16_Student_Term_Credit_Hours_View.Term_Credit_Hours' AS NVARCHAR(128)) AS [First Credit Term Hours],
              CAST('Y16_Student_Term_Credit_Hours_View.Term_Credits_Completed' AS NVARCHAR(128)) AS [First Term Credits Completed],
              CAST('SUM(Y16_Student_Transfer_Hours.Term_Credit_Hours)' AS NVARCHAR(128)) AS [Transfer Hours]
     END;    
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @ApplTableName varchar(26)
DECLARE @FAFSATableName  varchar(24)
DECLARE @ApplMSTableName varchar (31)
DECLARE @CreditTableName varchar (42)
DECLARE @TATableName varchar (16)
DECLARE @FirstRegTableName varchar(31)
DECLARE @sql nvarchar(max)
DECLARE @ETerm varchar(7)
DECLARE @UTERM varchar (6)
SET @UTERM = 
    (Select UPPER(@TERM))
SET @ETERM =
    (Select Substring(@UTerm,5,2) + 'R' + Substring(@UTerm,1,4))
SET @ApplTableName =
    (Select 'Y16_Applicants_' + @UTerm + '_View')
SET @FAFSATableName =
    (Select 'Y16_ISIR_FAFSA_' + @Year + '_View')
SET @ApplMSTableName =
    (Select 'Y16_Applications_MS_' + @UTerm + '_View')
SET @CreditTableName =
    (Select 'Y16_Student_Term_Credit_Hours_' +@ETerm + '_View')
SET @TATableName =
    (Select 'Y16_TA_' + @Year + '_View')
SET @FirstRegTableName =
    (Select 'Y16_First_Reg_Date_' + @ETerm + '_View')



SET @sql = CAST('' as nVarChar(MAX)) 
+'SELECT DISTINCT 
               APPLICATIONS.APPL_START_TERM, APPLICATIONS.APPL_APPLICANT, PERSON.LAST_NAME, PERSON.FIRST_NAME, 
               Y16_Appl_Statuses_Pos1_View.APPL_STATUS AS Current Status, Y16_Appl_Statuses_Pos1_View.APPL_STATUS_DATE AS Current Status Date, 
               '+@ApplTableName+'.APPL_STATUS AS [Application Status], '+@ApplTableName+'.[Latest AP Date] AS [Application Status Date], 
               APPLICATIONS.APPL_ADMIT_STATUS, APPLICATIONS.APPL_ACAD_PROGRAM, Y16_Student_Active_Program_View.STUDENT_PROGRAM, 
               Y16_Student_Active_Program_View.STPR_ADMIT_STATUS, Y16_Student_Active_Program_View.PROG_STATUS, 
               Y16_Student_Active_Program_View.PROG_STATUS_DATE, '+@ApplMSTableName+'.APPL_STATUS AS [MS or CM Status], 
               '+@ApplMSTableName+'.APPL_STATUS_DATE AS [MS or CM Status Date], '+@FAFSATableName+'.IFAF_ISIR_TYPE AS ISIR, 
               '+@FAFSATableName+'.ISIR_FAFSA_ADDDATE, '+@CreditTableName+'.Term_Credit_Hours AS [Term Cr Hrs], 
               PERSON_1.LAST_NAME AS Institution, INSTITUTIONS_ATTEND_VIEW.INSTA_EXT_GPA AS [INSTITUTION GPA], 
               INSTITUTIONS_ATTEND_VIEW.INSTA_GRAD_TYPE, DATES_ATTENDED.INSTA_END_DATES, TRANSCRIPT.INSTA_TRANSCRIPT_TYPE, 
               TRANSCRIPT.INSTA_TRANSCRIPT_DATE, TRANSCRIPT.INSTA_TRANSCRIPT_STATUS, PERSON.BIRTH_DATE, PERSON_LS.PER_ETHNICS, 
               PERSON.CITIZENSHIP, Y16_PERSON_PREFERRED_ADDRESS_VIEW.ADDRESS_LINE_1, 
               Y16_PERSON_PREFERRED_ADDRESS_VIEW.CITY, Y16_PERSON_PREFERRED_ADDRESS_VIEW.STATE,
               Y16_PERSON_PREFERRED_ADDRESS_VIEW.ZIP, Y16_PERSON_CONTACT_INFO_VIEW.HOME,
               Y16_PERSON_CONTACT_INFO_VIEW.OTHER, Y16_PERSON_CONTACT_INFO_VIEW.INT_EMAIL, Y16_PERSON_CONTACT_INFO_VIEW.YTC_EMAIL, 
               '+@FirstRegTableName+'.STC_REPORTING_TERM AS [First Reg Term], '+@FirstRegTableName+'.[First Reg Date for Term], 
               Y16_Student_First_Credit_Term_View.TERM_REPORTING_TERM AS [First YTC Credit Term], 
                Y16_Student_First_Credit_Term_View.FIRST_COURSE_REG_DATE AS [First YTC Credit Reg. Date],
               Y16_Student_Term_Credit_Hours_View.Term_Credit_Hours AS [First Credit Term Hours], 
               Y16_Student_Term_Credit_Hours_View.Term_Credits_Completed AS [First Term Credits Completed],
                SUM(Y16_Student_Transfer_Hours.Term_Credit_Hours) AS [Transfer Hours]
FROM  
PERSON AS PERSON_1 WITH (NOLOCK) INNER JOIN
               DATES_ATTENDED INNER JOIN
               INSTITUTIONS_ATTEND_VIEW WITH (NOLOCK) ON 
               DATES_ATTENDED.INSTITUTIONS_ATTEND_ID = INSTITUTIONS_ATTEND_VIEW.INSTITUTIONS_ATTEND_ID ON 
               PERSON_1.ID = INSTITUTIONS_ATTEND_VIEW.INSTA_INSTITUTIONS_ID RIGHT OUTER JOIN
               '+@ApplTableName+' WITH (NOLOCK) LEFT OUTER JOIN
               PERSON_LS WITH (NOLOCK) INNER JOIN
               Y16_PERSON_PREFERRED_ADDRESS_VIEW WITH (NOLOCK) INNER JOIN
               PERSON WITH (NOLOCK) ON Y16_PERSON_PREFERRED_ADDRESS_VIEW.ID = PERSON.ID ON PERSON_LS.ID = PERSON.ID ON 
               '+@ApplTableName+'.APPL_APPLICANT = PERSON.ID LEFT OUTER JOIN
               Y16_Appl_Statuses_Pos1_View WITH INNER JOIN
               APPLICATIONS WITH (NOLOCK) ON Y16_Appl_Statuses_Pos1_View.APPLICATIONS_ID = APPLICATIONS.APPLICATIONS_ID ON 
               '+@ApplTableName+'.APPL_DATE = APPLICATIONS.APPL_DATE AND 
               '+@ApplTableName+'.APPL_APPLICANT = APPLICATIONS.APPL_APPLICANT AND 
               '+@ApplTableName+'.APPLICATIONS_ID = APPLICATIONS.APPLICATIONS_ID AND 
               '+@ApplTableName+'.APPL_ACAD_PROGRAM = APPLICATIONS.APPL_ACAD_PROGRAM AND 
               '+@ApplTableName+'.APPL_START_TERM = APPLICATIONS.APPL_START_TERM LEFT OUTER JOIN

               Y16_Student_Transfer_Hours ON '+@ApplTableName+'.APPL_APPLICANT = Y16_Student_Transfer_Hours.STC_PERSON_ID LEFT OUTER JOIN

               '+@FAFSATableName+' WITH (NOLOCK) ON PERSON.ID = '+@FAFSATableName+'.IFAF_STUDENT_ID ON 
               INSTITUTIONS_ATTEND_VIEW.INSTA_PERSON_ID = '+@ApplTableName+'.APPL_APPLICANT LEFT OUTER JOIN
               Y16_Student_Active_Program_View WITH (NOLOCK) ON 
               APPLICATIONS.APPL_APPLICANT = Y16_Student_Active_Program_View.STUDENT_CID LEFT OUTER JOIN
               Y16_Student_Term_Credit_Hours_View WITH (NOLOCK) RIGHT OUTER JOIN
               Y16_Student_First_Credit_Term_View WITH (NOLOCK) ON 
               Y16_Student_Term_Credit_Hours_View.STC_PERSON_ID = Y16_Student_First_Credit_Term_View.STC_PERSON_ID AND 
               Y16_Student_Term_Credit_Hours_View.STC_REPORTING_TERM = Y16_Student_First_Credit_Term_View.TERM_REPORTING_TERM ON 
               APPLICATIONS.APPL_APPLICANT = Y16_Student_First_Credit_Term_View.STC_PERSON_ID LEFT OUTER JOIN
               '+@FirstRegTableName+' WITH (NOLOCK) ON 
               APPLICATIONS.APPL_APPLICANT = '+@FirstRegTableName+'.STC_PERSON_ID LEFT OUTER JOIN
               '+@CreditTableName+' WITH (NOLOCK) ON 
               APPLICATIONS.APPL_APPLICANT = '+@CreditTableName+'.STC_PERSON_ID LEFT OUTER JOIN
               '+@ApplMSTableName+' WITH (NOLOCK) ON 
               APPLICATIONS.APPLICATIONS_ID = '+@ApplMSTableName+'.APPLICATIONS_ID LEFT OUTER JOIN
               TRANSCRIPT WITH (NOLOCK) ON TRANSCRIPT.INSTITUTIONS_ATTEND_ID = INSTITUTIONS_ATTEND_VIEW.INSTITUTIONS_ATTEND_ID
               INNER JOIN Y16_PERSON_CONTACT_INFO_VIEW ON Y16_PERSON_PREFERRED_ADDRESS_VIEW.ID = Y16_PERSON_CONTACT_INFO_VIEW.ID
               '
+ ' WHERE (PERSON_LS.POS = 1) AND (DATES_ATTENDED.POS = 1)
GROUP BY APPLICATIONS.APPL_START_TERM, APPLICATIONS.APPL_APPLICANT, PERSON.LAST_NAME, PERSON.FIRST_NAME, 
               Y16_Appl_Statuses_Pos1_View.APPL_STATUS, Y16_Appl_Statuses_Pos1_View.APPL_STATUS_DATE, '+@ApplTableName+'.APPL_STATUS, 
               '+@ApplTableName+'.[Latest AP Date], APPLICATIONS.APPL_ADMIT_STATUS, APPLICATIONS.APPL_ACAD_PROGRAM, 
               '+@ApplMSTableName+'.APPL_STATUS, '+@ApplMSTableName+'.APPL_STATUS_DATE, '+@FAFSATableName+'.IFAF_ISIR_TYPE, 
               '+@FAFSATableName+'.ISIR_FAFSA_ADDDATE, '+@CreditTableName+'.Term_Credit_Hours, PERSON_1.LAST_NAME, 
               INSTITUTIONS_ATTEND_VIEW.INSTA_EXT_GPA, INSTITUTIONS_ATTEND_VIEW.INSTA_GRAD_TYPE, DATES_ATTENDED.INSTA_END_DATES, 
               TRANSCRIPT.INSTA_TRANSCRIPT_TYPE, TRANSCRIPT.INSTA_TRANSCRIPT_DATE, TRANSCRIPT.INSTA_TRANSCRIPT_STATUS, PERSON.BIRTH_DATE, 
               PERSON_LS.PER_ETHNICS, PERSON.CITIZENSHIP, Y16_PERSON_PREFERRED_ADDRESS_VIEW.ADDRESS_LINE_1,
               Y16_PERSON_PREFERRED_ADDRESS_VIEW.CITY, Y16_PERSON_PREFERRED_ADDRESS_VIEW.STATE,
               Y16_PERSON_PREFERRED_ADDRESS_VIEW.ZIP, Y16_PERSON_CONTACT_INFO_VIEW.HOME,
               Y16_PERSON_CONTACT_INFO_VIEW.OTHER, Y16_PERSON_CONTACT_INFO_VIEW.INT_EMAIL, '+@FirstRegTableName+'.STC_REPORTING_TERM, 
               '+@FirstRegTableName+'.[First Reg Date for Term], Y16_Student_First_Credit_Term_View.TERM_REPORTING_TERM, 
               Y16_Student_Active_Program_View.STUDENT_PROGRAM, Y16_Student_Active_Program_View.STPR_ADMIT_STATUS, 
               Y16_Student_Active_Program_View.PROG_STATUS, Y16_Student_Active_Program_View.PROG_STATUS_DATE, 
               Y16_PERSON_CONTACT_INFO_VIEW.YTC_EMAIL, Y16_Student_Term_Credit_Hours_View.Term_Credit_Hours, 
               Y16_Student_Term_Credit_Hours_View.Term_Credits_Completed,  Y16_Student_First_Credit_Term_View.FIRST_COURSE_REG_DATE
HAVING (APPLICATIONS.APPL_START_TERM = '''+@UTerm+''') AND (APPLICATIONS.APPL_ACAD_PROGRAM <> ''CE.CE'') 
ORDER BY [Current Status], APPLICATIONS.APPL_ADMIT_STATUS, PERSON.LAST_NAME, PERSON.FIRST_NAME'

SET NOCOUNT OFF
END
exec (@sql)

Answer 1

The first part of your stored procedure (the part that has the right field alias) doesn't execute because it is effectively commented out by the statement:

IF 1 = 2

The third line of of the assignment to @sql aliases to the wrong field name:

Y16_Appl_Statuses_Pos1_View.APPL_STATUS AS Current Status, Y16_Appl_Statuses_Pos1_View.APPL_STATUS_DATE AS Current Status Date, 

Note the field alias Current Status has a space rather than an underscore that your dataset expects. Use Current_Status instead in your SQL (or encase the field alias in the SQL in square brackets [Current Status] and change the field name in the dataset to have a space rather than an underscore).

Current Status Date looks like it will have the same problem.