My good friend Ken Rearick wrote a rather clever little stored procedure for managing all the exceptions that are raised in the life of a Sybase Replication Server. He posted this on sybase.public.rep-server a few days ago:
This is a rewriten rs_delexception script that will list out all exceptions and then step through deleting all of them. As this uses the same procedure for deleting the entries it does not cause any problems with integerity in the RSSD — Ken Rearick
IF OBJECT_ID(’dbo.rs_del_all_exception’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.rs_del_all_exception
IF OBJECT_ID(’dbo.rs_del_all_exception’) IS NOT NULL
PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.rs_del_all_exception >>> >>>’
ELSE
PRINT ‘<<< DROPPED PROCEDURE dbo.rs_del_all_exception >>>’
END
go
create proc rs_del_all_exception
@xact 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_delexception {Xact ID}’" */
exec rs_get_msg 20502, @msg output
print @msg
print " "
end
select @row_cnt = 1
while (@row_cnt > 0)
begin
set rowcount 1
select @systran = sys_trans_id from #tab1
select @row_cnt = @@rowcount
delete #tab1 where @systran = sys_trans_id
set rowcount 0
if (@row_cnt = 0)
begin
print " "
print "All exceptions deleted"
print " "
return
end
if (convert(int, 0×0000100) = 65536)
begin
select @cxact = convert(char(8), convert(int,
reverse(substring(@systran, 5, 8))))
end
else
begin
select @cxact = convert(char(8), convert(int, substring(@systran, 5, 8)))
end
/* 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
print " "
print " "
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_all_exception’,'unchained’
go
IF OBJECT_ID(’dbo.rs_del_all_exception’) IS NOT NULL
PRINT ‘<<< CREATED PROCEDURE dbo.rs_del_all_exception >>>’
ELSE
PRINT ‘<<< FAILED CREATING PROCEDURE dbo.rs_del_all_exception >>>’
go
Recent Comments