How do I force a job to fail ?

12 Aug 2005 8:12 AM

Steen Persson (DK)

Hi

I think this is quite simple, but how do I force a job to fail using a TSQL
command?

I have a job that copies a backup to another server and then it starts
another job that restore the backup. What I'd like to do, is to put a step
in the first job, that e.g. runs RESTORE HEADERONLY FROM DISK=....... and if
this doesn't return the correct value the job should quit with a failure.

Regards
Steen

12 Aug 2005 8:23 AM

Tibor Karaszi

Do a RAISERROR with severity > 10.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Steen Persson (DK)" <s**@REMOVEdatea.dk> wrote in message
news:uVV$UWxnFHA.3256@tk2msftngp13.phx.gbl...
> Hi
>
> I think this is quite simple, but how do I force a job to fail using a TSQL command?
>
> I have a job that copies a backup to another server and then it starts another job that restore
> the backup. What I'd like to do, is to put a step in the first job, that e.g. runs RESTORE
> HEADERONLY FROM DISK=....... and if this doesn't return the correct value the job should quit with
> a failure.
>
> Regards
> Steen
>

12 Aug 2005 8:24 AM

R.D

why dont you use continue, break in procedure that is getting executed
instead of stopping job on condition.

Show quote

"Steen Persson (DK)" wrote:

> Hi
>
> I think this is quite simple, but how do I force a job to fail using a TSQL
> command?
>
> I have a job that copies a backup to another server and then it starts
> another job that restore the backup. What I'd like to do, is to put a step
> in the first job, that e.g. runs RESTORE HEADERONLY FROM DISK=....... and if
> this doesn't return the correct value the job should quit with a failure.
>
> Regards
> Steen
>
>
>

12 Aug 2005 8:39 AM

Steen Persson (DK)

R.D wrote:

> why dont you use continue, break in procedure that is getting
> executed instead of stopping job on condition.
>
>

well....good question. It was just the first idea that came to my mind. If I
"force" the job to fail, I'd get a notification just like if the job was
failing in the "normal" way, so I saw it as the easiest way of doing it.

My next issue (which I thought I knew how to do...) is how I fetch the
result set I get from running "RESTORE HEADERONLY...." so I can evaluate on
e.g. the BackupName field?

Regards
Steen

12 Aug 2005 9:03 AM

Tibor Karaszi

> My next issue (which I thought I knew how to do...) is how I fetch the result set I get from
> running "RESTORE HEADERONLY...." so I can evaluate on e.g. the BackupName field?

CREATE TABLE r (...)
INSERT INTO r(...)
EXEC('RESTORE HEADERONLY ...')

You can find the structure of the table you need to create, and the overall technique at
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Steen Persson (DK)" <s**@REMOVEdatea.dk> wrote in message
news:%23ky1olxnFHA.3316@tk2msftngp13.phx.gbl...
>
> R.D wrote:
>> why dont you use continue, break in procedure that is getting
>> executed instead of stopping job on condition.
>>
>>
>
> well....good question. It was just the first idea that came to my mind. If I "force" the job to
> fail, I'd get a notification just like if the job was failing in the "normal" way, so I saw it as
> the easiest way of doing it.
>
> My next issue (which I thought I knew how to do...) is how I fetch the result set I get from
> running "RESTORE HEADERONLY...." so I can evaluate on e.g. the BackupName field?
>
> Regards
> Steen
>
>