1

I have a stored procedure USP_A with logic like following:

......
exec dbo.usp_Log 'Start to run job job1'
Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'   
  IF @intErrorCode <> 0 Goto errorHandling
exec dbo.usp_Log 'End to run job job1'
......

But when I run this stored procedure, it got stuck and when I check log I can only see message 'Start to run job job1'. Also in the SQL Server Agent job monitor I can not see this job get triggered.

But if I manually run 

Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'

it works fine.

The SQL Server is Microsoft SQL Server 2005 Enterprise Edition (version 9.00.5000.00)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mhan0125
  • 604
  • 1
  • 11
  • 33

2 Answers2

1

It turns out to be some kind of SQL Server bug (Maybe the SQL Server 2005 is quite old). The stored procedure just ended unexpectedly and does not return any code.

So the problem is solved by moving all the logic before this msdb.dbo.sp_start_job procedure into a separate stored procedure.

Hope it can help anyone who got this same issue.

mhan0125
  • 604
  • 1
  • 11
  • 33
0

Your job is probably failing to start and branching to the errorHandlingsection of your code. If you add @@ERROR_MESSAGE to the log first, then you will see the problem in your log. Something like:

exec dbo.usp_Log 'Start to run job job1'
begin try
    Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'
end try
begin catch
    exec dbo.usp_Log 'Error: ' + ERROR_MESSAGE()
    Goto errorHandling
end catch
exec dbo.usp_Log 'End to run job job1'

UPDATE

I ran a test on my server. I tried to initiate a job that was already running and an error was returned, but did not trigger the catch block. Apparently this is a problem with sp_start_job. This may be the source of some of your confusion. There is a workaround posted here: TRY/CATCH does not work on SQL Server Agent error?

My best guess though is that there is a permission issue when the code is run under a different user context. You could wrap the code to run the job into a procedure that executes under a different security context... like this:

create procedure dbo.sp_RunJob1
with execute as owner
as
exec sp_start_job @job_name = 'job1'

Then call the sp_RunJob1 instead of the sp_start_job statement in your code.

Community
  • 1
  • 1
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • 1
    But in my errorHandling section it does not write any error log. And if I just run this Exec @intErrorCode = msdb.dbo.sp_start_job 'job1' it works fine. – mhan0125 Jun 25 '15 at 16:53
  • Try using the BEGIN TRY syntax. It is more reliable at trapping errors. See: http://stackoverflow.com/questions/1111501/error-and-or-try-catch – Brian Pressler Jun 25 '15 at 16:55
  • It's possible that it is failing under a different user context that does not have permission to run the sp_start_job procedure... but until you see the error message you won't know for sure. – Brian Pressler Jun 25 '15 at 17:01
  • tried to add try-catch block, still no error message, just stuck there. – mhan0125 Jun 25 '15 at 17:31
  • When you say it's just stuck there... do you mean that the code doesn't terminate? How are you initiating the code... in SSMS or in some client code? – Brian Pressler Jun 25 '15 at 19:02
  • I also tried the way you suggested, it works without error. So it shouldn't be user privilege issue. – mhan0125 Jul 03 '15 at 13:18