July 23, 2010 at 4:29 am
Theo-929802 (7/23/2010)
Could you please help me with the try-catch statement?My sp looks like this:
ALTER PROCEDURE [dbo].[usp_consolidation_user_initiate]
@jobname VARCHAR(255),
@confirmation TINYINT
AS
IF @confirmation = 0
BEGIN
SELECT 'The job did not initiate because confirmation has to be set to YES.' AS result
END
ELSE IF @confirmation = 1
BEGIN
EXEC sp_start_job @jobname
END
Try this, i wasnt aware that sp_start_job wouldnt work inside a try catch loop?? are you getting an error along those lines?
ALTER PROCEDURE [dbo].[usp_consolidation_user_initiate]
@jobname VARCHAR(255),
@confirmation TINYINT,
@Result varchar(200) OUTPUT
AS
begin try
IF @confirmation = 0
BEGIN
SELECT @Result = 'The job did not initiate because confirmation has to be set to YES.'
END
ELSE IF @confirmation = 1
BEGIN
EXEC sp_start_job @jobname
END
End Try
Begin Catch
set @result = error_message()
End Catch
July 23, 2010 at 5:25 am
July 23, 2010 at 6:22 am
Changed the sp to the following but I face a new problem. When the job fails to start I don't receive the failure message. In addition I tried to call the sp from SMS and I received the typical SQL Agent error that the job could not start cause it is already running. In the result tab I received the expected message but this would not printed in the report. Any ideas?
USE [msdb]
GO
ALTER PROCEDURE [dbo].[usp_consolidation_user_initiate]
@jobname VARCHAR(255),
@confirmation TINYINT
AS
IF @confirmation = 0
BEGIN
SELECT 'The job did not initiate because confirmation has to be set to YES.' AS result
END
ELSE IF @confirmation = 1
BEGIN
SET NOCOUNT ON
DECLARE @result INT
EXEC @result = sp_start_job @jobname
SELECT @result
IF @result = 0
BEGIN
SELECT 'Job has been started successfully.' AS result
END
ELSE
SELECT 'An error occurred. Job could not be executed.' AS result
END
July 23, 2010 at 6:30 am
hmm ok, i cant remember how exactly i do it. One way you could get round it is to query the state of the job first and make sure its inactive. if it is then start the job
July 23, 2010 at 3:08 pm
I would say your two simplest options are either to create an SSRS report, or, if you don't have SSRS within your organisation then a batch file which calls SQLCMD.
Report or batch file, either way configure them to invoke the sp_start_job stored procedure.
The jobs in question do need to be owned by a specific login.
By granting the login membership of the SQLAgentUserRole within MSDB, this login will have the right to start only those jobs which it owns, as opposed to all jobs on the server.
You would therefore create this SQL Server login with limited rights - it is solely used by your users to start jobs.
July 23, 2010 at 3:23 pm
For the problem of being able to check if the job is started, how about querying sp_job_status within MSDB.
Column "current_execution_status" tells you what the job is doing. The codes correspond to:-
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply