-- 
-- Permission to use, copy, modify and distribute this code for 
-- NON-commercial purposes and without fee is hereby granted provided
-- that this copyright notice appears in all copies.
-- Sybase shall not be liable for any damages as a result of using,
-- modifying or distributing this code.
--
-- Name : readtran.sp
-- Auth.: R.Quakkelaar (Sybase Inc.)
-- Vers.: 1.0
-- Date : November 1996
-- Desc.: With dbcc log the syslogs is read for a specific transaction.
--        Make sure the dbcc traceon(3604) is on before running this
--        stored procedure otherwise the dbcc log won't give any output.
--
-- Date : July 1997
--        Change for exec with parameter op_type. Check sp_readlog
--        for all the op_types possible.

use sybsystemprocs
go

if exists (select 1 from sysobjects 
           where 
                  name = "sp_readtran"
           and    sysstat & 7 = 4)
begin
   print "Dropping procedure sp_readtran"
   drop proc sp_readtran
end
go

print "Installing procedure sp_readtran"
go

create procedure sp_readtran
(
  @page_int    int = null
 ,@row_int     int = null
 ,@only_header int = null
 ,@op_type     int = null
)
as

if suser_id() != 1
begin
   print "You must be SA to run this stored procedure"
   return -1
end

if (@@trancount > 0)
begin
   print "You can not run the procedure within a transaction"
   return -1
end

if (@page_int = null or @row_int = null)
begin
   print "sp_readtran <pageno> , <rowno>, header only <0|1>, op_type"
   return (-1)
end

if (@only_header = null)
   select @only_header = 1

if (@op_type = null)
   select @op_type = -1 -- Display all records

set nocount on

-- Get the right permissions for dbcc log command

exec  sp_role 'grant', 'sybase_ts_role', sa
      set role         'sybase_ts_role' on

declare @page_bin   binary(4),
        @row_bin    binary(2),
        @id_int     int,
        @rec_num    int,
        @xactid_val varbinary(6)

select @xactid_val = convert(varbinary(4), @page_int) +
       convert(varbinary(2), convert(smallint, @row_int) )


if not exists (select * from syslogs 
               where xactid = @xactid_val)
begin
   print "Transaction with id : %1! does not exist",  @xactid_val
   return (-1)
end


select @id_int   = db_id()
select @rec_num  = (select count(*) from syslogs
                      where xactid = @xactid_val)

--Make sure that the dbcc traceon(3604) is on when you are 
--running this stored procedure.

dbcc log( @id_int, 1, @page_int, @row_int, 0, @op_type, @only_header)

print  ""
select "   xactid"     = @xactid_val,
       "  db id"       = @id_int,
       "  page id"     = @page_int,
       "  row id"      = @row_int,
       "total records" = @rec_num
return
go

grant execute on sp_readtran to public
go