resend content sql

Mass Resend Reports

David Kurtz has a great post on resending reports in a "Not Posted" status. The SQL he provides works on Oracle. The SQL below will do the same on MS SQL Server.

DECLARE @PRCSINSTC INT;

DECLARE PROCESSINSTANCE_CURSOR CURSOR FOR
    SELECT PRCSINSTANCE
    FROM   PSPRCSRQST
    WHERE  DISTSTATUS = 4 --THAT ARE NOT POSTED
    AND    RUNSTATUS = 9;  --SUCCESSFUL PROCESSES

OPEN PROCESSINSTANCE_CURSOR
FETCH NEXT FROM PROCESSINSTANCE_CURSOR INTO @PRCSINSTC
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @PRCSINSTC
         UPDATE PSPRCSRQST
         SET    DISTSTATUS = '7'
         WHERE  PRCSINSTANCE = @PRCSINSTC;

         UPDATE PSPRCSQUE
         SET    DISTSTATUS = '7'
         WHERE  PRCSINSTANCE = @PRCSINSTC;

         UPDATE PS_CDM_LIST
         SET    DISTSTATUS   = '8'
         ,      TRANSFERINSTANCE = 0
         WHERE  PRCSINSTANCE = @PRCSINSTC
         AND    DISTSTATUS   <> '5'; --POSTED
        FETCH NEXT FROM PROCESSINSTANCE_CURSOR INTO @PRCSINSTC
    END;
CLOSE PROCESSINSTANCE_CURSOR;
DEALLOCATE PROCESSINSTANCE_CURSOR;
GO