April 4, 2011 at 8:30 am
I am attempting to setup a .bat file on a Win2008\SQL2008 SQL Server for our scheduling package to run this job. I have created the job within SQL Agent jobs and created a OSQL .bat to run it. However, when I run the .bat file the output simply says it has been started. How do I get it to show the actual output?
osql -S SERVERNAME -U userid /P passwrd -o output.log -Q"msdb..sp_start_job 'ABCD100DJ' "
This is what the output.log shows:
Job 'ABCD100DJ' started successfully.
I want it to show the actual output of what happens and that it succeeded or failed not that it just started successfully.
Any help would be appreciated.
April 4, 2011 at 8:39 am
msdb.dbo.sp_start_job just starts the job, but doesn't wait for it to finish.
If this is the behaviour you want, you will have to code some procedures to do this.
I do this regularly with my "TOOLS" database, but you can modify the code to match your needs:
First procedure: wait for job completion:
USE [TOOLS]
GO
/*
* Waits for a SQLAgent job to complete.
* PARAMS:
* @name - Name of the job to wait for
* @timeout - Maximum number of minutes to wait for. 0 means no timeout.
*/
CREATE PROCEDURE [agent].[dba_wait_job_completion] (@name nvarchar(255), @timeout int = 0)
AS
BEGIN
DECLARE @jobs_running INT
DECLARE @rnd INT
DECLARE @DELAY_STR CHAR(9)
DECLARE @END_TIME DATETIME
SET NOCOUNT ON
IF @timeout = 0
SET @END_TIME = DATEADD(year,10,GETDATE())
ELSE
SET @END_TIME = DATEADD(minute,@timeout,GETDATE())
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
WHILE GETDATE() < @END_TIME
BEGIN
SET @jobs_running = 0
SET @rnd = (SELECT CAST(RAND() * 60 AS INT))
SET @DELAY_STR = '000:00:' + RIGHT('0' + CAST(@RND AS VARCHAR(2)),2)
TRUNCATE TABLE #xp_results
INSERT #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME
IF EXISTS(
select *
from msdb.dbo.sysjobs jobs
inner join #xp_results xp_res
on jobs.job_id = xp_res.job_id
where xp_res.job_state = 1--RUNNING
and jobs.name = @name--JOB NAME
)
SET @jobs_running = 1
TRUNCATE TABLE #xp_results
IF @jobs_running = 1
BEGIN
--WAITS A RANDOM NUMBER OF SECONDS BETWEEN 0 AND 60
WAITFOR DELAY @DELAY_STR
END
ELSE
BEGIN
drop table #xp_results
SET NOCOUNT OFF
PRINT 'JOB COMPLETED'
RETURN
END
END -- END WHILE
drop table #xp_results
SET NOCOUNT OFF
RAISERROR('UNABLE TO WAIT FOR JOB COMPLETION, TIMEOUT EXPIRED',5,1)
END
Second procedure: start a job and wait for its completion:
USE [TOOLS]
GO
/*
* Starts a SQLAgent job and waits for its completion.
* PARAMS:
* @name - Name of the job to wait for
* @timeout - Maximum number of minutes to wait for. 0 means no timeout.
*
* PERMISSIONS:
* -- msdb
* GRANT SELECT ON dbo.sysjobs TO <username>
* GRANT SELECT ON dbo.sysjobhistory TO <username>
* EXEC msdb.dbo.sp_addrolemember 'SQLAgentOperatorRole' , '<username>'
* EXEC msdb.dbo.sp_addrolemember 'SQLAgentReaderRole' , '<username>'
* EXEC msdb.dbo.sp_addrolemember 'SQLAgentUserRole' , '<username>'
* -- master
* GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO <username>
*/
CREATE PROCEDURE [agent].[dba_execute_job] (@name nvarchar(255), @timeout int = 0)
AS
BEGIN
DECLARE @ret_val INT
SET NOCOUNT ON
BEGIN TRY
-- START THE JOB
EXEC @ret_val = msdb.dbo.sp_start_job @name
IF @ret_val <> 0
RAISERROR('Unable to start the job',16,1)
-- WAIT 10 SECONDS (Should be enough in most cases)
WAITFOR DELAY '00:00:10'
EXEC agent.dba_wait_job_completion @name, @timeout
IF (
SELECT TOP 1 run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = (
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE name = @name
)
ORDER BY instance_id DESC
) = 0
RAISERROR('The job failed. See job history to check the error condition.',16,1)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
Your command line would be:
osql -S SERVERNAME -U userid /P passwrd -o output.log -Q"TOOLS.agent.dba_execute_job 'ABCD100DJ' "
Hope this helps
Gianluca
-- Gianluca Sartori
April 4, 2011 at 9:00 am
Wow. Thanks a million. This is perfect.
April 4, 2011 at 9:02 am
Glad I could help.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply