Sybase ASE: A simple way to let users restore a database using a stored procedure

I wrote this simple method to restore a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related: Sybase ASE: A simple way to let users restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_load_userdb")
	drop procedure sp_load_userdb
go

CREATE PROCEDURE dbo.sp_load_userdb
@dbName varchar(100) = NULL, 
@DAYofMonth smallint = NULL
AS 
BEGIN
	if (@dbName is NULL or not exists select 1 from (master..sysdatabases where name = @dbName))
	begin
		print "Please specify a database and day of month to restore from."
		print "sp_load_userdb d1folio1, 15"
	end
	else
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if (@DAYofMonth is NULL or @DAYofMonth < 1 or @DAYofMonth > 31)
	begin
		DECLARE @cmd varchar(255)

		print "Dump file not found!"
		select @cmd = "find /some_dir/user_backups -name " + @dbName + "_*" + " -type f -exec basename {} \;"
		print @cmd
		exec xp_cmdshell @cmd
	end
	else
	begin
		if (db_name() = "master")
		begin
			DECLARE @dbNamePath varchar(255)

			exec kill_user_connections @dbName
			select @dbNamePath = "/some_dir/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
			load database @dbName from @dbNamePath
			online database @dbName
		end 
		else
			print "Please run sp_load_userdb from the master database."
	end 
END
go
exec sp_load_userdb jf_test, 5
-----------------------------------------------------------------------
There is no user connections in database jf_test

------------------------------------------------------------------------------------
Please log out if you currently connected to database jf_test
Backup Server session id is: 68. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/somedir/jf_test_5.dmp'

