Microsoft KB Archive/319631

= User Trends - Static Report Fails to Run =

Article ID: 319631

Article Last Modified on 10/22/2002

-

APPLIES TO


 * Microsoft Commerce Server 2000 Service Pack 2

-



This article was previously published under Q319631



SYMPTOMS
When you run the User Trends - Static report against a data warehouse that contains a certain set of data in the UniqueUserKeyByDate table, the report fails. The CSDW_MakeUserTrends stored procedure may cause the CPU usage on the Microsoft SQL Server computer to reach 100%, and the TempDB table may fill the hard disk.



CAUSE
This report was made static in Commerce Server 2000 SP2. The report displays new and repeat user activity broken down by month. However, the calculation of new users is performed by using a join that is so large that it cannot be calculated.



RESOLUTION
To resolve this problem, run the following code in the SQL Server Query Analyzer against the data store: if exists (select * from dbo.sysobjects _     where id = object_id(N'[dbo].[CSDW_MakeUserTrends]') _      and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[CSDW_MakeUserTrends] GO

SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO

create procedure [CSDW_MakeUserTrends] @SiteName varchar(255), _ @daterange varchar(300), @ResultTable varchar(36) as Begin Set nocount on   Declare @Query1 nvarchar(2000)

SELECT @Query1 = '  CREATE TABLE [dbo].['+ @RESULTTABLE + '] (        [Site Name] [nvarchar] (256) NULL,        [Year] int NULL ,        [Month] int NULL ,        [All Users] int NULL ,        [New Users] int NULL ,        [Repeat Users] int NULL ,        [Repeat User %] numeric(38,3) NULL    ) ON [PRIMARY]' exec (@Query1) SELECT @Query1 = ' insert into ['+ @RESULTTABLE + '] (       [Site Name],        [Year],        [Month],        [All Users],        [New Users],        [Repeat Users],        [Repeat User %]    ) select [Site Name], [Year], [Month], [All Users], 0,       0,        0    from (    SELECT SiteDimensionView.SiteName [Site Name],    datepart(yy, dTimestamp) Year,     datepart(mm, dTimestamp) Month,    Count(Distinct SQ1.AllUserKeys) [All Users]     FROM ( select distinct u.SiteId, d.dTimestamp, u.LogUserID AllUserKeys from UniqueUserKeyByDate u, Date d       where u.DateID = d.DateID group by u.SiteId, d.dTimestamp, LogUserID ) as SQ1,   SiteDimensionView     Where SQ1.SiteID = SiteDimensionView.SiteID    And ' + @SiteName + '    And ' + @DateRange + '    Group By     SiteName,     datepart(yy, dTimestamp),     datepart(mm, dTimestamp)) as XXX' exec (@Query1) SELECT @Query1 = ' Update [' + @ResultTable + '] SET [New Users] = (select       [NewUsers]    from ( Select SiteDimensionView.SiteName, datepart(yy, s1.dTimestamp) Year, datepart(mm, s1.dTimestamp) month, count (distinct s1.LogUserID) NewUsers From SiteDimensionView, (       select distinct u.SiteId, u.LogUserID, min(d.dTimestamp) as dTimestamp            from   UniqueUserKeyByDate u, Date d        where  u.DateID = d.DateID        group by u.SiteId, LogUserID) as s1    Where s1.SiteId = SiteDimensionView.SiteID And ' + @SiteName + ' Group By    SiteDimensionView.SiteName, datepart(yy, s1.dTimestamp), datepart(mm, s1.dTimestamp)) as YYY   Where YYY.SiteName = [' + @ResultTable + '].[Site Name]    And YYY.Year = [' + @ResultTable + '].[Year]    And YYY.month = [' + @ResultTable + '].[month])' exec (@Query1) SELECT @Query1 = ' Update [' + @ResultTable + '] SET [Repeat Users] = (select       [RepeatUsers]    from ( Select SiteDimensionView.SiteName, datepart(yy, s1.dTimestamp) Year, datepart(mm, s1.dTimestamp) month, count (distinct s1.LogUserID) RepeatUsers From SiteDimensionView, (       Select SiteId, LogUserID, d.dTimestamp          from UniqueUserKeyByDate u, Date d        where  u.DateID = d.DateID        And ' + @DateRange + '        Group by SiteId, LogUserID, d.dTimestamp        ) as S1, (       Select SiteId, LogUserID, min(d.dTimestamp) dTimestamp        from UniqueUserKeyByDate u, Date d        where  u.DateID = d.DateID        Group By SiteId, LogUserID        ) as S2     Where s1.dTimestamp > s2.dTimestamp And s1.LogUserID = s2.LogUserID And s1.SiteId = s2.SiteId And s1.SiteId = SiteDimensionView.SiteID And ' + @SiteName + ' Group By    SiteDimensionView.SiteName, datepart(yy, s1.dTimestamp), datepart(mm, s1.dTimestamp)) as YYY   Where YYY.SiteName = [' + @ResultTable + '].[Site Name]    And YYY.Year = [' + @ResultTable + '].[Year]    And YYY.month = [' + @ResultTable + '].[month])' exec (@Query1) SELECT @Query1 = ' Update [' + @ResultTable + '] SET [Repeat User %] _ = 100*(Convert(float, [Repeat Users])/Convert(float, [All Users]))' exec (@Query1) END GO



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Keywords: kbbug kbfix KB319631

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.