Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    Unanswered: Return code from exec

    Hi
    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?

    thnks
    des

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    no prob

    did a workaround, so is fine, but anyone who knows the answer, would be good for next time...
    des

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    select @@error -- 0
    exec myproc
    select @@error -- 2812 on a 'Could not find stored procedure 'myproc'.'

    use @@error before checking the proc's return value.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There's a couple of ways...

    I would reccommend against using the return value though...let sql manage that value

    Code:
    USE Northwind
    GO
    SET NOCOUNT ON
    GO
    
    CREATE PROC mySproc99 (@CondCode int OUTPUT)
    AS
    	DECLARE @rc int
    	SET @CondCode = 0
    	EXEC mySproc00
    	SELECT @rc = @@ERROR
    	IF @rc <> 0
    	  BEGIN
    		SELECT @CondCode = -1
    		RAISERROR 52001 'Error'
    	  END
    	RETURN
    GO
    
    DECLARE @rc int, @CondCode int
    
    EXEC @rc = mySproc99 @CondCode OUTPUT
    SET @rc = @@ERROR
    SELECT '@rc = ' + CONVERT(varchar(15),@rc), '@CondCode = ' + CONVERT(varchar(15),@CondCode) 
    GO
    
    SET NOCOUNT OFF
    DROP PROC mySproc99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    nice one - thanks...didnt realise to set to @@error AFTER, figured it got it
    as the exec =

    cheers.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SQL Server error handling (what a loosley defined term) is done many ways...as you have seen

    @@ERROR
    RETURN
    RAISERROR

    Are the ways I've seen...and they all may be set by different reasons....

    I still have to update this to eliminate the use of the RETURN values...you should just let sql manage that...

    http://weblogs.sqlteam.com/brettk/ar...5/25/1378.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •