Error handling/notification with a sql agent job

Question

Post reply

Error handling/notification with a sql agent job

Lee Hopkins

SSC Eights!

Points: 960
More actions
November 18, 2021 at 6:03 pm
Go to Answer
#3954546

I have a stored procedure that is returning a set of data from a table and then is populating a summary table with that data. if sussful I am deleting all the data from the first table. if there is an error I do not want to delete the data and notify me if it fails. I am using a try-catch on the stored proc so if the insert fails it falls to the catch. in the SQL agent job I know, I can set a notification on failure. Will the SQL agent job detect my catch as a failure?

Phil Parkin

SSC Guru

Points: 247159
More actions
November 18, 2021 at 7:25 pm
Answer
#3954575
That won't do it. When processing is transferred to your CATCH block, your SELECT merely returns the error message and the error is swallowed. I suggest the following slight modification:
```
begin catch
        SELECT  ERROR_MESSAGE() AS ErrorMessage;
        THROW;
END catch
```
This will ensure that the calling application (SQL Agent) knows about the error.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247159 More actions · Answer 1

It depends. Can you show us what is in your CATCH block?

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 2

Create procedure [dbo].[TO_POPULATE_ISD_MonthEndDetail] AS
begin Try

insert into [dbo].[U5TO_ISD_MONTHENDDETAIL] ([MOD_DOCID], [MOD_STORENO], [MOD_STOREORG], [MOD_AIMDATE], [MOD_PARTCOUNT], 
[MOD_PRT], [MOD_QTY], [MOD_AVP], [MOD_BATCHID], [MOD_CREATEDATE], [MOD_MONTHYEAR])

SELECT
    [ISD_DOCID], [ISD_STORENO], [ISD_STOREORG], [ISD_AIMDATE], [ISD_PARTCOUNT], [ISD_PRT], [ISD_QTY], [ISD_AVP], [ISD_BATCHID], [ISD_CREATEDATE], 
RIGHT('00' + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2))  , 2) + '/'+ CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR(4)) [MONTHYEAR]
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY [ISD_STOREORG] ORDER BY ISD_CREATEDATE DESC) AS rank_num
    FROM [U5TO_InventoryStoreDetails]
) AS derived1
WHERE rank_num = 1

end try
begin catch

        SELECT  ERROR_MESSAGE() AS ErrorMessage;  

END catch

Lee Hopkins SSC Eights! Points: 960 More actions · Answer 3

Thank you, i forgot about Throw (just like in C#) I am a C# developer and so so on SQL

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 4

I would suggest putting all of the logic in the one procedure - instead of separate steps in an agent job. Start a transaction, perform your insert and delete in a try/catch, commit after the delete. If it fails, perform a rollback in the catch and throw an error so the agent job fails and notification is sent on failure.

Another option - slightly different, would be to perform the delete first using the OUTPUT parameter into a temp table (or table variable) and then insert into your monthly table from the temp table. You would still wrap this all in a transaction and rollback on failure...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Error handling/notification with a sql agent job

Cookies on SQLServerCentral