passing success or failure in SQL Job

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

SQL Server Administration (2005)

passing success or failure in SQL Job

Author

Topic

akrivorot
Starting Member

2 Posts

Posted - 2008-09-16 : 21:28:10

Does anyone know if it is possible to pass success or failure as status code in an SQL Job?

I would like to create a Job that runs a T-SQL query and based on the result pass a success or failure code to the Job.

Thanks for any help.
Avi

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2008-09-17 : 09:37:24

I don't know if that's possible, but the code below will do something similiar.

DECLARE @retstat int;
EXECUTE @retstat = sp_who2
IF @retstat = 0
PRINT 'success'
ELSE
EXEC sproc_FailAlways_20080917_1430 --Execute something that will always fail, will cause the step to fail.

I'd suggest you reconsider your approach on this. Maybe use SSIS for the control flow.

akrivorot
Starting Member

2 Posts

Posted - 2008-09-17 : 10:26:31

Thanks, that's exactly what i was looking for. here is what i did:

declare @num int;
set @num = (my select query...)

if (@num < 1000)
BEGIN
PRINT 'Under 1000 rows!!'
END
else
BEGIN
PRINT 'Over 1000 Rows!!'
select * from none_existing_table
END

nice and simple.

I'll investigate SSIS, never used it before.

Avi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources