29 julio 2009

Accessing SQL Server Agent Jobs Metadata

In every BI project that we develop SQL Server Integration Services (SSIS) packages, we also need to create some SQL Server Agent jobs to automate the package execution. In addition, according to business needs, we also need to configure different “execution schedules” for the same SQL Agent job. For example, we have an SSIS package that needs to run from Monday to Friday at 1am (first schedule), and from Saturday and Sunday at 6am (second schedule). In other cases you can have more complex schedules like the one shown on the image below:

image

In this context, we needed an “easy way” to get better manageability and visibility of the existing job execution schedules for our packages (using jobs) instead of using SQL Server Management Studio to open job by job and reviewing its execution schedule. So I decided to query SQL Server Agent (SSA) jobs metadata directly. All the information about SQL Agent jobs (and its other features), is maintained in the MSDB system database, specifically in tables whose names begins with “dbo.sysjobs…”.

You can execute the following query in SQL Server Management Studio:

USE msdb
GO

SELECT name
FROM sys.tables
WHERE name like 'sysjob%'

The results are: image 

Here is a brief description of each of these tables from the SQL Server documentation in MSDN:



* sysjobs: Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobactivity: Records current SQL Server Agent job activity and status.


* sysjobhistory: Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.


* sysjobschedules: Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobsservers: Stores the association or relationship of a particular job with one or more target servers.This table is stored in the msdb database.


* sysjobssteps: Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobstepslogs: Contains the job step log for all SQL Server Agent job steps that are configured to write job step output to a table. This table is stored in the msdb database.



A SQL Server Diagram with the SQL Server Agent job tables and columns is shown below:



image



So, at this point it is relatively easy to create a query to get all the schedules for my active jobs:



SELECT J.name,  J.enabled, J.description, S.enabled AS 'Schedule Enabled?' , 
S.active_start_date, S.active_start_time, S.active_end_date,
S.active_end_time, S.freq_type, S.freq_interval,
S.freq_subday_type, S.freq_subday_interval
FROM sysjobs J
INNER JOIN sysjobschedules JS ON J.job_id = JS.job_id
INNER JOIN sysschedules S ON JS.schedule_id = S.schedule_id
WHERE J.enabled = 1
AND J.name LIKE '%NAPS%'
ORDER BY J.name



Most of the columns are self explanatory but those related to the schedule details are not (freq_type, freq_interval, freq_subday, freq_subday_interval). To get more information about what each of those column values means refer to the sysschedules table documentation on MSDN.



Finally, to make my SQL Agent job schedules results more relevant, I added some CASE conditions to translate values for frequency related columns as shown below:



'Frequency' = CASE S.freq_type 
WHEN 1 THEN 'One Time'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
ELSE 'Other'
END,
'Interval' = CASE
WHEN S.freq_type = 8 AND S.freq_interval = 1 THEN 'Sunday'
WHEN S.freq_type = 8 AND S.freq_interval = 2 THEN 'Monday'
WHEN S.freq_type = 8 AND S.freq_interval = 4 THEN 'Tuesday'
WHEN S.freq_type = 8 AND S.freq_interval = 8 THEN 'Wednesday'
WHEN S.freq_type = 8 AND S.freq_interval = 16 THEN 'Thursday'
WHEN S.freq_type = 8 AND S.freq_interval = 32 THEN 'Friday'
WHEN S.freq_type = 8 AND S.freq_interval = 64 THEN 'Saturday'
WHEN S.freq_type = 8 AND S.freq_interval = 127 THEN 'Everyday'
WHEN S.freq_type = 8 AND S.freq_interval = 111 THEN 'Except Thursday'
WHEN S.freq_type = 8 AND S.freq_interval = 95 THEN 'Except Friday'
WHEN S.freq_type = 8 AND S.freq_interval = 94 THEN 'Except Friday and Sunday'
WHEN S.freq_type = 8 AND S.freq_interval = 65 THEN 'Weekends'
WHEN S.freq_type = 8 AND S.freq_interval = 30 THEN 'Monday to Thursday'
WHEN S.freq_type = 4 THEN CAST(S.freq_interval AS varchar(10))+ ' day(s)'
WHEN S.freq_type = 16
THEN 'Day of the month = ' + CAST(S.freq_interval AS varchar(10))
WHEN S.freq_type = 32 AND S.freq_interval = 1 THEN 'Sunday'
WHEN S.freq_type = 32 AND S.freq_interval = 2 THEN 'Monday'
WHEN S.freq_type = 32 AND S.freq_interval = 3 THEN 'Tuesday'
WHEN S.freq_type = 32 AND S.freq_interval = 4 THEN 'Wednesday'
WHEN S.freq_type = 32 AND S.freq_interval = 5 THEN 'Thursday'
WHEN S.freq_type = 32 AND S.freq_interval = 6 THEN 'Friday'
WHEN S.freq_type = 32 AND S.freq_interval = 7 THEN 'Saturday'
WHEN S.freq_type = 32 AND S.freq_interval = 8 THEN 'Day'
WHEN S.freq_type = 32 AND S.freq_interval = 9 THEN 'Weekday'
WHEN S.freq_type = 32 AND S.freq_interval = 10 THEN 'Weekend day'
WHEN S.freq_type = 8 THEN CAST(S.freq_interval AS varchar(10))
ELSE 'N/A'
END,
'Sub Day Frequency' = CASE S.freq_subday_type
WHEN 1 THEN 'At the specified time'
WHEN 2 THEN 'Seconds'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END,
'Sub Day Interval' = S.freq_subday_interval


To put the cherry on the cake, for better access and automation you can include this query in your Reporting Services report as I did it:



image



The queries created here where designed for SQL Server 2008 but I’m very sure that it’s very similar for SQL Server 2005.


I have shown you how easy is to access SQL Server Agent metadata to get more visibility for IT operations and better maintenance purposes. Remember that there are more tables in MSDB for accessing other SQL Server Agent features.


Alan

13 julio 2009

Syntax highlighting bug in Reporting Services 2008 MDX Query Designer

Maybe you have noticed that in difference to Reporting Services 2005, the MDX Editor in Reporting Services 2008 has not the syntax highlighting feature enabled, for this reason it’s very painful to edit MDX queries relatively complicated using this editor.

Here some screenshots showing the problem in Business Development Studio (BIDS) 2008 – where we design the Reporting Services reports, notice that the BIDS 2005 “Data” tab is not more a tab but a dialog window:

image

image

image 
Below how the same query looks in the BIDS 2005 MDX Query Editor (showing the “Data” tab in the same window when we design the report):

image

image

From some time ago, I was looking for some explanation about why syntax highlighting doesn’t work in the current version of BIDS (and with the hope of turn that feature on as in BIDS 2005) but without any success.
Finally, some days ago I decided to contact Microsoft directly about this issue. First at all, I have to say that I was very pleased about the prompt and fast response from Carolyn Chau from the Reporting Services Team, it was a great example of customer service!

Here Carolyn’s response:

“…I’m afraid we lost this feature between 2005 and 2008 due to a strange quirk in the combination of VS and the MDX query designer.  When the query designer is hosted in a dialog page as we have in 2008, syntax highlighting does not work.  We continue to try and find work arounds to this issue…“

Finally, you know what is going on with syntax highlighting in BIDS 2008. Carolyn gave me the explanation response about this “bug” directly related to the integration of custom dialog windows and the Visual Studio IDE.

Extra-officially, this bug could be fixed in the next release of SQL (SQL 2008 R2), so we need to wait until the CTP to see it. Meanwhile, we have to patiently continue creating our MDX queries in SQL Server Management Studio or in BIDS 2005 (if you have it installed side by side with your 2008 version) to then copy and paste to BIDS 2008.

Alan