Sybase Error Message

[morsi]

Hi,
How to get Sybase Error Message in a procedure. With @@error, i can only get the description in master..sysmessages but i want to insert the full message (formatted with the right objects) in a table of log.
Thanks

 

[sybaseguru]

All you need is to write a small stored procedure and pass the @@error and parameter that caused the error . You can also pass your own error messages. Example below calls a stored procedure "cf_error_check_sp" to store the error

-- 
-- Procedure bc_del_TTPeriod_sp
-- 
if exists(select 1 from sysobjects where name = "bc_del_TTPeriod_sp" and type = "P")
begin
   print "dropping Stored Procedure bc_del_TTPeriod_sp"
   drop proc bc_del_TTPeriod_sp
end
go
create proc bc_del_TTPeriod_sp
( 
@TTPeriodId integer = null, 
@DTStamp datetime = null
)
as
begin

declare @proc_id integer,
        @row_count integer,
        @commit_flag bit,
        @err_no integer

select @proc_id = @@procid

if (@TTPeriodId = null)
begin
   exec cf_error_check_sp @err_no = 24002, @arg1 = '@TTPeriodId'
   return(1)
end

if (@DTStamp = null)
begin
   exec cf_error_check_sp @err_no = 24002, @arg1 = '@DTStamp'
   return(1)
end

if (@@trancount = 0)
  begin
    begin transaction SA308
    select @commit_flag = 1
  end
else
  begin
    save transaction SA308
    select @commit_flag = 0
  end

-- Delete the Record

delete TTPeriod
where TTPeriodId = @TTPeriodId and DTStamp = @DTStamp

-- Get the row count and error before they are overwritten

select @row_count = @@rowcount, @err_no = @@error

exec cf_error_check_sp @err_no = @err_no, @proc_id = @proc_id

if @row_count != 1
begin
   -- Abort if parameter does not exist
    rollback transaction SA308
    return(3)
end

-- Everything was Successful
if (@commit_flag = 1)
    commit transaction SA308

return(0)
end
go

Hope this helps

 

[morsi]

What i wnat to get is for exmaple the message :
[Attempt to insert duplicate key row in object 'BOOK' with unique index 'BOOK_PK' ]
like it appear in SqlAdvantage but in sysmessages i found only the template of the message 2601 :
[Attempt to insert duplicate key row in object '%.*s' with unique index '%.*s'%S_EED]