moodle.waes.ac.uk/moodle.waes.ac.uk.zip/moodle.waes.ac.uk/TMG/SP1-TMG-KB981324-AMD64-ENU.msp/PCW_CAB_SHFx2/F2143_msfpcui.dll/BINARY/25498

Text File | 2010-06-15 | 4KB | 111 lines

CREATE PROCEDURE [dbo].[ISA_spServerParticipationRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10)
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblServerParticipationSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        [ServerName] nvarchar(32),
        [date]       datetime
    );
    -- we filter out '-' entries which mean no log DBs for that day
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            [ServerName] AS ServerName,
            [FromDate]   AS [date]
        FROM ' + @SummTableName + '
        WHERE ([FromDate] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [FromDate] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
              AND [ServerName] != ''-''
        GROUP BY [ServerName], [FromDate]
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    -- create string of participating servers
    DECLARE @ServerName nvarchar(100)
    DECLARE Servers_Cursor CURSOR FOR
    SELECT [ServerName] FROM #Table1 GROUP BY [ServerName]
    OPEN Servers_Cursor;
    FETCH NEXT FROM Servers_Cursor
    INTO @ServerName;
    DECLARE @ServersConcat_String nvarchar(4000) = ''
    DECLARE @DbCount Int = 1
    WHILE @@FETCH_STATUS = 0
    BEGIN
          PRINT 'Server Name: ' + @ServerName;
          IF (@DbCount <> 1)
          BEGIN
               SET @ServersConcat_String = @ServersConcat_String + N'{[10911]}'
          END
          SET @DbCount = @DbCount + 1
          IF (@DbCount > 64)
          BEGIN
            CLOSE Servers_Cursor;
            DEALLOCATE Servers_Cursor;
            RETURN(4)
          END
          SET @ServersConcat_String = @ServersConcat_String + @ServerName
          FETCH NEXT FROM Servers_Cursor
          INTO @ServerName;
    END
    CLOSE Servers_Cursor;
    DEALLOCATE Servers_Cursor;
    -- create table with full date range
    CREATE TABLE #AllDays ( [date] datetime );
    DECLARE @tmpDate datetime = @FromDate
    WHILE @tmpDate <= @ToDate
    BEGIN
        INSERT INTO #AllDays ([date]) VALUES(@tmpDate)
        SET @tmpDate = DATEADD (day, 1, @tmpDate)
    END
    -- create table with missing servers
    CREATE TABLE #MissingServers ( [ServerName] nvarchar(32));
    INSERT INTO #MissingServers
    SELECT [ServerName] FROM
    (SELECT *
    FROM (SELECT [ServerName] FROM #Table1 GROUP BY [ServerName]) AS t_in
     CROSS JOIN #AllDays AS t_allDays) t_All
    WHERE NOT EXISTS
        (SELECT * FROM #table1
         WHERE t_All.date = #Table1.date AND t_All.ServerName = #Table1.ServerName)
    GROUP BY [ServerName]
    -- create string of missing servers
    DECLARE MissingServers_Cursor CURSOR FOR
    SELECT [ServerName] FROM #MissingServers
    OPEN MissingServers_Cursor;
    FETCH NEXT FROM MissingServers_Cursor
    INTO @ServerName;
    DECLARE @MissingServersConcat_String nvarchar(4000) = ''
    SET @DbCount = 1
    WHILE @@FETCH_STATUS = 0
    BEGIN
          PRINT 'Missing Server Name: ' + @ServerName;
          IF (@DbCount <> 1)
          BEGIN
               SET @MissingServersConcat_String = @MissingServersConcat_String + N'{[10911]}'
          END
          SET @DbCount = @DbCount + 1
          IF (@DbCount > 64)
          BEGIN
            CLOSE MissingServers_Cursor;
            DEALLOCATE MissingServers_Cursor;
            RETURN(4)
          END
          SET @MissingServersConcat_String = @MissingServersConcat_String + @ServerName
          FETCH NEXT FROM MissingServers_Cursor
          INTO @ServerName;
     END
     CLOSE MissingServers_Cursor;
     DEALLOCATE MissingServers_Cursor;
     PRINT 'Servers : ' + @MissingServersConcat_String;
     SELECT
        @ServersConcat_String AS Servers,
        @MissingServersConcat_String AS MissingServers
END