8/22/13 Using SQL Server Agent to Run Scheduled Reports
Tech Note 573
Using SQL Server Agent to Run Scheduled Reports
All Tech Notes, Tech Alerts and KBCD documents and software are provided "as is" without warranty of any kind. See the Terms of Use for more information.
Topic#: 002326
Created: February 2009
Introduction
This Tech Note provides an example of configuring SQL Server Agent to manage the execution of published reports for ActiveFactory. It also provides an example of configuring a custom event Archiving Rule and Scheduled Report Path for the SQL Server Agent.
Application Versions
Wonderware ActiveFactory 9.2
Wonderware Information Server (WIS) 3.0 Microsoft SQL Server 2005
Configure Event Group
To allow SQL Server Agent to trigger when a report is executed, we need to provide a set of report group types.
Scheduled Report paths and Archiving rules are configured in the Configuration.config file located at C:\Inetpub\wwwroot\AFWeb.
1. Backup the Configuration.Config file.
2. Open the Configuration.Config file with Notepad.
3. Use the Ctrl+F keys to find the WEEKLY element as it appears in Figure 1 (below). 4. Make a copy of the WEEKLY archival rule element.
FIGURE 1: COPYTHE WEEKLY ARCHIVAL RULE
5. Modify the name and description of the new weekly archival rule. In this example, it is called WEEKLYAgent.
FIGURE 2: RENAME AFTER COPYING
For example:
<WEEKLYAgent Description="Rule that applies to all reports assigned to the WEEKLY schedule using sql server agent.">
8/22/13 Using SQL Server Agent to Run Scheduled Reports <FORMATSTRING>MM (MMM)</FORMATSTRING>
</DIRECTORYNAME> <FILENAME>
<FORMATSTRING>'Week of' dd-MM-yyyy</FORMATSTRING> </FILENAME>
<OLDERTHAN>365,0,0</OLDERTHAN> <!-- 365 = one year -->
<KEEPCOUNT>520</KEEPCOUNT>
<!-- 520 = ten years' worth of weekly reports --> </WEEKLYAgent>
5. Create a copy of the Example Report for WEEKLY and name it WEEKLYAgent. You will need this Report Folder Name later.
FIGURE 3: WEEKLYAGENT REPORT ELEMENT
For example: <WEEKLYAgent> <DIRECTORYNAME> <FORMATSTRING>'Weeks of 'MMM-yyyy</FORMATSTRING> </DIRECTORYNAME> <FILENAME>
<FORMATSTRING>'Week of' dd-MM-yyyy</FORMATSTRING> </FILENAME>
</WEEKLYAgent>
6. Create a copy of the WEEKLY Scheduled Report Path and modify the settings so it mimics the behavior of an Event report type.
FIGURE 4: MODIFIED EVENT REPORT SETTINGS
For example:
<WEEKLYAgent>
<FOLDERNAME>Weekly</FOLDERNAME>
<TIMEINTERVAL>0,0,0</TIMEINTERVAL> <!-- Change time interval to Event settings -->
<STARTTIME>-1,*,*,*</STARTTIME>
<!-- -1 => this never fires on its own -->
<DESCRIPTION>Every week (SQL Server Agent)</DESCRIPTION> </WEEKLYAgent>
7. Save the Configuration.Config file. Within 30 seconds, the
aahistclientReportingService rereads this file and identifies the new group. It is then accessible when publishing from ActiveFactory Workbook.
If the new Group does not appear, you may have a error in the file. Look at the OS event viewer.
Publish the Report
8/22/13 Using SQL Server Agent to Run Scheduled Reports
FIGURE 5: PUBLISHA DYNAMIC REPORT
2. Select Report Type Scheduled.
3. In the Schedules list, select the new schedule that we created.
FIGURE 6: SCHEDULED REPORT TYPE
Make sure you remember/save the Report Name as you will need this later. 4. Click OK to publish the report.
5. Report information is stored in the Runtime database on the selected server. The table containing this report is aaHistClientReport.
FIGURE 7: REPORT NAMEINAAHISTCLIENTREPORT TABLE
Schedule Report with SQL Server Agent
8/22/13 Using SQL Server Agent to Run Scheduled Reports
FIGURE 8: CREATEA NEW SQL SERVER AGENT JOB
2. Provide the General page information for this job.
3. Select the Steps page and create a new step.
Provide the Transact SQL script below. Modify the ReportFolderName and ReportName with the names you have noted in previous steps.
UPDATE aaHistClientReport SET LastRun='1753-1-1', Published=0 FROM aaHistClientReport r, aaHistClientReportsFolder f
WHERE r.Name = 'Book1'
AND r.ReportFolderKey = f.ReportFolderKey AND f.ReportFolderName = 'WEEKLYAgent' AND r.ReportSiteKey = 1
4. Select the Schedules page and create a new schedule. In this example, we created a weekly scheduled report. You can create any schedule you like.
Note: The query provided to trigger a report can also be used with the InSQL Event System.
Click the following icon to view this file in .pdf format:
C. Azer
Tech Notes are published occasionally by Wonderware Technical Support. Publisher: Invensys Systems, Inc.,
26561 Rancho Parkway South, Lake Forest, CA 92630. There is also technical information on our software products at Wonderware Technical Support.
For technical support questions, send an e-mail to [email protected].
Back to top
©2013 Invensys Systems, Inc. All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying,
recording, broadcasting, or by anyinformation storage and retrieval system, without permission in writing from Invensys Systems, Inc. Terms of Use.
