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

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way to
specify that (concering that I get IS package, very hard to modify anything)?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:
> Pedja
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000).
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed the
> password
> In other words , your SQL Agent runs under the same account as you logged ,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> > Hi All,
> > I am trying to create maintenance plan for updating statistics, checking
> > database integrity and database backup, using wizard. First of all, I
> > don't
> > see any option for how long do I want to keep backup files on the system
> > (like we had on 2000). I find it very inconvinient. Is there any other way
> > to
> > specify that (concering that I get IS package, very hard to modify
> > anything)?
> > More important question is regarding security issue with this wizard: I
> > have
> > only 'sa' access to the server (not member of local admin group, and I'm
> > not
> > able to use windows authentication). Each month we change 'sa' password on
> > all our servers. When we change the password, all our maintenance jobs
> > start
> > failing (with the message that 'sa' login failed)!?!?! This is happening
> > regardless of the fact that it is specified that the job runs under sql
> > server agent account... I remember having similar problem with DTS (sql
> > 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> > am I
> > missing something?
> > Thanks in advance,
> > Pedja
> >
> > P.S. The same thing would happen even if I created maintenance plan
> > manually, because I would have to create connection objects manually (and
> > specify 'sa' again)...
>
>

maintenance plan wizard - security question, possible bug?

Hi All,
I am trying to create maintenance plan for updating statistics, checking
database integrity and database backup, using wizard. First of all, I don't
see any option for how long do I want to keep backup files on the system
(like we had on 2000). I find it very inconvinient. Is there any other way t
o
specify that (concering that I get IS package, very hard to modify anything)
?
More important question is regarding security issue with this wizard: I have
only 'sa' access to the server (not member of local admin group, and I'm not
able to use windows authentication). Each month we change 'sa' password on
all our servers. When we change the password, all our maintenance jobs start
failing (with the message that 'sa' login failed)!?!?! This is happening
regardless of the fact that it is specified that the job runs under sql
server agent account... I remember having similar problem with DTS (sql
2000). Is it possible that Microsoft didn't correct this obvious bug? Or am
I
missing something?
Thanks in advance,
Pedja
P.S. The same thing would happen even if I created maintenance plan
manually, because I would have to create connection objects manually (and
specify 'sa' again)...Pedja
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000).
I think your SQL Server Agent is running not under domain account but
under LocalSystem account and it will be thrown the error if you changed the
password
In other words , your SQL Agent runs under the same account as you logged ,
isnt?
BTW ,what is the version are you using? Is it SQL Server 2005?
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
> Hi All,
> I am trying to create maintenance plan for updating statistics, checking
> database integrity and database backup, using wizard. First of all, I
> don't
> see any option for how long do I want to keep backup files on the system
> (like we had on 2000). I find it very inconvinient. Is there any other way
> to
> specify that (concering that I get IS package, very hard to modify
> anything)?
> More important question is regarding security issue with this wizard: I
> have
> only 'sa' access to the server (not member of local admin group, and I'm
> not
> able to use windows authentication). Each month we change 'sa' password on
> all our servers. When we change the password, all our maintenance jobs
> start
> failing (with the message that 'sa' login failed)!?!?! This is happening
> regardless of the fact that it is specified that the job runs under sql
> server agent account... I remember having similar problem with DTS (sql
> 2000). Is it possible that Microsoft didn't correct this obvious bug? Or
> am I
> missing something?
> Thanks in advance,
> Pedja
> P.S. The same thing would happen even if I created maintenance plan
> manually, because I would have to create connection objects manually (and
> specify 'sa' again)...|||Uri,
Both, sql server and sql server agent service run under domain account which
is member of local administrators group. Version is 2005sp1.
Why don't you try: create maintenance plan (updating statistics,
integrity,...) logged on as 'sa', run it (successfully), change 'sa'
password, run it again, and it will fail (with 'sa' login failure message).
Again, this is happening even if it is defined for the job step (which ends
up being integration services package) to run under sql server agent account
(domain account, local administrator group).
Pedja
"Uri Dimant" wrote:

> Pedja
> I think your SQL Server Agent is running not under domain account but
> under LocalSystem account and it will be thrown the error if you changed t
he
> password
> In other words , your SQL Agent runs under the same account as you logged
,
> isnt?
> BTW ,what is the version are you using? Is it SQL Server 2005?
>
> "Pedja" <Pedja@.discussions.microsoft.com> wrote in message
> news:23831A15-C5E6-4F2F-AE2F-3B165C9B5419@.microsoft.com...
>
>