WSUS Cleanup / Loop through a temp table and execute a stored procedure?

1

Evening all,

I am trying to clean up a WSUS DB, there are 2 stored procedures to help
exec spGetObsoleteUpdatesToCleanup
and
exec spDeleteUpdate @localUpdateID=xxxxxx

I'm trying to automate this a little so I wrote a little script to create a temp table, put the results from the first stored procedure in that then loop through the temp table running the second stored procedure against every entry...

The first part works, the second part doesn't appear to, anything glaringly obvious that I've done wrong...? Oh, and is it possable to make it have some sort of out put so I know how / what its doing...?

Many thanks

Dave

Code:
CREATE TABLE #MyTempTable(LocalUpdateID INT)

    INSERT INTO #MyTempTable EXECUTE spGetObsoleteUpdatesToCleanup
    SELECT * FROM #MyTempTable

    GO

    DECLARE @MyUpdateID int
    DECLARE MyCur CURSOR FOR SELECT LocalUpdateID FROM #MyTempTable
    OPEN MyCur
    FETCH NEXT FROM MyCur INTO @MyUpdateID
    WHILE @@FETCH_STATUS = 0 BEGIN
        exec spDeleteUpdate @localUpdateID=@MyUpdateID
        FETCH NEXT FROM MyCur INTO @MyUpdateID
    END
    CLOSE MyCur    
    DEALLOCATE MyCur
2

Just to be safe, let's make sure the cursor is a LOCAL one:

DECLARE @MyUpdateID int
DECLARE @ReturnCode int
DECLARE MyCur CURSOR LOCAL FAST_FORWARD FOR SELECT LocalUpdateID FROM #MyTempTable
OPEN MyCur
FETCH NEXT FROM MyCur INTO @MyUpdateID
WHILE @@FETCH_STATUS = 0 BEGIN
    print 'EXECing proc spDeleteUpdate with id value = ' + CAST(@MyUpdateID AS varchar(10))
    exec @ReturnCode = spDeleteUpdate @localUpdateID=@MyUpdateID
    IF @ReturnCode <> 0 BEGIN
        RAISERROR('Proc spDeleteUpdate got error %d when processing id %d.', 16, 1,
            @ReturnCode, @MyUpdateID )
    END /*IF*/
    FETCH NEXT FROM MyCur INTO @MyUpdateID
END
CLOSE MyCur    
DEALLOCATE MyCur
3

Hiya,

Thanks for your reply, yup looks good to me, seems to be working. One little glitch, the message that tells you which one is executing doesn't output until its finished, they all output at the same time. Any way to make it do a running comentary?

EDIT:

Cancel that, looks to be working fine, just a little slow to update. Thanks Scott.

Thanks

Dave

4

You can use RAISERROR with a severity of 0 and the WITH NOWAIT option to help the output to the screen along instead of just using a PRINT or similar output.

5

If you are not familiar with CURORS and their vagaries I think it can often be better to just use a loop. It helps if you define the #TEMP table to have an IDENTITY column - lets call it [T_ID], but any Primary Key / Unique Column will do

DECLARE @T_ID int = 0, @RowCount int=1 -- Force first iteration

WHILE @RowCount >= 1
BEGIN
    SELECT TOP 1 @T_ID = T_ID,
                 @MyUpdateID = LocalUpdateID
    FROM #MyTempTable
    WHERE T_ID > @T_ID
    ORDER BY T_ID
    SELECT @RowCount = @@ROWCOUNT

    IF @RowCount = 1
    BEGIN
        exec @ReturnCode = spDeleteUpdate @localUpdateID=@MyUpdateID
        IF @ReturnCode <> 0
        BEGIN
             ... Error Handling Etc.
        END
    END
END

Cursor may perform faster in some situations, but not enough to make a difference for either small batches or one-of-jobs.

6

SQL is buffering the results, i.e., output is held until it reaches a certain size (fills the buffer), then SQL outputs it all at once. This is for efficiency. Afaik, the same occurs even if you specify RAISERROR WITH NOWAIT.

7

I get debug data appearing using RAISERROR WITH NOWAIT which does not appear (in a timely fashion) using PRINT, and thus use RAISERROR in that way for that purpose when PRINT is not outputting sooner enough.

But whether RAISERROR is "instant" or just "more sooner-ish" :slight_smile: I don't know.

RAISERROR (and PRINT for that matter) can muck up APP code that is not expecting to get that "stuff" in the flow of packets from the server. We use an SProc @DEBUG parameter to turn on/off all debug messages