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/25530

Text File | 2010-06-15 | 2KB | 74 lines

CREATE PROCEDURE [dbo].[ISA_spFlushServerCommitedTables]
    @RemoteRegistryTbl varchar(300)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    BEGIN TRANSACTION
    BEGIN TRY
        -- commit all summary tbls
        CREATE TABLE #RegTbl
        (
            TempTableName varchar(300),
            TableName varchar(300)
        )

        DECLARE @strGetTbls varchar(2000)
        SET @strGetTbls = '
           INSERT INTO #RegTbl
           SELECT *
           FROM [' + @RemoteRegistryTbl + ']'
        PRINT 'Executing: ' + @strGetTbls
        EXEC(@strGetTbls)

        DECLARE @TblName varchar(300)
        DECLARE @TmpTblName varchar(300)
        IF EXISTS (SELECT [TableName] FROM #RegTbl)
        BEGIN
            DECLARE tbl_cursor CURSOR FOR
            SELECT [TempTableName], [TableName]
            FROM #RegTbl

            OPEN tbl_cursor
            FETCH NEXT FROM tbl_cursor INTO @TmpTblName, @TblName
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                EXECUTE ISA_spFlushSingleServerSummary
                       @TempTblName               = @TmpTblName,
                       @TblName                   = @TblName

                FETCH NEXT FROM tbl_cursor INTO @TmpTblName, @TblName
            END
            CLOSE tbl_cursor
            DEALLOCATE tbl_cursor
        END

        COMMIT TRANSACTION
        PRINT 'Successfully commited summaries'
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        PRINT 'Unable to commit summaries'
        PRINT ERROR_MESSAGE()
        DECLARE
            @ErrorMessage    NVARCHAR(4000),
            @ErrorNumber     INT,
            @ErrorSeverity   INT,
            @ErrorState      INT,
            @ErrorLine       INT,
            @ErrorProcedure  NVARCHAR(200);

        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
    END CATCH

END