• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

How to handle the error when a step in the SQL job fails

I created a job with about 4 steps that process data and uses ftp to send data to our customer.

If the job fails during any step , I want to log that into  a table in the database with the error which caused the step to fail.

0
Bkuniyil
Asked:
Bkuniyil
  • 3
  • 3
1 Solution
 
dbaSQLCommented:
I would just create an Error table to house the error functions, and insert them from the TRY/CATCH, similar to what's in this link:
http://www.sqlservernation.com/home/using-trycatch-in-sql-server.html


even better, the uspLogError procedure writes error details into the ErrorLog table.  you can use this within each of your jobs steps as the action upon failure.  then you can send an email with the error_line or error_number, with the 'JobName Failure' in the subject ine.

http://msdn.microsoft.com/en-us/library/ms179296.aspx
0
 
BkuniyilAuthor Commented:

  Actually the job step does not execute a stored procedure. It executes an SSIS package.  When the package returns with an exit code other than 0 it is a failure. Hence the job fails . I need to trap this error and log into the database.

Experts, could you please help me in achieving this.
0
 
lcohanDatabase AnalystCommented:
Assuming it's a SQL job you can do that directly in the job step under the "Advanced" like in the attached picture - either log to a table and/or include step output in the history and you can also send emil notifications on failure. If you need to log from the batch (cmd file) that does the FTP then you may need additional code and run SQL command shell to populate the table.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
lcohanDatabase AnalystCommented:
Ok, in that case the SSIS step that is executed need to include a BEGIN/TRY/CATCH set of SQL commands as "dbasql" suggested. Also you can configure the SSIS to do the Logging including in the Event long on that server.

Integration Services Log Providers

http://msdn.microsoft.com/en-us/library/ms140246(v=sql.90).aspx
0
 
BkuniyilAuthor Commented:

   The requirement is to log the error in the table created in the database.  I guess the "Log to table"  option under the Notifactions  does not help me here.  Also the SSIS package is used to execute a batch file , so i would not be able to include a  TRY/CATCH there.
0
 
lcohanDatabase AnalystCommented:
Of course you can easily do that - from the batch file you execute commands against SQL to populate that table or anything else via SQLCMD utility:

sqlcmd Utility Tutorial
http://msdn.microsoft.com/en-us/library/ms170207(v=SQL.90).aspx
0
 
BkuniyilAuthor Commented:
The solution partially resolved my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now