I have a maintenance proceudre that trims data tables on the live site, but prior to trimming i need to execute procs via linked servers at four seprate locations (subscribers) to ensure all of their data is up to date before the trim happens (the deletes are blocked at the subs). my problem is that if i go
exec @error_id = procname
and an error such as permission being denied or proc missing, the return code
is still 0 - therefor it looks like th proc ran successfully, meanwhile it didnt.
does anyone know how to check for these sorts of erors, so i dont leave this to chance?
I would reccommend against using the return value though...let sql manage that value
SET NOCOUNT ON
CREATE PROC mySproc99 (@CondCode int OUTPUT)
DECLARE @rc int
SET @CondCode = 0
SELECT @rc = @@ERROR
IF @rc <> 0
SELECT @CondCode = -1
RAISERROR 52001 'Error'
DECLARE @rc int, @CondCode int
EXEC @rc = mySproc99 @CondCode OUTPUT
SET @rc = @@ERROR
SELECT '@rc = ' + CONVERT(varchar(15),@rc), '@CondCode = ' + CONVERT(varchar(15),@CondCode)
SET NOCOUNT OFF
DROP PROC mySproc99