Jason L. Froebe: Ramblings of a Geek

– rs_del_exception [xactid] [,xactid]
– no transaction id just list exceptions
– single xactid delete that xact
– range of xactid’s delete the full range of xact

CREATE PROC rs_del_exception
@xacts INT = NULL,
@xacte INT = NULL

AS

DECLARE @systran BINARY(8)
DECLARE @cnt INT, @err INT
DECLARE @ccnt CHAR(8)
DECLARE @cxact CHAR(8)
DECLARE @rsname VARCHAR(30)
DECLARE @cmdcount CHAR(9)
DECLARE @msg VARCHAR(255)
DECLARE @tab_name VARCHAR(30)
DECLARE @row_cnt INT

SET NOCOUNT ON

/* find RS name */

SELECT @rsname = charvalue
FROM   rs_config
WHERE  optionname = "oserver"

/* Build temp table #tab1 */

CREATE TABLE #tab1
(    orig_site VARCHAR(30),
    orig_db   VARCHAR(30),
    orig_user VARCHAR(30),
    orig_time DATETIME,
    error_site VARCHAR(30),
    error_db VARCHAR(30),
    log_time DATETIME,
    reccount INT null,
    sys_trans_id BINARY(8),
    app_usr VARCHAR(30)
)

INSERT INTO #tab1 (orig_site, orig_db,
           orig_user, orig_time,
           error_site, error_db,
           log_time, sys_trans_id,
           app_usr)
SELECT
    orig_site,
    orig_db,
    orig_user,
    orig_time,
    error_site,
    error_db,
    log_time,
    sys_trans_id,
    app_usr
FROM    rs_exceptshdr exh

/* add logged command counts to table */

UPDATE #tab1
  SET reccount = (SELECT MAX(src_cmd_line)
                    FROM   rs_exceptscmd exc
                      WHERE exc.sys_trans_id = #tab1.sys_trans_id
                    GROUP BY exc.sys_trans_id)
/* print summary */

BEGIN
  SELECT @cnt = COUNT(sys_trans_id)
  FROM rs_exceptshdr

  SELECT @ccnt = CONVERT(CHAR(8), @cnt)

  IF (@cnt = 0)
  BEGIN
    PRINT " "
    /* 20500,"         There are 0 Logged Transactions." */
    EXEC rs_get_msg 20500, @msg OUTPUT
    PRINT @msg
    PRINT " "
    RETURN
  END

  PRINT " "
  /* 20501,"         Summary of Logged Transactions on ‘%1!’", @rsname */
  EXEC rs_get_msg 20501, @msg OUTPUT
  PRINT @msg, @rsname
  PRINT " "
  PRINT " "

  IF (CONVERT(INT, 0×0000100) = 65536)
  BEGIN
    SELECT
      "Xact ID" = CONVERT(INT, REVERSE(SUBSTRING(sys_trans_id, 5, 8 ))),
      "Org Site" = SUBSTRING(RTRIM(orig_site)+"."+RTRIM(orig_db), 1, 15),
      "Org User" = SUBSTRING(orig_user, 1, 8 ),
      "Org Date " = CONVERT(CHAR(11), orig_time),
      "Dest Site" = SUBSTRING(RTRIM(error_site)+"."+RTRIM(error_db), 1, 15),
      "# Recs/Xact" = reccount
    FROM #tab1
  END
  ELSE
  BEGIN
    SELECT
      "Xact ID" = CONVERT(INT, SUBSTRING(sys_trans_id, 5, 8 )),
      "Org Site" = SUBSTRING(RTRIM(orig_site)+"."+RTRIM(orig_db), 1, 15),
      "Org User" = SUBSTRING(orig_user, 1, 8 ),
      "Org Date " = CONVERT(CHAR(11), orig_time),
      "Dest Site" = SUBSTRING(RTRIM(error_site)+"."+RTRIM(error_db), 1, 15),
      "# Recs/Xact" = reccount
    FROM #tab1
  END

  PRINT " "
  PRINT " "
  /* 20502,
          "         To Delete a Specific Logged Xact.,
          type ‘rs_del_exception {Xact ID}’"
  */
  EXEC rs_get_msg 20502, @msg OUTPUT
  PRINT @msg
  PRINT " "
END

IF (@xacts = null)
  RETURN 0
 
SELECT @row_cnt = 1

WHILE (@row_cnt > 0)
BEGIN

