Running RS2000 on Windows 2003
I published a new report today (copied from an existing report, used same
data source calling different stored procedure, changed parameters, etc.).
It worked fine when run locally.
However, when published and run within Report Manager I get the following
error -
Reporting Services Error
----
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
General network error. Check your network documentation.
I'm not sure I've seen this error before. I've had errors before but they
usually occurred because I forgot to give adequate permissions to the stored
procedure or something like that.
I've search around but haven't figured out how to troubleshoot this problem.
I have looked at the trace logs but I'm still learning how to read them.
Here is what appear to be the relevant portion found in them -
In ReportServer__08_17_2007_06_42_51.log
w3wp!library!1058!08/17/2007-10:54:06:: i INFO: Call to RenderFirst(
'/Development/Ref Des Term Count' )
w3wp!library!1058!08/17/2007-10:54:07:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: A severe error occurred on
the current command. The results, if any, should be discarded.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.CancelableSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.ChunkStorage.WriteNewSnapshotToDB(String
effectiveParams, Int32 paramsHash, DateTime createdDate, String description,
Guid snapshotDataID, Boolean isPermanentSnapshot)
at
Microsoft.ReportingServices.Library.ReportSnapshot.WriteNewSnapshotToDB(String effectiveParams, Int32 paramsHash, DateTime createdDate, String description)
at
Microsoft.ReportingServices.Library.RSService.AllocateNewSnapshot(Boolean
isPermanentSnapshot, String effectiveParams, Int32 paramsHash, DateTime
createdDate, String description)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext
reportContext, ItemProperties properties, ParameterInfoCollection
effectiveParameters, Guid reportId, ClientRequest session, String
description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection
thisReportDataSources, Boolean cachingRequested, Warning[]& warnings,
ReportSnapshot& resultSnapshotData, DateTime& executionDateTime,
RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
usedUserProfile)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --
w3wp!library!1058!08/17/2007-10:54:07:: i INFO: Initializing
EnableExecutionLogging to 'True' as specified in Server system properties.
w3wp!webserver!1058!08/17/2007-10:54:08:: e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An internal
error occurred on the report server. See the error log for more details. -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: A severe error occurred on
the current command. The results, if any, should be discarded.
In ReportServerService__main_08_17_2007_05_10_24.log
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.1038.00</Product>
<Locale>en-US</Locale>
<TimeZone>Central Daylight Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__main_08_17_2007_05_10_24.log</Path>
<SystemName>IW-SQL</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!servicecontroller!11dc!8/17/2007-05:10:24:: i INFO:
Recycling the service from default domain
ReportingServicesService!servicecontroller!11dc!8/17/2007-05:10:27:: i INFO:
New app domain started
Other existing reports seem to be working with no problem.
Any help troubleshooting this would be greatly appreciated.
KevinWell I've found a solution. Instead of copying an existing report I just
started from scratch. I built a new one from the ground up and it worked
fine.
Apparently there was something in the report I made from the copy that it
didn't like. I thought I went through it pretty thoroughly to make sure
nothing referred to fields, parameters, etc. that didn't apply but I guess I
missed something.
For future reference I would appreciate any recommendations for catching
problems when copying a report. Maybe copying isn't recommended? It usually
seems to save time (except in this case!).
Kevin|||Rats!!! I thought I had it fixed. My report and stored procedure both have
just one parameter. I just made a simple report (data is listed in a table)
and set a default value for the report parameter and it worked.
Thinking that it was fixed, I removed the default and re-published. Now
when I select a value from the list in the prompt and run the report I get
the error.
So, is the problem something to do with my prompt? Any ideas on
troubleshooting would be appreciated. The prompt is just a simple query from
one table -
SELECT DISTINCT r.item AS PromptValue
,r.item AS PromptLabel
FROM dbname.dbo.tablename r
WHERE r.item <> ''
ORDER BY r.item
The stored procedure queries the same table and is as follows -
CREATE PROCEDURE rpt_Ref_DesTermCount
(
@.StockCode VARCHAR(30) = '%'
)
AS
SELECT r.item
,r.ref_des
,r.TermCount
FROM dbname.dbo.tablename r
WHERE r.item LIKE @.StockCode
ORDER BY r.ref_des
GO
Thanks in advance,
Kevin
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
RS2005's own SPs to add users to RS
Dear Anyone,
Is it possible to use Reporting Services' own stored procedure to add users directly into RS's security roles?
Thanks,
Joseph
Direct access to the RS catalog is not supported. Is there a reason why SOAP APIs are not enough?
Thanks
Tudor
sqlRS2005's own SPs to add users to RS
Dear Anyone,
Is it possible to use Reporting Services' own stored procedure to add users directly into RS's security roles?
Thanks,
Joseph
Direct access to the RS catalog is not supported. Is there a reason why SOAP APIs are not enough?
Thanks
Tudor
RS2005 multi-select parameter
How do I use the mult-select parameter option on a report and pass it
to the stored procedure?
ie. if I have a multi-select list of employees and I pass in the
employeeid to the proc
would I?
create procedure show_employees(@.empid int)
as
select * from employees where empid in (@.empid)
would that work?
Regards,
Tom OlthoffNope. The stored procedure won't work (note that SQL will work in RS).
Why it doesn't work has nothing really to do with RS but has to do with
Stored Procedures in SQL Server. You cannot do the following in a stored
procedure. Let's say you have a Parameter called @.MyParams Now you can map
that parameter to a multi-value parameter but if in your stored procedure
you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.What you can do
is to have a string parameter that is passed as a multivalue parameter and
then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<OTSolutions@.shaw.ca> wrote in message
news:1144854681.805431.214920@.i40g2000cwc.googlegroups.com...
> How do I use the mult-select parameter option on a report and pass it
> to the stored procedure?
> ie. if I have a multi-select list of employees and I pass in the
> employeeid to the proc
> would I?
> create procedure show_employees(@.empid int)
> as
> select * from employees where empid in (@.empid)
> would that work?
> Regards,
> Tom Olthoff
>|||You can do this using XML I believe.
Pass an XML string that looks like this:
<root>
<param1>
<multiParam>value1</multiParam>
<multiParam>value2</multiParam>
<multiParam>value3</multiParam>
</param1>
</root>
And then use SQL's readXML function to read multiple values from the
XML.|||The one problem with that approach is its not really a list of
parameters its one paramter that is a string of XML.
regards,
Stas K.|||You can read about this approach with the sp_XML_PrepareDocument
function:
http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx
as for the actual access to the xml:
OPENXML( @.XMLString, N'/root/param1/multiparam/*')
http://msdn2.microsoft.com/en-us/library/ms186918(SQL.90).aspx
regards,
Stas K.
to the stored procedure?
ie. if I have a multi-select list of employees and I pass in the
employeeid to the proc
would I?
create procedure show_employees(@.empid int)
as
select * from employees where empid in (@.empid)
would that work?
Regards,
Tom OlthoffNope. The stored procedure won't work (note that SQL will work in RS).
Why it doesn't work has nothing really to do with RS but has to do with
Stored Procedures in SQL Server. You cannot do the following in a stored
procedure. Let's say you have a Parameter called @.MyParams Now you can map
that parameter to a multi-value parameter but if in your stored procedure
you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.What you can do
is to have a string parameter that is passed as a multivalue parameter and
then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<OTSolutions@.shaw.ca> wrote in message
news:1144854681.805431.214920@.i40g2000cwc.googlegroups.com...
> How do I use the mult-select parameter option on a report and pass it
> to the stored procedure?
> ie. if I have a multi-select list of employees and I pass in the
> employeeid to the proc
> would I?
> create procedure show_employees(@.empid int)
> as
> select * from employees where empid in (@.empid)
> would that work?
> Regards,
> Tom Olthoff
>|||You can do this using XML I believe.
Pass an XML string that looks like this:
<root>
<param1>
<multiParam>value1</multiParam>
<multiParam>value2</multiParam>
<multiParam>value3</multiParam>
</param1>
</root>
And then use SQL's readXML function to read multiple values from the
XML.|||The one problem with that approach is its not really a list of
parameters its one paramter that is a string of XML.
regards,
Stas K.|||You can read about this approach with the sp_XML_PrepareDocument
function:
http://msdn2.microsoft.com/en-us/library/ms187367(SQL.90).aspx
as for the actual access to the xml:
OPENXML( @.XMLString, N'/root/param1/multiparam/*')
http://msdn2.microsoft.com/en-us/library/ms186918(SQL.90).aspx
regards,
Stas K.
Tuesday, March 20, 2012
RS: multiple SP more performant?
Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
RS: multiple SP more performant?
Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
RS: multiple SP more performant?
Hi,
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
If I use for one given Report (Reporting Services) multiple Stored
Procedures as DataSource instead of 1 big Stored Procedure, will this be
more performant or not? Are these multiple sotred procedures executed
simultaneously, of one after the other?
Thanks a lot in advance,
PieterHey Lot of things involved. Stored procedures are executed sequencial if you
have one processor, if you have multiple then it may go as a parallel
execution. Again
it depends if you have complex calculation in single stored procedure then
it may take some time. Otherwise it should not make much of a difference if
it is optimized.
Amarnath
"Pieter" wrote:
> Hi,
> If I use for one given Report (Reporting Services) multiple Stored
> Procedures as DataSource instead of 1 big Stored Procedure, will this be
> more performant or not? Are these multiple sotred procedures executed
> simultaneously, of one after the other?
> Thanks a lot in advance,
> Pieter
>
>
Saturday, February 25, 2012
rs drops first column in data set
I started having this problem some time ago when trying to build a report using a stored procedure. Existing reports work fine, it is only new reports that result in this error:
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.
Subscribe to:
Posts (Atom)