Question regarding running a stored procedure as a job step

I am running a stored procedure as a job step and in the stored procedure I use return to pass one of several possible values when there is an error in processing (not an system error) so that the job step will fail. However, even when I return a non-zero value using return the job step completes as successful. What should I be doing so that the job step picks up the non-zero value and then indicates the step failed?

Try using a RAISERROR with a severity greater than 10.

-Sue

|||Thank you very much Sue

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:

>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.

Question on SQL job step invoking .cmd

I have a simple SQL job that is scheduled to invoke a .cmd to perform a
file copy task.
How can I get SQL job to report to correct status when the copy task
fails, such as due to connectivity issue, or file not found, and etc?
thanks.One option is to capture the return code and do a raiserror
if the return code <> 0. Along the lines of:
DECLARE @.ret int
EXEC @.ret = master..xp_cmdshell 'copy ...etc' , NO_OUTPUT
IF @.ret <> 0 RAISERROR('File copy error',16, 1)
-Sue
On 28 Jul 2006 08:23:49 -0700, "Rose" <rose.say@.gmail.com>
wrote:
>I have a simple SQL job that is scheduled to invoke a .cmd to perform a
>file copy task.
>How can I get SQL job to report to correct status when the copy task
>fails, such as due to connectivity issue, or file not found, and etc?
>thanks.sql