T-SQL Job Steps - setting return code

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)

T-SQL Job Steps - setting return code

Author

Topic

martinch
Starting Member

35 Posts

Posted - 2005-03-21 : 09:37:35

Hi all,

As part of a scheduled job, I have a job step which is a SQL script. This creates a backup of a table, and then runs a DTS package (via xp_CmdShell). Now, in the "Advanced" tab of the "Edit Job Step" window, you can set it to go to different job steps depending on whether it suceeded/failed. So, the question is, can you set whether or not the step has suceeded/failed in SQL?

Basically, I'd like something like this:

SELECT * INTO BAK_TableToBackup
FROM TableToBackup

SET @Cmd = 'dtsrun /blah'

EXEC @Status = master.dbo.xp_cmdShell @Cmd

IF @Status = 0
Set it so that this step succeded
ELSE
Set it so this step failed

The problem is, I can't figure out what the keywords are (I'd have thought you used RETURN, but you can't) - I've had a look in BOL and can't seem to find it

Any ideas?

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-21 : 10:21:02

This should do it:


IF @Status <> 0
	begin
	raiserror('Error running DTS package BLAH ',18,1) 
	end

CODO ERGO SUM

martinch
Starting Member

35 Posts

Posted - 2005-03-21 : 10:32:27

That looks great, Michael. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources