The data set ‘WeeklyManagement’ contains a definition for the field ‘BrewNumber’. This field is missing from the returned result set from the data source.
I get this error when I try to preview the report. The missing field is always the first column in the dataset. I have tried everything I can think of, including repeating the column, rewriting the sproc, rewriting the report, everything. I cannot find any reason why this should be happening. Can anyone help? BTW, when I create the dataset and run the sproc, all fields are returned fine. When I examine the xml, all the fields are there, correctly defined.
Help, please! TIA. D. Lewis
Can you publish the sproc? Print statements did this to us in the past.|||Hmmm. Very interesting. Following is the sproc, which I altered to comment out the 'Print' statements (yes, I did have some in there for debugging purposes). I also as an experiment repeated the first column to see if it would work, and RS began dropping that new column; error message as below:
Build complete -- 0 errors, 0 warnings
The data set ‘WeeklyManagement’ contains a definition for the field ‘B’. This field is missing from the returned result set from the data source.
Preview complete -- 0 errors, 1 warnings
The sproc is a bit messy, and uses a temp table, but for completeness' sake here it is:
use snb01
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Alter PROCEDURE spBrewhouseOverview
AS
Declare @.BeginDate smalldatetime
Declare @.EndDate smalldatetime
Set @.BeginDate = DATEADD(dd,-45,Getdate())--'9/1/05'
set @.EndDate = Getdate()--'9/15/05'
--Print convert(varchar,@.begindate)
--Print convert(varchar,@.enddate)
--spBrewhouseOverview '8/2/05','8/3/05'
--5/20/03
--Have to convert the dates to char then to date again
--to strip out the time portion that crystall passes
--also added one day to the end time to
--allow for the fact that the end day would be midnight
--at the beginning of the ending day, which would otherwise
--effectively eliminate all brews on that day from showing up.
BEGIN
CREATE TABLE [#tblOverview] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProBrewID] [int] NULL,
[BrewNumber] [int] NULL ,
[Kettle] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTimeInKettle] [smalldatetime] NULL ,
[StartTimeBoil] [smalldatetime] NULL ,
[EndTimeBoil] [smalldatetime] NULL ,
[Hop1Time] [smalldatetime] NULL ,
[Hop2Time] [smalldatetime] NULL ,
[MossTime] [smalldatetime] NULL ,
[Hop3Time] [smalldatetime] NULL ,
[Hop4Time] [smalldatetime] NULL ,
/*[StartTimeMash] [smalldatetime] NULL ,
[SaccReqTime] [smalldatetime] NULL ,
[SaccTime] [smalldatetime] NULL ,
[MashOutTime] [smalldatetime] NULL ,
[StartTimeInLauter] [smalldatetime] NULL ,
[StartTimeVorlauf] [smalldatetime] NULL ,
[StartTimeLauter] [smalldatetime] NULL ,
[EndTimeLauter] [smalldatetime] NULL ,
[GravityVorlauf] [int] NULL ,
[GravityFirstRunnings] [int] NULL ,
[GravityLastRunnings] [int] NULL ,
[GravityLauter] [int] NULL ,
[HazeFirstRunnings] [int] NULL ,
[HazeLauterVorlauf] [int] NULL ,
[HazeLauterLauter] [int] NULL ,*/
[StartTimeRest] [smalldatetime] NULL ,
[StartTimeWhirlWaterVor] [smalldatetime] NULL,
/*[MashHour] [int] NULL,
[LauterHour] [int] NULL,
[KettleHour] [int] NULL,
[WhirlpoolHour] [int] NULL,
[BrewerMash] [Varchar] (20) NULL,
[BrewerLauter] [Varchar] (20) NULL,*/
[BrewerKettle] [Varchar] (20) NULL,
[BrewerWhirlpool] [Varchar] (20) NULL,
CONSTRAINT [PK_tblOverview] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
SET NOCOUNT ON
INSERT #tblOverview
(Probrewid
, Brewnumber
, Kettle
, StarttimeInKettle
, StartTimeBoil
, EndTimeBoil
, Hop1Time
, Hop2Time
, MossTime
, Hop3Time
, Hop4Time/*
, StartTimeMash
, SaccReqTime
, SaccTime
, MashOutTime
, StartTimeInLauter
, StartTimeVorlauf
, StartTimeLauter
, EndTimeLauter
, GravityVorlauf
, GravityFirstRunnings
, GravityLastRunnings
, GravityLauter
, HazeFirstRunnings
, HazeLauterVorlauf
, HazeLauterLauter*/
, StartTimeRest
, StartTimeWhirlWaterVor)
(
Select a.probrewid
, a.Brewnumber
, a.Kettle
, a.StarttimeInKettle
, a.StartTimeBoil
, a.EndTimeBoil
, a.Hop1Time
, a.Hop2Time
, a.MossTime
, a.Hop3Time
, a.Hop4Time
/*, b.StartTimeMash
, b.SaccReqTime
, b.SaccTime
, b.MashOutTime
, c.StartTimeInLauter
, c.StartTimeVorlauf
, c.StartTimeLauter
, c.EndTimeLauter
, c.GravityVorlauf
, c.GravityFirstRunnings
, c.GravityLastRunnings
, c.GravityLauter
, c.HazeFirstRunnings
, c.HazeLauterVorlauf
, c.HazeLauterLauter*/
, d.StartTimeRest
, d.StartTimeWhirlWaterVor
FROM Proleit.dbo.tblRptKettleAll a
INNER JOIN Proleit.dbo.tblRptMashingAll b on a.Probrewid=b.probrewid
INNER JOIN Proleit.dbo.tblRptLauterAll c on a.Probrewid=c.Probrewid
INNER JOIN Proleit.dbo.tblRptWhirlpoolAll d on a.Probrewid=d.probrewid
WHERE a.StartTimeBoil>=CONVERT(datetime,(CONVERT(varchar,@.BeginDate,112)))
AND a.StartTimeBoil<= DATEADD(day,1,CONVERT(datetime,(CONVERT(varchar,@.EndDate,112))))
)
--Comment out for troubleshooting
--declare a cursor for the table, loop through
--and update each brewer
DECLARE CurC CURSOR FOR SELECT Probrewid,MossTime,StartTimeRest
FROM #tblOverview
DECLARE @.Probrewid int
DECLARE @.TimeMoss datetime
DECLARE @.TimeWhirl datetime
OPEN CurC
FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
BEGIN
WHILE @.@.FETCH_STATUS=0
BEGIN
--Print convert(varchar,@.probrewid) + ' is probrewid'
--Print convert(varchar,@.TimeMoss) + ' is @.TimeMoss'
--Exec spBrewhouseOverviewSacc @.probrewid,@.timeSacc
--Exec spBrewhouseOverviewVorlauf @.probrewid,@.timeVorlauf
Exec Proleit.dbo.spBrewhouseOverviewKettle @.probrewid,@.timeMoss
Exec Proleit.dbo.spBrewhouseOverviewWhirlpool @.probrewid,@.timeWhirl
FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
END
END
CLOSE CurC
DEALLOCATE CurC
SET NOCOUNT ON
UPDATE #tblOverview
SET BrewerKettle='None Recorded'
WHERE BrewerKettle is null
UPDATE #tblOverview
SET BrewerWhirlpool='None Recorded'
WHERE BrewerWhirlpool is null
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,MossTime,Hop3Time) as Minutes
, BrewerKettle as Brewer
, 'Hop3Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,MossTime,Hop3Time)<10
OR DATEDIFF(n,MossTime,Hop3Time)>13
UNION ALL
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,Hop3Time,Hop4Time) as Minutes
, BrewerKettle as Brewer
, 'Hop4Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,Hop3Time,Hop4Time)<10
OR DATEDIFF(n,Hop3Time,Hop4Time) > 13
UNION ALL
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor) as Minutes
, BrewerWhirlpool
, 'WhirlpoolTime' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor)>21
Drop Table #tblOverview
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|||If you're not working with a whole lot of data, an @.Table variable instead of a temp table may be more efficient and you won't need the drop table statement at the end.
Try adding to your UNION ALL statements with a select statement at the top that sets a default value.... eg. SELECT 0 as B, 0 as BrewNumber, 0 as Kettle, 0 as BrewDate, etc.... to see if it is a problem with the naming of the fields and the first result set not returning a value.
Maybe dump that above dummy SELECT statement in various places within the stored procedure, so that you can find out where it is breaking. (not returning the right result set)
It could have something to do with the 2 execs you have going there... It would be interesting to see the SQL messages pane.|||I will set to work on those suggestions, thanks.
Just to clarify, the result set returned in Query Analyzer is fine, with no errors and no messages. When I refresh the dataset from within visual studio while designing the report, I also get the entire set with no issues. It is only when I try to preview the report that the thing bonks -- i.e., the first column is dropped and VS tells me that it is not defined.
Also, I went back and examined the two 'exec ....' statements, and they are simple update statements, with no 'Print....' or anything else. Very innocuous, actually. I placed them in separate sprocs because they are reused elsewhere and are long.
No comments:
Post a Comment