making a statistics table....

hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.

however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:

UserID Stat1 Stat2 Stat3 ..... Stat32
23 0 0 0 0
56 0 0 0 0
72 0 0 0 0

this is a query for one of the statistics i am currently using:

SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID
FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID
WHERE UserDescription.Status=1 AND UserDescription.UserType=1
GROUP BY DefendantCase.UserID

if i run this query, this is what i get:

UserID CountOfUserID
54 21
60 10
52 29
4 4
27 22

how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!I'm guessing you want to update when the userid is already present in the table and insert when it's not...
-- Insert
-- OUTER JOIN with OfficeReport WHERE UserID IS NULL should give all
-- UserID's that are not in OfficeReport
INSERT OfficeReport (UserID, Stat1)
SELECT dc.UserID
,COUNT(dc.UserID)
FROM DefendantCase dc
LEFT OUTER JOIN UserDescription ud ON dc.UserID = ud.UserID
LEFT OUTER JOIN OfficeReport orep ON orep.UserID = dc.Userid
WHERE ud.Status = 1
AND orep.UserID IS NULL

-- Update
UPDATE orep
SET Stat1 = COUNT(dc.UserID)
FROM OfficeReport orep
INNER JOIN DefendantCase dc ON dc.UserID = orep.UserID
LEFT OUTER JOIN UserDescription ud ON dc.UserID= ud.UserID
WHERE ud.status = 1|||if i use your update method, i get this error:
An aggregate may not appear in the set list of an UPDATE statement. thanks!|||Aaah, yes, an aggregate (COUNT in this case) skips NULL columns.

Sorry, I seem to have forgotten the GROUP BY in the INSERT en UPDATE this should be added to both:
GROUP BY dc.UserID
:osql

Major problem with Forms Auth and WMI

I am getting following errors more and more often, when trying to sign-in to
Report Manager on the server hosting RS:
- "provider load failure"
- "Cound not read the Report Manager configuration file. Check to ensure
your Reporting Services WMI provider is working properly."
I don't have a clue where these error messages are coming from. IIS?
What is this WMI provider, and how can I verify if it is working properly'
Where should I start investigating the problem?
Until now, the only workaround is to reboot the server :(((
SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
We're using Forms Auth as per the MSDN article.At a guess - Windows Management Instrumentation service is not running/has
stopped. Go to Administrative Tools,Services and find out.
Charles Kangai, MCT, MCDBA
"James" wrote:
> I am getting following errors more and more often, when trying to sign-in to
> Report Manager on the server hosting RS:
> - "provider load failure"
> - "Cound not read the Report Manager configuration file. Check to ensure
> your Reporting Services WMI provider is working properly."
> I don't have a clue where these error messages are coming from. IIS?
> What is this WMI provider, and how can I verify if it is working properly'
> Where should I start investigating the problem?
> Until now, the only workaround is to reboot the server :(((
> SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> We're using Forms Auth as per the MSDN article.|||Thanks Charles,
I had restarted the server, so I cannot reproduce the "WMI provider" problem
right now. I'll make sure I check the WMI service and the WMI Driver
Extensions service next time I get the error.
Anybody can contribute on the "provider load failure" problem? This one
comes and goes. I try twice to log-on to RS Manager and get the error twice,
and the 3rd time I can log-on. Sometimes I can log-on straight away.
I'd like to know which is the application generating these messages, and
what I can do to find the source of the problem.
"Charles Kangai" wrote:
> At a guess - Windows Management Instrumentation service is not running/has
> stopped. Go to Administrative Tools,Services and find out.
> Charles Kangai, MCT, MCDBA
> "James" wrote:
> > I am getting following errors more and more often, when trying to sign-in to
> > Report Manager on the server hosting RS:
> > - "provider load failure"
> > - "Cound not read the Report Manager configuration file. Check to ensure
> > your Reporting Services WMI provider is working properly."
> >
> > I don't have a clue where these error messages are coming from. IIS?
> > What is this WMI provider, and how can I verify if it is working properly'
> > Where should I start investigating the problem?
> >
> > Until now, the only workaround is to reboot the server :(((
> >
> > SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> > We're using Forms Auth as per the MSDN article.|||I've been experiencing the same problem on a reports server with the forms
extension set up. The problem seems to appear sporadically. Sometimes every
couple of weeks, sometimes every couple of hours, and the only solution has
been to restart the WMI service. Anyone got any pointers on how to avoid
this?
Cheers,
Si
"James" wrote:
> Thanks Charles,
> I had restarted the server, so I cannot reproduce the "WMI provider" problem
> right now. I'll make sure I check the WMI service and the WMI Driver
> Extensions service next time I get the error.
> Anybody can contribute on the "provider load failure" problem? This one
> comes and goes. I try twice to log-on to RS Manager and get the error twice,
> and the 3rd time I can log-on. Sometimes I can log-on straight away.
> I'd like to know which is the application generating these messages, and
> what I can do to find the source of the problem.
>
> "Charles Kangai" wrote:
> > At a guess - Windows Management Instrumentation service is not running/has
> > stopped. Go to Administrative Tools,Services and find out.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "James" wrote:
> >
> > > I am getting following errors more and more often, when trying to sign-in to
> > > Report Manager on the server hosting RS:
> > > - "provider load failure"
> > > - "Cound not read the Report Manager configuration file. Check to ensure
> > > your Reporting Services WMI provider is working properly."
> > >
> > > I don't have a clue where these error messages are coming from. IIS?
> > > What is this WMI provider, and how can I verify if it is working properly'
> > > Where should I start investigating the problem?
> > >
> > > Until now, the only workaround is to reboot the server :(((
> > >
> > > SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> > > We're using Forms Auth as per the MSDN article.|||I've been effected by this bug as well, also after implementing the
Forms Authentication per the MSFT sample (and its Teo Lachev derivative
on devx.com). I can't say with any authority that the Forms Auth
causes it, because implementing the custom security extension is just
about the first thing I did after installing Reporting Services.
The bug happens infrequently but not rarely.
There are a couple entries that show up in the Application Event Log
when the "Provider Load Failure" message appears:
Event Type: Error
Event Source: WinMgmt
Event Category: None
Event ID: 20
Description:
Failed to CoGetClassObject for provider "ReportingServicesWMI". Error
code 0x800703E6 was returned.
Event Type: Error
Event Source: Schedule and Delivery Processor
Event Category: Management
Event ID: 107
Description:
Schedule and Delivery Processor cannot connect to the report server
database.
Like others here, the only (temporary) fix I've found is restarting the
WMI service. When in this state, the services control panel shows WMI
as running but, when shutdown, creates this popup:
WinMgmt.exe - Application Error : The instruction at "0x77fcc823"
referenced memory at "0x041747f0". The memory could not be "read".
Once you click OK the service does shutdown, and on restart the whole
thing works again ... until it doesn't.|||can you explain this...The instruction at "0x77fcc823"referenced memeory at "0x000074b1". The memeory counld not be read...Pls i need answer via e-mail..thanks..
From http://www.developmentnow.com/g/115_2004_11_0_0_453374/Major-problem-with-Forms-Auth-and-WMI.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com