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.
11-Jun-2009
Posted by : Andreas
01-Apr-2009
Posted by : Andreas
I have a customer that needed to run a lot of subscriptions on the last day of every month. As you may know, reporting services doesn’t support this type of schedule. After reading on this forum thread, I noticed that there was no straight forward solution to this. Many (my customer included) have tried to solve this by using three different schedules (one for months with 30 days, one for months with 31 days, and one for february). This solution requires 3 times as many subscriptions, and is also very buggy! (See forum link). To solve this, I made a new Shared schedule called LastDayOfMonth, and found the ScheduleID using a query like this:
1
2
3
4
5
| SELECT TOP 1 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 |
It’s important that you set up the shared schedule as “run once”, and preferrably let it run/expire before you proceed to the next step. After the schedule was expired, I looked up the schedule in SQL Server Agent and copied the SQL from the step in the Agent Job. Using the same SQL, I created a new job with the same SQL-statement. The beauty of using the scheduler in SQL Server Agent directly is that it’s much more flexible. Here you can choose “last day of month” as the schedule, and everyone setting up subscriptions in reporting services can from now on use the LastDayOfMonth shared schedule. Everyone is happy :)
What I don’t understand is, that since microsoft is allready using sql server agent as the trigger for the subscriptions, why not use the same flexibility in reporting services that you have in sql server agent?
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”
18-Feb-2009
Posted by : Andreas
Some nice to have date expressions in reporting services:
--First day of week (monday):
=DATEADD("d", -(WeekDay(Today(),2))+1, Today())
--Next monday:
=DATEADD("d", 7-(WeekDay(Today(),2))+1, Today())
--First day of month:
=DATEADD("d",1-DATEPART("d",Today()),Today())
--First day of next month:
=DATEADD("m",1,DATEADD("d",1-DATEPART("d",Today()),Today()))
--First day of year:
=DateSerial(YEAR(Today()),1,1)
--First day of next year:
=DateSerial(YEAR(Today())+1,1,1)
17-Feb-2009
Posted by : Andreas
Yesterday I spent some time trying to write a script that deletes all schedules, subscriptions and related SQL Server agent jobs from a reporting services instance by using cursors and stored procedures in the msdb and Reportserver databases.
Turns out that the reportserver tables have the necessary triggers on delete, so all you need to do is a simple
DELETE FROM ReportSchedule
GO
DELETE FROM Subscriptions
GO
DELETE FROM Schedule
GO
28-Jan-2009
Posted by : Andreas
I spent a few days trying to get a subscription to a file share to another domain to work, before I finally figured out that it’s not possible at all. Tried both SSRS 2005 and SSRS 2008 to no avail.
Ended up with a workaround using a batch script that moves files from the file share on the local SSRS server to the external file share after the subscription is run.
28-Jan-2009
Posted by : Andreas
I just did a migration from a SSRS 2005 installation to SSRS 2008 instance on a different server. Microsoft has very nice guides about the migration process that I followed, but I encountered problems when trying to restore the encryption key on the new server. I tried to post the question to the msdn message board here, but got no response.
I’m currently trying to move a ssrs 2005 installtion to a sql server 2008 instance on another physical server. I’ve followed every step on the official migration guide, and the database is upgraded and all data is there. The problem is when I try to restore the encryption key on the 2008-server. The old server then shows up under “scale-out deployment”, and is impossible to remove. If I go to the 2008 report manager, I get “The feature: “Scale-out deployment” is not supported in this edition of Reporting Services”. If I try to remove the old server from the “scale-out deployment”-tab, I get: “Microsoft.ReportingServices.WmiProvider.WMIProviderException: No report servers were found. I tried to delete all encrypted data on the 2008 server, and that made the 2008-instance work, but I have 150 subscriptions on the old 2005-instance that I don’t want to recreate (understatement).
The solution was to use the rskeymgmt utility by command line interface when restoring the key:
rskeymgmt -i NEWINSTANCE -a -f FILEPATH -p PASSWORD
If you use the configuration tool, the restored key will be linked to the wrong instance, and the old server will show up as a scale-out deployment server.
UPDATE:
Its easier to just just do the following after you restore the key using the configuration tool:
USE ReportServer
DELETE FROM Keys WHERE MachineName='OLDMACHINENAME'
23-Jan-2009
Posted by : Andreas
I have installed a new theme on the site (fusion), and updated the wordpress version. Things work better now, but I need to fix some highlighting bugs.
