Error handling in SQL Job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error handling in SQL Job

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-12 : 08:07:16

Pablo writes "Hello SQL Team,

When I run the following code using Query Analyzer:

---------------------------------------------------------------
EXEC xp_startmail
PRINT "HELLO"
EXEC xp_sendmail @recipients = 'wrong recipient!!!', @message="HOLAAAAAAA"
PRINT "GOODBYE"
EXEC xp_stopmail
--------------------------------------------------------------

I get the following output:

--------------------------------------------------------------
SQL Mail session started.
HELLO
Server: Msg 17914, Level 18, State 1, Line 0
Unknown recipient: Parameter '@recipients', recipient 'wrong recipient!!!'
GOODBYE
Stopped SQL Mail session.
-----------------------------------------------------------

As expected, "xp_sendmail" generates an error message, but all the statements are executed.

If I run the same code in a SQL Agent Job, I get the following output:

------------------------------------------------------------
Job 'Testing' : Step 1, 'Enviar Mail' : Began Executing 09-Jan-04 4:08:15 PM

SQL Mail session started. [SQLSTATE 01000]
HELLO [SQLSTATE 01000]
Msg 17914, Sev 18: Unknown recipient: Parameter '@recipients', recipient 'wrong recipient!!!' [SQLSTATE 42000]
---------------------------------------------------------------

As you can see in the output file, the statements after the error are not executed.

My question is: Why the SQL Agent stops the execution immediately after the error?

I tried to put the code within a stored procedure, but it doesn't work. With Query Analyzer, all the code is executed. With the job, the execution stops immediately after the error.

I need a SQL Job to send emails to many recipients. If one of the emails has a wrong recipient, the step should continue to send the other emails. This is the reason why I need to handle the error within the job step.

Any help will be appreciated

Pablo."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-12 : 08:09:01

After an error, jobs will quit by default. You can alter your job steps to continue after an error. In Enterprise Manager, right-click the job, choose Properties, then go to the steps tab. You can edit each step and provide the correct actions it should take for failure, success, and/or completion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources