Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #762

SQL Agent job RAISEERROR roll-up

Show all headers | View raw

I have and SPROC that calls the below via a  cursor so parameters can
be based from a table.

When in run it in SSMS it gives the desired results. Any file that
errors it it continues on to the next which is what I want.

When I take the caller of the below and put it in  a SQL Agent job it
dies on the first error.

Somehow the RAISEERROR percolates to the top of the stack and stops
the job.

IF OBJECT_ID ( 'dbo.usp_PurgeAgedTables', 'P' ) IS NOT NULL
   DROP PROCEDURE dbo.usp_PurgeAgedTables;
GO

CREATE Procedure dbo.usp_PurgeAgedTables
( @I_Table varchar(128), @I_PurgeCol varchar(128),  @I_PurgeRange
varchar(255) )

BEGIN
SET NOCOUNT ON;
DECLARE @V_SQLQuery NVARCHAR(400),
        @V_Count    INT,
        @V_ParameterDefinition NVARCHAR(100);
SET     @V_SQLQuery = 'DELETE FROM ' + @I_Table + ' WHERE ' +
@I_PurgeRange;
--SET     @V_ParameterDefinition = '@V_PurgeRange SMALLINT'
PRINT   @V_SQLQuery;
--PRINT @V_ParameterDefinition;
/* Execute Transact-SQL String */
 BEGIN TRANSACTION;
  BEGIN TRY
     --Remove rows  from table based on Criteria.
     --EXECUTE sp_executesql @V_SQLQuery, @V_ParameterDefinition,
@V_PurgeRange=@I_PurgeRange;
     EXECUTE sp_executesql @V_SQLQuery;
     SELECT @V_count = @@ROWCOUNT
     PRINT 'Rows processed = ' + CAST(@v_count AS VARCHAR(7))

     COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH

     IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION;
      END
      EXECUTE [dbo].[usp_LogError];
      RAISERROR ('Delete in Table above must be reviewed',       --
Message text.
                  16,                                            --
Severity.
                  1                                              --
State.
                 );

  END CATCH;
END;

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

SQL Agent job RAISEERROR roll-up JAW <jwilliam@aglresources.com> - 2011-10-31 11:51 -0700
  Re: SQL Agent job RAISEERROR roll-up Erland Sommarskog <esquel@sommarskog.se> - 2011-10-31 22:43 +0100
    Re: SQL Agent job RAISEERROR roll-up JAW <jwilliam@aglresources.com> - 2011-11-01 07:02 -0700
      Re: SQL Agent job RAISEERROR roll-up JAW <jwilliam@aglresources.com> - 2011-11-01 09:39 -0700
        Re: SQL Agent job RAISEERROR roll-up Erland Sommarskog <esquel@sommarskog.se> - 2011-11-01 22:17 +0100

csiph-web