I have complete error handling and printing the error after every insert or update statements or after calling another procedure.
But somehow when executing the proc it is not printing the error.
The query analyzer shows a general message 'Query batch completed with errors'
All the logic seems to be working properly, but this message is bothering me. Why is this message displayed if everything is run correctly [or] is something wrong ?
Example:
[code]
/*************************************************************
** Error Handling
**************************************************************/
SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3
IF (@.error <> 0)
BEGIN
EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error
PRINT 'ERROR=' + convert(varchar(255),@.error)
ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure
END
[/code]
IF (@.@.error <> 0) --this should be @.@.error @.error from previous statement is unreliable
BEGIN
EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error
PRINT 'ERROR=' + convert(varchar(255),@.error)
ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure
END
|||
Not quite getting it.
Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error
In other words, the following doesn't work ?
declare @.error int
select @.error = @.@.error
if (@.error <> 0)
begin
end
|||
QUOTED:
Not quite getting it.
Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error
In other words, the following doesn't work ?
declare @.error int
select @.error = @.error + (other select clause ) --<-- what if the error lies in here
if (@.error <> 0)
begin
end
|||
thanks joeydj,
ok i see...
@.@.error is for select statements too ?
i can check the selects, but its a standard select as shown above and there seems to be no error there.
|||
thats a wild guess anyway.
|||
can you please check if this line is valid
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
|||
still not getting it try this. this one should do it.
hahaha
declare @.error int
select @.error=0
SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3
IF (@.error <> 0)
BEGIN
EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error
PRINT 'ERROR=' + convert(varchar(255),@.error)
ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure
END
