return error from isql process?

Question

Post reply

return error from isql process?

Will1922

SSCertifiable

Points: 7189
More actions
May 10, 2007 at 9:52 am

#177951

My problem is that when I run an isql script inside a Sql Agent job, any errors encountered aren't returned and the job doesn't stop.
Is there an equilivant of ON ERROR in sql? I what the script to stop running and then to stop the job if any errors are returned. I know that I could do something with RAISERROR('Err Text',16,127), but I don't want to have to check after every statement.
There's got to be an easy way to fix this and I'm just not seeing it. I been doing some research this morning and haven't found an easy way yet.
Thanks in advance to any responses to this post.
Live to Throw
Throw to Live
Will Summers

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

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

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

- SET ARITHABORT ON

... but I don't want to have to check after every statement....

Don't complain if you check your data afterward and don't get to see correct results ...

You should at least check @@error after every select/insert/update/delete !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Will1922 SSCertifiable Points: 7189 More actions · Answer 2

Why would ARITHABORT ON have ANYTHING to do with my problem with returning an error condition from an isql script? I said nothing in my original post about divide by zero or overflow.

Is there some hidden feature of ARITHABORT that I'm not aware of?

Live to Throw
Throw to Live
Will Summers

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 3

You're completely correct.

I actualy wanted to point to SET XACT_ABORT ON

This should stop the execution when the first error occurs within a batch (GO)

(check BOL)

However this will not reflect in the returncode of the ISQLw

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Add -b parameter to the isql command line

p.s. also check the use of the -m errorlevel parameter as well

Far away is close at hand in the images of elsewhere.
Anon.

Will1922 SSCertifiable Points: 7189 More actions · Answer 5

ALZDBA - Thanks a lot, I will test that out. Sorry if I seemed irritated by your original post. I was thinking that you were one of those 'flyby posters' who are just trying to get their post count up. That really irritates me when people do that.

David Burrows - I will test the -b and -m parameters.

Thanks to both for your replies. I will post my results.

Live to Throw
Throw to Live
Will Summers

Will1922 SSCertifiable Points: 7189 More actions · Answer 6

The -b did what I wanted. That was the first that I tried. I even tried to put a couple of 'go's in the script and it stopped the script when it hit the first error.

Since all I really wanted to do was stop the script when an error occurs, the -b is all I have added for now. I gotta make changes to all my jobs that use isql.

Thanks again.

Live to Throw
Throw to Live
Will Summers