Gettting a job to fail - 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 2005 Forums

Transact-SQL (2005)

Gettting a job to fail

Author

Topic

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 12:16:21

I'm trying to get a job to fail using a stored procedure command.

I have been using:
EXEC sp_stop_job @job_name = 'Archive Tables'

But this command only cancels the job, I want to the job to report back a failure.

Is there a sp_fail_job, or a sp_quit_job or something?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 13:10:28

Can i ask why you need to fail a job for?

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 13:17:39

I want it to be apparent that there is a problem with the job.

If I just use the
EXEC sp_stop_job @job_name = 'Archive Tables'

Then it will just cancel the job. But if I can cause the job to fail then it will put a big red X next to the job name making it clear that attention is needed.

If someone looks at the job history and sees that the job was canceled, then they will assume this was done on purpose by someone.
But, if they see that the job failed, they will know to look into the job for a solution.

I am aware that there are commands that will notify and things like that, and I'm also aware that I can cause the job to fail by doing something like calling a SP that does not exist, but I want to avoid these alternatives if possible.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 13:21:44

When I purposefully want a job to fail, I put this in a job step:
asdf

In order to help you further, we'd need more information as to exactly why the job isn't failing on its own. In what condition do you need to cancel the job and show a failure?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 13:29:15

The problem is that when I try to unzip some files in a directory, and they arent all there, then the step will just hang up. So you dont know if its just taking a lot time to unzip or if the step is frozen.

The job will not fail if the files are not all there, so I need to cause the job to fail manually.

So i wrote some code like this.

if (unzip files are all there)
unzip files
else
cause job to fail manually(this is where I need a fail commmand)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 13:52:30

Can't you write code to ensure that everything is there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 14:08:19

I dont think so because we FTP the zip files in from a server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 14:11:19

That shouldn't matter. You can use vbscript in a job step to check for everything that you expect to be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 14:35:33

So what do I do when everything is not there?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-05-19 : 15:37:03

do not try to unzip if its not there

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 15:45:30

I dont try to unzip if the files aren't there.

What I want to happen is if the files aren't there, run a command that will fail the job so that people can see that the job messed up.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 16:07:51

Rocket,

What are you using in your job step. Are you runing an ActiveX (VBscript) script? If so you can do something like this:

IF 1 =1 THEN
	Err.Raise vbObjectError + 999, "Failure", "Check Failed"
END IF

Rocket452
Starting Member

7 Posts

Posted - 2008-05-19 : 16:17:21

I need code that I can put in my T-SQL stored procedure

thank you though

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 16:21:51

Perhaps you should show us the code then as there is not a system stored procedure to fail the job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 16:51:52

Assuming your Stored Procedure is called fromthe job - try: RAISERROR('Something has gone terribly wrong!', 15, 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources