November 24, 2015 at 12:56 pm
Hi,
I am looking into the same script instead of priniting , I want to send the result to me.Also if the job is still running, I need the result when it started, how much duration so far..?
DECLARE @JOB_NAME SYSNAME = N'Daily update of indexes & statistics';
IF NOT EXISTS(
select 1
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
where
activity.run_Requested_date is not null
and activity.stop_execution_date is null
and job.name = @JOB_NAME
)
BEGIN
PRINT 'Starting job ''' + @JOB_NAME + '''';
EXEC msdb.dbo.sp_start_job @JOB_NAME;
END
ELSE
BEGIN
PRINT 'Job ''' + @JOB_NAME + ''' is already started ';
END
November 24, 2015 at 1:00 pm
I tried the following script but it is not working properly. It is sending just plain attachment no result
Ue [Tools]
CREATE PROCEDURE [dbo].[Sp_JObStatus](@JobName SysName)
As
select job.Originating_Server Server
,job.Name JobName
,job.job_ID
,activity.run_requested_Date run_request_Date
,activity.stop_execution_date stop_execution_date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsedmin
FROM
msdb.dbo.sysjobs_view job
INNER JOIN --select top(1) * from
msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
job.name =
@JobName
and
run_Requested_date is not null
and stop_execution_date is null
IF EXISTS(Select
job.Originating_Server Server
,job.Name JobName
,job.job_ID
,activity.run_requested_Date run_request_Date
,activity.stop_execution_date stop_execution_date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsedmin
FROM
msdb.dbo.sysjobs_view job
INNER JOIN --select top(1) * from
msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
job.name =
@JobName
and
run_Requested_date is not null
and stop_execution_date is null)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile',
@recipients = 'ramana3327@yahoo.com',
@subject = 'JobRunStatus',
@body = 'The Job is still running',
@query='
declare @JobName sysname
Select
job.Originating_Server Server
,job.Name JobName
,job.job_ID
,activity.run_requested_Date run_request_Date
,activity.stop_execution_date stop_execution_date
,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsedmin
FROM
msdb.dbo.sysjobs_view job
INNER JOIN --select top(1) * from
msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
job.name = @JobName',
@attach_query_result_as_file=1
end
else
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile',
@recipients = 'ramana3327@yahoo.com',
@subject = 'JobRunStatus',
@body = 'The Job run completed'
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply