December 18, 2016 at 5:23 am
hi,
This is a classic scenario of an SP wrapped in a sqlAgent job and despite SP failing to do its job (say, loading a table), and despite setting "ON FAILURE" to "Quit the job", the sqlAgent job succeeds.
The reason looks like the error number is less significant than sqlAgent to interpret as an error to shutdown the job and it is advised to manually assign an error code between 11-19 to cause a failure.
Could someone explain 1) why a load failure to a table "less significant" incident as viewed by sqlAgent job? 2) How to catch the error code after an EXEC statement and know what number was actually thrown by the executable statement prior to that?
thank you
December 18, 2016 at 5:43 am
etl2016 (12/18/2016)
hi,This is a classic scenario of an SP wrapped in a sqlAgent job and despite SP failing to do its job (say, loading a table), and despite setting "ON FAILURE" to "Quit the job", the sqlAgent job succeeds.
The reason looks like the error number is less significant than sqlAgent to interpret as an error to shutdown the job and it is advised to manually assign an error code between 11-19 to cause a failure.
Could someone explain 1) why a load failure to a table "less significant" incident as viewed by sqlAgent job? 2) How to catch the error code after an EXEC statement and know what number was actually thrown by the executable statement prior to that?
thank you
We need some more information here, can you please elaborate further?
😎
1) What are the severity and state set in the raiserror?
2) Any exception handling in the code, i.e. try/catch?
December 18, 2016 at 6:16 am
thanks.
1) There are 5 steps in the sqlAgent job.
2) Each step has "Quit the job reporting failure" as On failure condition. Each step goes to next step if succeeds, last step quitting with a success message.
3) The generic step definition used in the sqlAgent is as follows
BEGIN TRY
DECLARE Local variables
EXEC Stored_Procedure_EXTRACT_THAT_table
EXEC Stored_Procedure_LOAD_THAT_table (....this is the SP which is failing, but the job continues to execute downstream steps, which is not correct...)
END TRY
BEGIN CATCH
;THROW
END CATCH
3) Neither in the Extract nor in the Load Stored procedure, there is a specific RAISERROR implemented.
thank you
December 18, 2016 at 6:54 am
The TROW statement works similar to the RAISERROR but has the severity level default of 16. Suggest you change the statement to the latter with higher severity level or even remove the try catch altogether.
😎
December 23, 2016 at 1:55 am
I had similar issues myself a few weeks back (SQL SERVER 2008).
Data Flow task would run a stored procedure as the source, get a row count and then create a file. The row count was added due to the behaviour of creating files in SSIS - one will be created even when no results are returned. The next part of the package, if the number of rows was greater than 0 the file was copied into an FTP and then moved into an archive folder otherwise the file was deleted.
The SP had TRY/CATCH blocks and any error was logged to a table, this error handling was causing the package to believe the SP was run successfully when it failed, but no data was returned. I tried adding in RAISEERROR with different severity levels and still the package would run as though the SP had not failed. This meant no files were being produced and job was running "successfully"
Solution:
I was already logging the ETL Start/Finish times into a job log table, so added a SQL task to query the table used to log any SP errors based on the name of the SP and if any errors were present greater than the time the package started populate an SPIsError variable.
Then added a script task component that would check this variable and fail the package if the SP had indeed failed
public void Main()
{
// TODO: Add your code here
if (Dts.Variables["User::SPIsError"].Value.ToString() == "Error")
{
//Dts.TaskResult = (int)ScriptResults.Failure;
Dts.Events.FireError(0, "Script Component", "Package failed due to SP Error. See dbo.SPErrorlog", "See dbo.SPErrorlog", 0);
}
else
{
Dts.TaskResult = (int)ScriptResults.Success;
}
}
Seemed a bit clunky, but it worked if it helps. If anyone else has a simpler solution I'd also be keen to know!
December 27, 2016 at 6:53 am
Hi,
Use a return value in your stored procedure , 0 for success and 1 for failure for example:
create procedure bla
as
select 1/0
if @@error <> 0
return 1
return 0
In your job you can put something like this :
declare @dOuput tinyint
exec @dOuput = bla
select @dOuput
if @dOuput <> 0
select 1 / 0 -- causing the job to fail
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply