08-Jul-2010
Posted by : Andreas
I have made a generic report in Reporting Services 2008 to browse and display the job history of SQL Server Agent. This is very useful if you want to see what jobs fail the most, when do jobs overlap, is execution time increasing for a specific job etc. If you have many jobs, it can be quite useful to set up a subscription on this report, so you can see every day the status of alle the SQL Server Agent jobs. I use this report on all SQL Server Instances i control. I also recommend increasing the number of log entries stored in the log table on msdb to increase the usefulness of the report. The report is made in Reporting Services 2008, but the query against log data works against 2005 and up.
The user account accessing the SQL Server database needs read-acces to msdb, and the report has two parameters:
- Search – For entering search strings. Useful if your project for example as prefixed job names with the project name
- Include Subscriptions – This parameter is useful if SQL Server has many reporting services subscriptions running. SSRS Subscriptions has a GUID as the name of the SQL Server Agent job, so they are quite easy to filter out.
Download here: http://www.sqldisco.com/files/Jobstatus.zip
Let me know if you find it useful, or if you have any recommended improvements I can implement.
09-Dec-2009
Posted by : Andreas
Just figured out a neat way to quickly search metadata easily when working with SQL. If you use SQL Prompt from Red Gate, I suggest adding the following as a new snippet (I use FF):
1
2
3
| SELECT RTRIM(so.name) + '.' + RTRIM(sc.name) AS DBObjectName
FROM SYSOBJECTS so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.type = 'U' AND (sc.name+' '+so.name) LIKE '% |
Very handy to always have search ready at your fingertips, especially when working with data extraction from unfamiliar databases.
25-Feb-2009
Posted by : Andreas
I have a customer that have hundreds of subscriptions on their report server. Sometimes subscriptions fail, but there’s no built in solutions to recieve alerts when subscriptions are failing as far as I know. For this customer we use SQL Server 2008, and to recieve alerts on failed subscriptions, I used the following SQL to make a report (copied from http://www.theruntime.com):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
| WITH [SUBJECT] ( [SubscriptionID], [SubjectLine] )
AS ( SELECT I.[SubscriptionID],
I1.ROWS.VALUE('Value [1]', 'VARCHAR(500)') AS [SubjectLine]
FROM (SELECT S.[SubscriptionID],
CONVERT(XML, N'<Root>'
+ CONVERT(NVARCHAR(MAX), S.[ExtensionSettings])
+ N'</Root>') AS [ExtensionSettings]
FROM ReportServer.dbo.[Subscriptions] S WITH ( NOLOCK )
WHERE S.[DeliveryExtension] = 'Report Server Email'
) I
CROSS APPLY I.ExtensionSettings.nodes('/Root/ParameterValues/ParameterValue')
AS I1 ( ROWS )
WHERE I1.ROWS.VALUE('Name [1]', 'VARCHAR(100)') = 'Subject'
)
SELECT S.[SubscriptionID],
S.[InactiveFlags],
S.[ExtensionSettings],
[SUBJECT].[SubjectLine],
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
S.[MatchData],
S.[LastRunTime],
S.[DeliveryExtension],
SC.[StartDate],
SC.[EndDate],
SC.[Flags],
SC.[RecurrenceType],
SC.[STATE],
C.[PATH],
C.[Name],
[U1].[UserName] AS [Owner],
[U2].[UserName] AS [ModifiedBy],
[URL] = 'http://SERVER/Reports_Sql2008/Pages/SubscriptionProperties.aspx?ItemPath='
+ C.[PATH] + '&IsDataDriven=False&SubscriptionID='
+ CAST(S.[SubscriptionID] AS VARCHAR(80)),
[URL2] = 'http://SERVER/Reports_Sql2008/Pages/Report.aspx?ItemPath='
+ C.[PATH] + '&SelectedTabId=SubscriptionsTab'
FROM ReportServer.dbo.[Subscriptions] S WITH ( NOLOCK )
LEFT OUTER JOIN ReportServer.dbo.[CATALOG] C WITH ( NOLOCK ) ON S.[Report_OID] = C.[ItemID]
LEFT OUTER JOIN ReportServer.dbo.[Users] [U1] WITH ( NOLOCK ) ON S.[OwnerID] = [U1].[UserID]
LEFT OUTER JOIN ReportServer.dbo.[Users] [U2] WITH ( NOLOCK ) ON S.[ModifiedByID] = [U2].[UserID]
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] RS WITH ( NOLOCK )
ON S.[SubscriptionID] = RS.[SubscriptionID]
LEFT OUTER JOIN ReportServer.dbo.[Schedule] SC WITH ( NOLOCK ) ON RS.[ScheduleID] = SC.[ScheduleID]
LEFT OUTER JOIN [SUBJECT] ON S.[SubscriptionID] = [SUBJECT].[SubscriptionID]
WHERE UPPER(LEFT(S.[LastStatus], 7)) = 'FAILURE' |
I also used the URL2-field to make a link from the subscription report to the actual subscription that has failed to make it easier for the customer to correct the mistakes in the subscription.
When the subscription report was ready, I set it up as a one time / run once subscription. I found the name of the SQL Server Agent job by running this query (it should be the top one) :
1
2
3
4
5
| SELECT schedule.scheduleid
FROM subscriptions
LEFT JOIN reportschedule ON subscriptions.subscriptionid = reportschedule.subscriptionid
LEFT JOIN schedule ON schedule.scheduleid = reportschedule.scheduleid
ORDER BY subscriptions.modifieddate DESC |
I then copied the SQL from the step in the Server Agent Job, and wrapped it in a “IF EXISTS”. The resulting query was put into a new sql server agent job which run daily:
1
2
3
4
5
6
7
8
9
10
11
| IF EXISTS (SELECT *
FROM Subscriptions
WHERE UPPER(LEFT(Subscriptions.[LastStatus], 7)) = 'FAILURE' AND
LastRunTime>=DATEADD(dd,-1,GETDATE())
)
BEGIN
--SQL From the run-once SQL Server Agent Job should look something like this:
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription',
@EventData='0326035a-ed10-4bd3-9b37-14341d49d3dc'
END |
This triggers the report every morning only if there have been failed subscriptions during the last 24 hours, and make it easy for the customer to fix the failed subscriptions. They can also change who recieves the subscription alert by changing the subscription of the “subscription report”
16-Nov-2007
Posted by : Andreas
I noticed somthing interesting today about NULL handling. This is very basic stuff, but if it’s new to me, then it’s disco material.
Say you have a table with one integer column:
CREATE TABLE Table1(Column1 INT)
With the following values:
INSERT INTO Table1 VALUES (NULL)
INSERT INTO Table1 VALUES (0)
INSERT INTO Table1 VALUES (1)
You might think that the following two queries produce inverse results, but they don’t:
SELECT Column1,
CASE WHEN Column1<>0 THEN 'TRUE' ELSE 'FALSE' END AS Column2
FROM Table1
NULL FALSE
0 FALSE
1 TRUE
SELECT Column1,
CASE WHEN Column1=0 THEN 'TRUE' ELSE 'FALSE' END AS Column2
FROM Table1
NULL FALSE
0 TRUE
1 FALSE
08-Nov-2007
Posted by : Andreas
In reporting it is very common to group a datetime by date / day. There are several ways to do this, and I have done some testing on a large table I have. Listed here are four ways, from best to worst. The performance increase by changing a query from the worst (Which I have seen being used several times!) to the best might be the difference between a query taking 10 minutes and a query taking 10 seconds. CRETS is a datetime column.
--Use this: :)
SELECT DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0),
COUNT(*)
FROM Table1 GROUP BY DATEADD(dd,(DATEDIFF(dd,0,CRETS)),0)
SELECT CAST(DATEDIFF(dd,0,CRETS) AS DATETIME),
COUNT(*)
FROM Table1 GROUP BY CAST(DATEDIFF(dd,0,CRETS) AS DATETIME)
SELECT DATEPART(dd,CRETS) AS [DAY],
DATEPART(mm,CRETS) AS [MONTH],
DATEPART(yyyy,CRETS) AS [YEAR],
COUNT(*)
FROM Table1
GROUP BY DATEPART(dd,CRETS), DATEPART(mm,CRETS), DATEPART(yyyy,CRETS)
--Don't use this:
SELECT CONVERT(DATETIME, CONVERT(NCHAR(10), CRETS, 121)),
COUNT(*)
FROM Table1
GROUP BY CONVERT(DATETIME, CONVERT(NCHAR(10), CRETS, 121))
