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;

JAW (jwilliam (AT) aglresources (DOT) com) writes:
This seems familiar; When Agent notices that the job raises and error, it
sends an attention signal to SQL Server to cancel execution. I don't know
of any way to change this behaviour. I think your best bet is to log the
errors in a table, and then raise an error at the end.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Oct 31, 5:43*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Changing the RAISERROR to PRINT gives the desired result though I
don't consider it clean programming.

I have going to try changing the serverity level next.

A TRY block must be immediately followed by a CATCH block.

TRY…CATCH constructs can be nested. This means that TRY…CATCH
constructs can be placed inside other TRY and CATCH blocks. When an
error occurs within a nested TRY block, program control is transferred
to the CATCH block that is associated with the nested TRY block.

To handle an error that occurs within a given CATCH block, write a
TRY…...CATCH block within the specified CATCH block.

Errors that have a severity of 20 or higher that cause the Database
Engine to close the connection will not be handled by the TRY…CATCH
block. However, TRY…CATCH will handle errors with a severity of 20 or
higher as long as the connection is not closed.

Errors that have a severity of 10 or lower are considered warnings or
informational messages, and are not handled by TRY…CATCH blocks.

Attentions will terminate a batch even if the batch is within the
scope of a TRY…CATCH construct. This includes an attention sent by the
Microsoft Distributed Transaction Coordinator (MS DTC) when a
distributed transaction fails. MS DTC manages distributed transactions

On Nov 1, 10:02*am, JAW <jwill... (AT) aglresources (DOT) com> wrote:


If you set the serverity to 10 it works as expected also since 10 is
treated as a informational error..

JAW (jwilliam (AT) aglresources (DOT) com) writes:
Actually, severity 10 is converted to severity 0, so RAISERROR with level
10, is just a complicated way of saying PRINT...

As I said, log the error to a table, and the raise an error at the end of
the job.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx