RETURN from a sp to a SQL Agent 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

General SQL Server Forums

New to SQL Server Programming

RETURN from a sp to a SQL Agent job

Author

Topic

Sharon_DBA
Starting Member

7 Posts

Posted - 2009-07-29 : 17:01:14

Hi,
I am not able to have an Agent job fail when it calls this stored procedure. If I stage it so that I expect the proc to fail, and hence the job to fail, the job instead ends successfully. The print statements for @return show that the return value is correct, but the job won't fail. Any ideas? Is RETURN interpreted correctly by a job?

The syntax in the jobstep is: exec scmprm.dbo.usp_checkzerokbfile '\\uncname\bb_w\sha.txt'

use dbname
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_checkzerokbfile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_checkzerokbfile]
GO
/******************************
usp_checkzerokbfile v2unfinished
example of usage:
exec dbname.dbo.usp_checkzerokbfile '\\uncname\bb_w\sha2.txt'
*******************************/
CREATE PROCEDURE dbo.usp_checkzerokbfile
@fullfile varchar(255)=''

AS

declare @exists int
declare @result int
set @exists=0
set @result=0

/********************************************************************************************
** Make sure file exists
********************************************************************************************/

create table #FileExists
(FileExists int,
FileDirectory int,
ParentDirectory int)

insert into #FileExists
EXEC [master].[dbo].[xp_fileexist] @fullfile

select @exists=FileExists
from #FileExists

if @exists=0
begin
RAISERROR ('%s not found. Be sure that this is not a mapped drive - SQL can only use UNC for non-local drives. Exiting...',16,1,@fullfile) WITH SETERROR
goto done
end

drop table #FileExists
/**********************************************************************************/

set nocount on

declare @execsql nvarchar (255)
,@size int
,@outmsg varchar(50)
,@cmd varchar (100)

create table #filedetails
(
[Alternate Name] nvarchar (30),
[Size] int,
[Creation Date] int,
[Creation Time] int,
[Last Written Date] int,
[Last Written Time] int,
[Last Accessed Date] int,
[Last Accessed Time] int,
[Attributes] int
)

--get size from file in dir and check for 0KB file
insert into #filedetails exec master..xp_getfiledetails @fullfile

set @size = (select size from #filedetails)

drop table #filedetails
if @size <>0
begin
select 'file '+ @fullfile +' is not 0KB.'
set @result=0
select @result
RETURN @result
end

else

begin

select 'file '+ @fullfile +' is 0KB. Returning non-zero to indicate an empty file.'
--RAISERROR ('File is 0KB. Returning non-zero to indicate an empty file.',16,1) WITH SETERROR
set @result=1
select @result
RETURN @result

end

DONE:

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 17:12:31

No.
What are the step details? Especially the "On Failure" column.

N 56°04'39.26"
E 12°55'05.63"

Sharon_DBA
Starting Member

7 Posts

Posted - 2009-07-29 : 17:15:52

On Success -> Quit with Success
On Failure -> Quit with Failure

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 17:17:58

quote:
if @exists=0
begin
RAISERROR ('%s not found. Be sure that this is not a mapped drive - SQL can only use UNC for non-local drives. Exiting...',16,1,@fullfile) WITH SETERROR
goto done
end

if @exists=0
begin
RAISERROR ('%s not found. Be sure that this is not a mapped drive - SQL can only use UNC for non-local drives. Exiting...',16,1,@fullfile)
return -1000
end

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-29 : 17:29:59

The RETURN statement shouldn't affect a sql agent job. You can, however, use that value if you like. If you can try something more simplified and work backwards maybe that'll help determine what the issue is.

I created a super simple stored proc and called it from a job and it errors as expected:

ALTER PROCEDURE ErrorTest
AS
RAISERROR('Fail!', 16, 1)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-29 : 18:05:04

If you want the job to fail on a non-zero return code, something like this will do it.


declare	@retcode		int
declare	@SQL_Error		int

execute @retcode = [dbo].[MyStoredProcedure]

select @SQL_Error = @@error

if @retcode <> 0 or @SQL_Error <> 0
	begin
	raiserror('Error executing [dbo].[MyStoredProcedure]',18,1)
	end

CODO ERGO SUM

Sharon_DBA
Starting Member

7 Posts

Posted - 2009-07-29 : 18:27:50

Thank you all for your feedback.

The reason why I commented out the RAISEERROR in favor of using RETURN @result was that this proc will also be used as a child proc called by a main proc. If I use RAISEERROR, as I understand it, will terminate immediately. It appears that the main proc is terminating when it calls this child proc, and I don't want it to. The main proc loops three times calling this child proc when it checks for 0KB. When 0KB is found on the first loop, the main proc was not looping the second and third times. This is why I switched from RAISEERROR to RETURN, so it wouldn't terminate. So, in troubleshooting, I tried just calling the child proc from a job as a test, and the results are what I described. I don't understand why the job succeeds when it should fail using this code...I can't see the problem...

Sharon_DBA
Starting Member

7 Posts

Posted - 2009-07-29 : 18:41:18

Lamprey,
When you say "shouldn't affect the job", do you mean that it won't work when calling a job? I used your example and found the following:

CREATE procedure errortest
as
return 1
GO
This will NOT fail a job.

CREATE procedure errortest
as
raiserror ('fail',16,1)
GO
This WILL fail a job.

It appears that RETURN will not work with jobs, and I think that is what you are saying...If this is true, then I never knew this and have to revisit a lot of former code :)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-30 : 11:06:52

quote:
Originally posted by Sharon_DBA

Lamprey,
When you say "shouldn't affect the job", do you mean that it won't work when calling a job? I used your example and found the following:

CREATE procedure errortest
as
return 1
GO
This will NOT fail a job.

CREATE procedure errortest
as
raiserror ('fail',16,1)
GO
This WILL fail a job.

It appears that RETURN will not work with jobs, and I think that is what you are saying...If this is true, then I never knew this and have to revisit a lot of former code :)

That is correct. The return code is just that, a code. It doesn't have any affect of the calling environemnt. If you want to use the code rather than raising an error, you should be able to adapt MJV's code to your needs.

Sharon_DBA
Starting Member

7 Posts

Posted - 2009-07-30 : 12:34:08

Thank you all for teaching me this...much appreciated...

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 12:38:42

Except for the fact that a return code can be over-ridden by SQL Server...and you should not code for it, except for what SQL Server sets.

If you set rc, code for, sql server over-rides it, you are hosed

Use OUTPUT variables instead

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-30 : 13:09:34

quote:
Originally posted by X002548

Except for the fact that a return code can be over-ridden by SQL Server...and you should not code for it, except for what SQL Server sets.

If you set rc, code for, sql server over-rides it, you are hosed

Use OUTPUT variables instead

Can that even happen?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-30 : 14:17:27

quote:
Originally posted by X002548

Except for the fact that a return code can be over-ridden by SQL Server...and you should not code for it, except for what SQL Server sets.

If you set rc, code for, sql server over-rides it, you are hosed

Use OUTPUT variables instead

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

I have never seen that.

Do you have an example that demonstrates that?

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-30 : 15:29:56

Are you talking about in a job step?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources