Job vs manual execution issues

[Valcor]

All,

I'm having an odd issue, and it's kicking my butt. I have a stored procedure that calls a series of other stored procedures. The first called stored procedure gets a list of files from a folder. It then uses a BULK INSERT and puts them into a table for processing by the next stored procedure.

Occasionally the files won't import, due to bad data, incorrect types, etc. So I wrapped the BULK INSERTs with error handling, that looks like this:

create table #tmpFiles (
  fName varchar(150)
)

insert into #tmpFiles
  exec master..xp_cmdshell 'dir c:\imports\*.tab'

delete from #tmpFiles where fName is null or fName = 'NULL'

declare @sql varchar(1000), @fName varchar(150), @err int

declare getfName cursor
for
  select fName from #tmpFiles

open getfName
WHILE (1=1)
BEGIN
  FETCH getfName INTO @fName
  if @err <> 0 or @@FETCH_STATUS <> 0
    BREAK

  SET @sql = 'BULK INSERT [mytable] FROM ''C:\Imports\' + @fName + ''' WITH (MAXERRORS=1000000, FIELDTERMINATOR = ''\t'', ROWTERMINATOR=''\N'')'

  BEGIN TRANSACTION

  EXEC(@sql)
  SELECT @err = @@error
  IF @err <> 0
  BEGIN
    GOTO Fail
  END

  COMMIT TRANSACTION
  CONTINUE
Fail:
  ROLLBACK TRANSACTION
  exec master..xp_sendmail @recipients='me@mydomain',
    @subject='Failed ' + @fName,
    @message='Failure'
END

CLOSE getfName
DEALLOCATE getfName

This is slightly abbreviated, as there are several other import steps, but it gives you a good enough idea. This sproc is called from inside another sproc. If I execute the parent sproc from query analyzer, it runs through, executes fine, generates an email when there was a bad file, and the does the other stored procedures. However, if I run this same code as a job, it'll run through the files, until it hits one with an error, it'll send the email, then just exit, failing the job.

Any idea why they behave differently? And how can I make this work as a job?

Thanks
--
Jon

--
Jon