SET rowcount 1

  IF (@xacte = null)
  BEGIN
    IF (CONVERT(INT, 0×0000100) = 65536)
      SELECT @systran = sys_trans_id
          FROM #tab1
          WHERE CONVERT(INT, REVERSE(SUBSTRING(sys_trans_id, 5, 8 ))) = @xacts
    ELSE
      SELECT @systran = sys_trans_id
         FROM #tab1
         WHERE CONVERT(INT, SUBSTRING(sys_trans_id, 5, 8 )) = @xacts
  END
  ELSE
  BEGIN
    IF (CONVERT(INT, 0×0000100) = 65536)
      SELECT @systran = sys_trans_id
         FROM #tab1
               WHERE CONVERT(INT, REVERSE(SUBSTRING(sys_trans_id, 5, 8 ))) between @xacts and @xacte
    ELSE
      SELECT @systran = sys_trans_id
         FROM #tab1
         WHERE CONVERT(INT, SUBSTRING(sys_trans_id, 5, 8 )) between @xacts and @xacte
  END
 
  SELECT @row_cnt = @@ROWCOUNT

  DELETE #tab1 WHERE @systran = sys_trans_id

  SET rowcount 0

  IF (@row_cnt = 0)
  BEGIN
    PRINT " "
    PRINT "Exceptions deleted"
    PRINT " "
    RETURN
  END

  IF (CONVERT(INT, 0×0000100) = 65536)
    SELECT @cxact = CONVERT(CHAR(8), CONVERT(INT, REVERSE(SUBSTRING(@systran, 5, 8 ))))
  ELSE
    SELECT @cxact =  CONVERT(CHAR(8), CONVERT(INT, SUBSTRING(@systran, 5, 8 )))

  /* if logged transaction exists, delete it. */

  SELECT @cmdcount = RTRIM(CONVERT(CHAR(9), reccount))
  FROM   #tab1
  WHERE  sys_trans_id = @systran

  PRINT " "
  /* 20505," Deleting %1! Commands in Logged Transaction # %2! on ‘%3!’" */
  EXEC rs_get_msg 20505, @msg OUTPUT
  PRINT @msg, @cmdcount, @cxact, @rsname

  BEGIN TRANSACTION

  DELETE  rs_systext
  FROM    rs_exceptscmd exc,
          rs_exceptshdr exh,
          rs_systext sys
  WHERE   exc.sys_trans_id = exh.sys_trans_id
  and     exc.cmd_id = sys.parentid
  and     sys.texttype = "C"
  and     exh.sys_trans_id  =  @systran

  SELECT @err = @@ERROR

  IF (@err != 0)
  BEGIN
    SELECT @tab_name = ‘rs_systext’
    /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
    EXEC rs_get_msg 20506, @msg OUTPUT
    PRINT @msg, @tab_name
    ROLLBACK TRANSACTION
  END
  ELSE
  BEGIN
    DELETE  rs_exceptscmd
    WHERE   sys_trans_id  =  @systran

    SELECT @err = @@ERROR

    IF (@err != 0)
    BEGIN
      SELECT @tab_name = ‘rs_exceptscmd’
      /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
      EXEC rs_get_msg 20506, @msg OUTPUT
      PRINT @msg, @tab_name
      ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN
      DELETE  rs_exceptshdr
      WHERE   sys_trans_id  =  @systran

      SELECT @err = @@ERROR

      IF (@err != 0)
      BEGIN
        SELECT @tab_name = ‘rs_exceptshdr’
        /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
        EXEC rs_get_msg 20506, @msg OUTPUT
        PRINT @msg, @tab_name
        ROLLBACK TRANSACTION
      END
      ELSE
      BEGIN
        COMMIT TRANSACTION

        SELECT @err = @@ERROR

        IF (@err !=0)
        BEGIN
          /* 20509,"Executing ‘commit transaction’  failed. Transaction Rolled Back." */
          EXEC rs_get_msg 20509, @msg OUTPUT
          PRINT @msg
          ROLLBACK TRANSACTION
        END
        ELSE
        BEGIN
          PRINT " "
          /* 20510,"         Logged Transaction # %1! Successfully Deleted.
                Truncate RSSD Transaction Log if Necessary." */
          EXEC rs_get_msg 20510, @msg OUTPUT
          PRINT @msg, @cxact
          PRINT " "
        END
      END
    END
  END
END

go
EXEC sp_procxmode ‘dbo.rs_del_exception’,‘unchained’
go
IF OBJECT_ID(‘dbo.rs_del_exception’) IS NOT NULL
    PRINT ‘< << CREATED PROCEDURE dbo.rs_del_exception >>>’
ELSE
    PRINT ‘< << FAILED CREATING PROCEDURE dbo.rs_del_exception >>>’
go