Most of the reports use powershell to retrieve information from SQL servers. ETL is used to load data to DBA data warehouse and SSRS is used to display data. In the backend, Powershell script and ETL packages are scheduled in SQL Agent job.
In the process of learning how to call a SQL agent job from SSRS, I have learnt how to pass parameters to a new SSRS window and passing a parameter to a new report.
In DBA Reporting Dashboard, I want to refresh a specific report. I added runaJobtorefresh column in DBA Dashboard table and populated the column with "Refresh". So, Refresh link will appear beside the report.
The DashBoard dataset is retrieve from a Table which is already populated with ReportName, JobName, ReportURL and Refresh. In RunaJobToRefresh column display Refresh word.
Right Click on RunaJobTORefresh textbox and open properties.
Go to Action tab, Check Go To URL and we will type expression in Select URL.
="javascript:void(window.open('http://ServerName/ReportServer?/DBA_Reports/RunAgentJob&rc:stylesheet=hideToolBar&JobName="+Fields!JObName.Value+"','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=350'))"
In the expression, rc:stylesheet=hideToolBar is passed to hide report server toolbar of the linked report (RunAgentJob). I have created a stylesheet hideToolbar.css and saved it under C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\Styles\
The content of the file is .ToolBarButtonsCell{display: none;}
If I click Refresh, a new window will open and pass a JobName from DashBoard report and call the following linked report - RunAgentJob.
The screenshot below is RunAJob report which display notification "Refreshing this report will take 2 mins. " and a hyperlink "Click here to check refresh status"
RunAgentJob report receive a paramenter = JobName from Dashboard report and call a stored procedure which is sp_startjob @jobName
CallAjob Dataset will run a storedprocedure
DataSource will be created on the server where agent job is created.
And then create a parameter Job Name, visibility is set to hidden
On RunAgentJob report, I have created another link "Click here to check refresh status". By clicking on the link, you will be able to see the status of refresh report.Basically, the link open another linked report called RefreshedJobStatus which pulls the status of the job running at SQL Agent.
Click on Text Box Properties,
In Action tab, It will open RefreshedJobStatus report and passed a parameter JobName to the report.
RefreshedJobStatus report will call a stored Procedure - usp_RefreshJobStatus. In the stored procedure,
CREATE PROCEDURE [dbo].[usp_RefreshJobStatus]
-- Add the parameters for the stored procedure here
(@JobName varchar(256))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
create table #run_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
insert into #run_job
exec master.dbo.xp_sqlagent_enum_jobs 1,garbage
select
sj.job_id,
sj.name,
'Enabled' =
CASE
WHEN sj.enabled = 0 THEN 'NO'
WHEN sj.enabled = 1 THEN 'YES'
END
,sja.sed As RunningJobStartDateTime,
'Current_execution_status'= CASE
WHEN #run_job.State = 0 THEN 'Not idle or suspended'
WHEN #run_job.State = 1 THEN 'Executing'
WHEN #run_job.State = 2 THEN 'Waiting For Thread'
WHEN #run_job.State = 3 THEN 'Between Retries'
WHEN #run_job.State = 4 THEN 'Idle'
WHEN #run_job.State = 5 THEN 'Suspended'
WHEN #run_job.State = 6 THEN 'WaitingForStepToFinish'
WHEN #run_job.State = 7 THEN 'PerformingCompletionActions'
END
,#run_job.Current_Step,
sjs.step_id,
sjs.step_name,
CASE SjSe.[Last_Run_Date]
WHEN 0 THEN NULL
ELSE CAST(
CAST(#run_job.[Last_Run_Date] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST(SjSe.[Last_Run_Time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END As LastRunDateTime,
sjse.last_outcome_message,
STUFF(
STUFF(RIGHT('000000' + CAST([SjSe].[last_run_duration] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [JobLastRunDuration (HH:MM:SS)],
'Notify_Email'=CASE
WHEN sj.notify_level_email= 0 THEN 'NEVER'
WHEN sj.notify_level_email= 1 THEN 'When the job succeeds'
WHEN sj.notify_level_email= 2 THEN 'When the job fails'
WHEN sj.notify_level_email= 3 THEN 'Whenever the job completes (regardless of the job outcome)'
END
from #run_job
join [msdb].[dbo].[sysjobsteps] As SJS on #run_job.job_id= SJS.job_id
left join [msdb].[dbo].[sysjobs] sj on sj.job_id = sjs.job_id
left Join msdb.dbo.SysJobServers SjSe on sjse.job_id = sj.job_id
left join(
select sysjobactivity.job_id,max(sysjobactivity.start_Execution_date)sed from msdb.dbo.sysjobactivity
where sysjobactivity.start_Execution_Date is not null and sysjobactivity.stop_execution_date is null
group by sysjobactivity.job_id) as SJA on
sj.job_id = sja.job_id
where sj.name = @JobName
Order by sj.Name,sjs.Step_id
drop table #run_job
END
In DataSet tab, Call the stored Procedure.
In Parameter , JobName is set up as a Parameter and visibility set to Hidden.
In report Propertites, AutoRefresh = 10 sec.
Now, we are succesfully create a report which can be refreshed on Demand from SSRS.
http://www.sqlservercentral.com/blogs/dknight/2009/11/11/using-a-ssrs-report-to-run-sql-agent-jobs/
http://nishantrana.me/2012/10/20/hiding-tool-bar-from-the-ssrs-report-while-using-them-in-dashboard-crm-2011/
http://thebakingdba.blogspot.com/2011/04/jobs-quick-dirty-is-job-running-another.html
Enjoy Scripting...
Can you please share full code of the SSRS
ReplyDeleteDebasish
Such a nice information through this. And i hope this will let the people those who are ready to face the interview. And please keep update like this.
ReplyDeleteHr Consultancy in Chennai