(1 row affected)
Backup Server: 4.188.1.1: Database jf_test: 25480 kilobytes (4%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 53770 kilobytes (10%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 98316 kilobytes (18%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 141328 kilobytes (27%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 184848 kilobytes (35%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 227860 kilobytes (43%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 271380 kilobytes (52%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 314392 kilobytes (60%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 357912 kilobytes (69%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 387100 kilobytes (74%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 430620 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 473120 kilobytes (91%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 516640 kilobytes (99%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518178 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518186 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database jf_test).
Caution:  You have set up this database to include space on disk 36 for both data and the transaction log.  This can make recovery impossible if that disk fails.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Started REDO pass for database 'jf_test'. The total number of log records to process is 2.
Completed REDO pass for database 'jf_test'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Recovery of database 'jf_test' will undo incomplete nested top actions.
Database 'jf_test' is now online.
(return status = 0)
exec sp_load_userdb
Please specify a database and day of month to restore from.
sp_load_userdb d1folio1, 15
(return status = 0)
Share Button

Ed Barlow’s sp__helprotect updated for SAP Sybase ASE 15.7 Solved

I needed to copy the permissions from one database to another but the sp__helprotect stored procedure in Ed Barlow’s Extended System Stored Procedure Library for Sybase and Sql Server wasn’t producing output with “GRANT” and “REVOKE”. It also had problems with DBCC privileges. Here you go:

sp__helprotect.sql

/* Procedure copyright(c) 1995 by Edward M Barlow */
/* Updated for v15.7 by Jason Froebe */

/******************************************************************************
**
** Name : sp__helpprotect.sql
**
******************************************************************************/
if exists (select * from sysobjects
where name = “sp__helprotect”
and type = “P”)
begin
drop proc sp__helprotect
end
go

create procedure sp__helprotect
@parameter varchar(30) = NULL /* name of object or user to check */,
@do_system_tables char(1) = null, /* if not null will include system tbls */
@dont_format char(1) = null,
@groups_only char(1) = null
as
declare @type char(2), @uid int, @msg varchar(255), @objid int

if @parameter is NULL
select @objid=null
else
select @objid = object_id(@parameter)

/* define our table */
select id,uid,action,protecttype,columns,grantor,
column_name = ” ”
,action_text = ” ”
,protecttype_text = ” ”
,ending = ” ”
into #protects
from sysprotects
where 1=2

/* Either a passed object or all objects */
if @objid is not null or @parameter is null
begin

select uid,gid into #groups from sysusers

if @groups_only is not null
delete #groups
where uid != gid

/* IT IS AN OBJECT */
insert #protects
select id,p.uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p, #groups g
where id=isnull(@objid,id)
and p.uid = g.uid

/* REVOKES ON COLUMNS */
insert #protects
select id,p.uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”,””,””,””
from sysprotects p, master.dbo.spt_values c, #groups g
where p.columns is not null
and convert(tinyint,substring(p.columns,c.low,1)) & c.high=0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and id=isnull(@objid,id)
and p.uid=g.uid

if @do_system_tables is null and @objid is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end
else
begin

/* IS IT A USER */
select @uid = uid from sysusers where name=@parameter
if @@rowcount = 0 or @uid is null
begin
print “No User Or Object Found”
return (1)
end

insert #protects
select distinct id,uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p
where uid=@uid
/* and isnull( p.columns,0x01 ) = 0x01 */

/* REVOKES ON COLUMNS */
insert #protects
select id,uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”, “”,””,””
from sysprotects p, master.dbo.spt_values c
where isnull( p.columns,0x01 ) != 0x01
and convert(tinyint, substring(p.columns, c.low, 1)) & c.high = 0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and uid=@uid

if @do_system_tables is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end

/* References etc */
delete #protects
where action in(151,207,222,233,236)

update #protects
set action_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.action

update #protects
set protecttype_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.protecttype +204

— protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
update #protects
set protecttype_text =
case
when protecttype = 0
then “GRANT”
when protecttype = 1
then “GRANT”
when protecttype = 2
then “REVOKE”
end

update #protects
set ending = ” WITH GRANT OPTION”
where protecttype = 0

declare @max_len int
select @max_len = max(char_length( rtrim(protecttype_text)+” “+rtrim(action_text)+” on “+rtrim(object_name(id))+column_name+” to “+rtrim(user_name(uid))+ending))
from #protects

if @max_len < 60 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,59) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 80 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,79) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 132 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,131) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text return (0) go grant execute on sp__helprotect to public go[/sql]

Share Button

ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs

The ASE 15 stored procedure sp_tables doesn’t work with SQL UDFs. sp_tables has the udfs coming back as system tables.

repro:
1) create a sql udf in a db
2) run sp_tables, you will see the UDF as a “SYSTEM TABLE”

DBADEV1.sybase_dba.1> sp_tables;
table_qualifier table_owner table_name table_type remarks
--------------- ----------- ---------- ---------- -------
sybase_dba dbo DATE_FORMAT SYSTEM TABLE NULL

In the above example, the DATE_FORMAT UDF is the one at Porting MySQL’s date_format function to Sybase ASE 15.0.2

I’ve opened a case with Sybase to get this fixed – I’ll post the CR # when I get it.

UPDATE:  Sybase assigned CR 497173 to this 🙂

Share Button

Replication Server Exception List Deleter (updated)

As you may remember, my good friend Ken Rearick created an excellent stored procedure, rs_del_all_exception, that safely clears out old exceptions from the RSSD database. Ken has just sent an updated version to me. B-)

IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.rs_del_exception
    IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
        PRINT '< << FAILED DROPPING PROCEDURE dbo.rs_del_exception >>>'
    ELSE
        PRINT '< << DROPPED PROCEDURE dbo.rs_del_exception >>>'
END
go

-- 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, 0x0000100) = 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, 0x0000100) = 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, 0x0000100) = 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, 0x0000100) = 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

Download: rs_del_exception stored procedure

Share Button

Should “return” reset @@error?

Drew Montgomery brought the fact that Sybase ASE’s sp_password stored procedure raises the appropriate error message if it is unable to update the password of a login then returns "1" indicating a failure:

 /*
**  Encrypt and store the input @new_password.
**  @caller_password will be checked against the password of the caller.
**  set_password() builtin will print out nice messages.
*/
select @returncode = set_password(@caller_password, @new_password, @loginame, @immediate)
if (@returncode = 0)
begin
        /*
        ** 17720, "Error:  Unable to set the Password."
        */
        raiserror 17720
        return (1)
end
else
begin
        /*
        ** 17721, "Password correctly set."
        */
        exec sp_getmessage 17721, @msg output
        print @msg
        return (0)
end

Normally this would be fine but if you write a stored procedure wrapper for sp_password (or another ASE stored procedure), it is impossible to trap the exact error message from within your wrapper stored procedure.  The problem is that @@error is being reset by the return() operation, indicating that the return(), itself, resulted in no error.

I know this is the way it is now, but should it remain so?  I mean, should the execution of return() reset @@error if called in a stored procedure?

Granted, Sybase could probably correct their code by return(@@error) instead of returning a generic ‘1’ but is this a design flaw of the stored procedures or Sybase’s implementation of T-SQL?

I have to admit that I’m on the fence as to where the flaw resides other than it is a ‘Sybase issue’.

Share Button

Eliminating Temp Table Usage in sp__diskdevice

The sp__diskdevice code from Ed Barlow was written 11 years and there are a number of issues that need addressing:

  1. Unnecessary use of temporary tables (#tablename) that can lead to lock contention in the system tables tempdb..sysobjects, tempdb..sysindexes, and tempdb..syscolumns.
  2. It doesn’t handle the logical page size (2k, 4k, 8k, 16k) in an intuitive manner.

For those of you that don’t know, the system variable @@pagesize is the physical page size, which is always 2K.  The @@maxpagesize is the logical page size (2K, 4K, 8K, 16K).  The column size in the master..sysusages table is specified in logical pages while the low and high columns of the master..sysdevices table is specified in physical pages.

Original Code:

/ * Procedure copyright(c) 1995 by Edward M Barlow */
/************************************************************************\
|* Procedure Name:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
   from   sysobjects
   where  type = "P"
   and  name = "sp__diskdevice_old")
begin
drop proc sp__diskdevice_old
end
go
create procedure sp__diskdevice_old (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1 and v.type = "E"
create table #dev_tbl
(
  name  char(30),
  phyname    char(255),
  disk_size float  null,
  status   int  null,
  disk_used float  null,
  mirrored   char(1)  null
)
insert  #dev_tbl
select  name=d.name,
    phyname = d.phyname,
    disk_size=0,
    status=status,
    disk_used=0,
    mirrored=NULL
  from master.dbo.sysdevices d
  where name=isnull(@devname,name)
/* Parallel */
update #dev_tbl
  set mirrored="P"
  where status & 64 = 64
/* Serial */
update #dev_tbl
  set mirrored="S"
  where status & 32 = 32
/* Disabled */
update #dev_tbl
  set mirrored="?"
  where status & 256 = 256
/* Confused */
update #dev_tbl
  set mirrored="?"
  where status & 32 = 32
    and   status & 512 != 512
/*  Add in its size in MB.  */
update #dev_tbl
  set disk_size = (1. + (d.high - d.low)) / @numpgsmb
  from master.dbo.sysdevices d, #dev_tbl
  where d.status & 2 = 2
    and #dev_tbl.name = d.name
update #dev_tbl
  set disk_used = ( select sum(size)
  from master.dbo.sysusages u, master.dbo.sysdevices d
  where d.status & 2 = 2
    and vstart between low and high
    and #dev_tbl.name = d.name
  group by name ) / @numpgsmb
update #dev_tbl
  set name=rtrim(name)+" ("+mirrored+")"
  where mirrored is not null
/*
if @devname is null
begin
  if @dont_format is not null
  begin
    print ""
    print "****** PHYSICAL DISK DEVICES (Mirror info after device name) ******"
  end
end
*/
update #dev_tbl set disk_used=0 where disk_used is null
if @dont_format is not null
  select
      "Device Name"=name,
      "Physical Name"=phyname,
      size=str(disk_size,7,1)+"MB",
      alloc=str(disk_used,7,1)+"MB",
      free=str(disk_size-disk_used,7,1)+"MB"
  from  #dev_tbl
  where status & 2 = 2
else
  select
      "Device Name"=substring(name,1,18),
      "Physical Name"=substring(phyname,1,31),
      size=str(disk_size,6,0)+"MB",
      alloc=str(disk_used,6,0)+"MB",
      free=str(disk_size-disk_used,6,0)+"MB"
  from  #dev_tbl
  where status & 2 = 2
if @devname is not null
begin
  if exists (select  *
        from master.dbo.sysdatabases d, master.dbo.sysusages u,
          master.dbo.sysdevices dv
        where d.dbid = u.dbid
        and dv.low < = size + vstart
        and dv.high >= size + vstart - 1
        and dv.status & 2 = 2
        and dv.name=@devname
  )
  begin
       select  db_name=d.name,
            size = size / @numpgsmb,
            usage = convert(char(18),b.name)
       from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
            master.dbo.spt_values b
       where d.dbid = u.dbid
            and dv.low < = size + vstart
            and dv.high >= size + vstart - 1
            and dv.status & 2 = 2
            and b.type = "S"
            and u.segmap & 7 = b.number
            and dv.name=@devname
       order by db_name,usage
  end
  else
  begin
      if @dont_format is not null
        print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice_old to public
go
exit

No more temp table code:

/* Procedure copyright(c) 1995 by Edward M Barlow */
/************************************************************************\
|* Procedure Name:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
     from   sysobjects
     where  type = "P"
     and  name = "sp__diskdevice")
begin
  drop proc sp__diskdevice
end
go
create procedure sp__diskdevice (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
if @devname is null
begin
  select d.name as "Device Name",
    d.phyname as "Physical Name"
  from master.dbo.sysdevices d
    where d.status & 2 != 2
end
select
  case
  when (@dont_format is null)
    then
    substring(d.name, 1, 18)
  else
    d.name
  end as "Device Name",
  case
  when (@dont_format is not null)
    then
    substring(d.phyname, 1, 31)
  else
    d.phyname
  end as "Physical Name",
  convert(varchar(7),
  convert(int, ((1. + (d.high - d.low))*@@pagesize) / 1048576.)
  ) + "MB" as "size",
  convert(varchar(7),
  (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  ) + "MB" as "alloc",
  convert(varchar(7),
  convert(int,
    ( ((1. + (d.high - d.low))*@@pagesize) / 1048576.) -
    (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  )
  ) + "MB" as "free"
from master.dbo.sysdevices d
where d.status & 2 = 2
  and name=isnull(@devname,name)
if @devname is not null
begin
  if exists (select  *
    from master.dbo.sysdatabases d, master.dbo.sysusages u,
      master.dbo.sysdevices dv
    where d.dbid = u.dbid
    and dv.low < = size + vstart
    and dv.high >= size + vstart - 1
    and dv.status & 2 = 2
    and dv.name=@devname
  )
  begin
     select  db_name=d.name,
      size =  convert(int, u.size / (1024 / (@@maxpagesize/1024))),
      usage = convert(char(18),b.name)
     from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
      master.dbo.spt_values b
     where d.dbid = u.dbid
      and dv.low < = size + vstart
      and dv.high >= size + vstart - 1
      and dv.status & 2 = 2
      and b.type = "S"
      and u.segmap & 7 = b.number
      and dv.name=@devname
     order by db_name,usage
  end
  else
  begin
    if @dont_format is not null
    print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice to public
go
exit
Share Button

FW Ken Rearick: repserver exception list/deleter

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

Share Button