Using SQL Server 2000 Technologies to Deliver Data
MSDN Data Access Content Strategist
Microsoft® SQL Server™ 2000
Microsoft® Visual Studio® .NET 2003
Transact-SQL (T-SQL) database programming language
Summary: Learn how to use Notification Services and Reporting Services in SQL Server 2000 to generate and deliver data to your users when they want it. (12 printed pages)
Download the associated TimelyDataSample.exe code sample.
|Download the Example.zip code sample.|
SQL Server provides a lot of useful technologies in addition to the database engine. Two of the newer ones are Notification Services, which is a platform for generating notifications based on subscriber requests, and Reporting Services, which is a platform for generating Web-based reports. Both of these technologies are about getting relevant data out to the end user. While Reporting Services provides subscriptions based on two built-in event types (TimedSubscription and SnapshotUpdated), you can leverage Notification Services to provide subscriptions based on a custom event source. In this article, I'll walk you through building a simple application that uses Notification Services and Reporting Services together to gather, format, and distribute data. After all, why make people hunt down the data they need when you can give them just what they want when they want it?
For the sample application, you need to have SQL Server 2000 and Visual Studio .NET 2003 installed, and you must also download and install:
- The sample files available in the associated downloadable file. Extract this .zip file to C:\ for the correct directory structure.
- SQL Server 2000 Notification Services, Standard Edition 2.0 SP1
- Reporting Services. Make sure you include the AdventureWorks2000 sample database when you install, as we will use that to provide sample data to the application.
The sample application is built to run with all components on a single server, using Windows® authentication for SQL Server logins.
Let's say that your floor managers want regular inventory reports, to see what needs re-ordering. We'll use a Notification Services subscription to store information about the managers that want to be notified, and what product type they want inventory information about. Notification Services events are used to populate and constantly refresh a table containing information on products with low inventory levels. Whenever a subscription comes due (Notification Services handles triggering of scheduled subscriptions automatically), a notification is generated based on the latest cached event information. This notification will contain a parameterized URL to a Reporting Services report. When the user clicks the link, a custom report containing details on the inventory levels will be displayed.
Notification Services has an unusual programming model. Installing Notification Services puts the command line tools and the APIs on your machine, but not a running instance of Notification Services. To get that, you must create at least two XML metadata files, one of which—the configuration file—contains the information for the Notification Services instance. The other, called the application definition file (ADF), contains the information for a specific application that the instance hosts. You can have multiple ADFs per configuration file. Think of it as an instance of SQL Server and the databases it hosts—it's a similar model. Once you have these files, you run NSControl, a command line tool. This creates the Windows service and the SQL Server databases for the application by using the information in the metadata files.
In this sample, we'll use a configuration file and a single ADF. I'll give you a quick overview of the information in the metadata files here. For more extensive coverage of these files, as well as Notification Services architecture and general programming, check out the Notification Services Books Online.
Go ahead and open the appConfig.xml file (installed at C:\Example). It identifies the applications that the instance contains, and also specifies system parameters and delivery channel settings. The system parameters allow me to pass in values at application creation time to avoid hard-coding things like server names in the file. The delivery channel is a combination of a delivery protocol, such as SMTP or HTTP, plus any information needed to send a notification via that protocol. If you take a look at the <DeliveryChannels> node, you'll see I use the standard SMTP delivery protocol that ships with Notification Services for e-mail delivery of notifications. Now you can close this file.
Open up appADF.xml (installed at C:\Example\AppDefinition) in your favorite XML editor (I like Visual Notepad, myself). This file defines the schemas of the events and subscriptions that the application accepts, and of the notifications it produces. It also contains rules that determine how events and subscriptions are matched to produce notifications and information about the event provider, which is the component that submits events to the system. Let's take a brief look at the key nodes in this file.
The <EventClasses> node contains a definition of the InventoryEvents event class. It identifies the fields that an incoming event must contain. It also contains a definition for a chronicle table, which is just an additional table for storing application information. In this case, we want to use the chronicle table to store the latest event information, so that whenever a scheduled subscription runs, there is always current event data available to match against the subscription information. So we create this table and an associated rule, which uses T-SQL statements to refresh the chronicle data each time events come in.
The <SubscriptionClasses> node contains a definition of the InventorySubscriptions subscription class. This class identifies the fields that an incoming subscription must contain. It also contains a rule that determines how events and subscriptions are matched to produce notifications. The InventoryNotificationsNotify function (a user-defined function generated by Notification Services when the application is created) is used in the rule to create notifications for scheduled subscriptions. The criteria used to determine if a notification is generated is if there is event data in the event chronicle table that has a ProductCategoryID that matches a ProductCategoryID specified in one of the subscriptions.
The <NotificationClasses> node contains a definition of the InventoryNotifications notification class. It defines the fields that a notification will contain. In this case, we are only generating a few fields to use in the notification—all of the additional inventory information will be provided in the Reporting Services report that is based on the ProductCategoryID contained in the notification. It also contains information on the formatter that will format the notification data for display (the standard XSLT formatter that comes with Notification Services is used here), and on the method of delivery that will be used for this notification—SMTP e-mail, in this case.
Note In this node, you'll want to change the From field for the SMTP protocol from 'email@example.com' to a valid e-mail address on your system.
Finally, the <Providers> node contains settings for the event provider. I am using the standard SQL Server event provider that comes with Notification Services to execute the GetInventoryLevels stored procedure (code in the StoredProc.sql sample file in C:\Example\AdditionalFiles) in the AdventureWorks2000 database. This event provider is set to run once per minute. Next, save and close the file.
You have your metadata files. Now what? First, create an account for your Notification Services service to use. Create a local account on your machine, and give it administrative privileges. Yes, this is bad, I know, but is required in order to use the SMTP service—for other delivery options you can usually avoid assigning admin privileges. For production applications, you'll want to talk to your systems administrator and get a proper domain account set up for your service.
Give that account a SQL Server login. It will need to be added to the System Administrators server role, and granted database access to the master and AdventureWorks2000 databases using the public role.
Open up the Notification Services command prompt (available on the Notification Services group on your Programs menu) and type:
This utility grants your service account the ability to run the Notification Services extended stored procedures, which it will need to do when creating the application. Note that you shouldn't need to enter the domain, just the account, since it is local.
Create the event submission stored procedure. Open the C:\Example\AdditionalFiles\StoredProc.sql file, and copy the contents. In the AdventureWorks2000 database, create a new stored proc, and paste in the copied text. Give the Notification Services account execute permissions on the new stored procedure.
Once your account is set up, it is time to create the Windows service for your application. In the Notification Services command prompt, type:
NSControl Register –name example –server "YourDatabaseServerName" –service –serviceusername "YourAccountName" -servicepassword "YourAccountPassword"
If you are using a named instance of SQL Server, you must specify "YourDatabaseServerName" in the format of ServerName\InstanceName. Running NSControl Register will create necessary registry entries for the application, and create the Windows service that it will use.
Next, create the databases that store the application data. In the Notification Services command prompt, type:
NSControl Create –in c:\example\appconfig.xml DBSystem=YourServerName NSSystem=YourServerName BaseDirectoryPath=C:\example SmtpServer=smarthost
–in parameter tells NSControl what metadata files to use to create the instance and application. The additional parameters are all application-specific parameters that I defined in the configuration file, so that I wouldn't have to hard-code system information. They take the name of the database server, the name of the Notification Services server (where the Windows service resides), the base directory for the application files, and the name of the SMTP server. You can replace the SmtpServer value with a different SMTP server name if you do not use Microsoft Exchange, or if your e-mail system does not use a smart host to route e-mail. Leave the command prompt open, as we will use it again later.
Once NSControl Create has completed, open SQL Server Enterprise Manager. You will see two new databases—ExampleNSMain, which contains instance data, and ExampleInventory, which contains application data. Grant database access to the Notification Services service account login for both of these databases, and add the account to the NSRunService role they both contain.
Now the notification application is created, but we haven't set it to run yet. Before we do that, let's put together a simple report to display the inventory data for the subscriber.
You can use Reporting Services and the Visual Studio Report Designer for developing your reports. Let's take a look at developing a basic report.
Open a new project in Visual Studio .NET 2003. In your Project Types pane, you will see a new entry for Business Intelligence Projects. Select this, and then select the Report Project template. Type InventoryReport for your project name, and click OK.
In Solution Explorer, right-click the Shared Data Sources folder, and then click Add New Data Source. Enter connection information for the AdventureWorks2000 database and click OK.
In Solution Explorer, right-click the Reports folder, click Add, click Add New Item, and then select the Report template. Name it InventoryReport.rdl and then click Open. The report will open up in design view on the Data tab.
In the Dataset drop-down list, select <New Dataset...>. In the Dataset dialog box, accept the defaults and cut and paste the following T-SQL into the Query string field:
select p.Name AS ProductName, c.ProductCategoryID, c.Name AS ProductCategory, p.SafetyStockLevel, Sum(i.Quantity) AS CurrentLevel, p.ReorderPoint From product p inner join productinventory i on p.productid = i.productid left outer join productsubcategory x on p.productsubcategoryid = x.productsubcategoryid left outer join productcategory c on x.productcategoryid = c.productcategoryid group by p.name, c.name, p.safetystocklevel, p.ReorderPoint, p.productid, c.ProductCategoryID, p.productsubcategoryid having p.SafetyStockLevel > Sum(i.Quantity) and p.productsubcategoryid is not null and c.ProductCategoryID = @ProductCategoryID
Click OK. You'll now see the query on the Data tab in the SQL pane.
You'll notice that I included a query parameter in the HAVING clause of my T-SQL statement. Reporting Services recognizes this and automatically creates a corresponding report parameter to handle it. We'll want to change some of the parameter settings from the defaults, though.
First, let's add another Dataset that can be used to provide values to the parameter. In the Dataset drop-down list, select <New Dataset...>.
In the Dataset dialog box that opens, accept the defaults, and cut and paste the following T-SQL into the Query string field:
SELECT * from ProductCategory
Click OK to save the new Dataset.
Go to the Report menu and click Report Parameters. You'll see there is a ProductCategoryID parameter to handle the @ProductCategoryID parameter specified in the T-SQL. Make the following changes in this dialog box:
- Change the Prompt field value from "ProductCategoryID" to "Product Category".
- Select the From query radio button in the Available values set. Change the values for the query-related fields as follows: Dataset="DataSet2", Value field="ProductCategoryID", Label field="Name".
Now when you are previewing the report, you can change the report by selecting the product category you want to see instead of entering a ProductCategoryID. Your selections should now look like this:
Figure 1. Creating the report parameters
Click OK to exit the dialog.
Next, click the Layout tab in the report designer. Since the report will be displaying data for potentially multiple products but only one product category at a time, a table makes more sense for data display than a matrix. In the toolbox, drag a Table control onto the surface.
Right-click any of the columns and insert an additional column. Position the table to allow room above it for a text box that we will insert later.
Drag the ProductName, SafetyStockLevel, CurrentLevel, and ReorderPoint fields over one at a time and drop them onto the Details row of your Table control. Adjust the columns as necessary to accommodate the data. The Header row should automatically populate with appropriate labels.
Drag a textbox control onto the surface above the table. Expand it to be the same width as the table. For the Value property, enter:
="Inventory for " + Parameters!ProductCategoryID.Label + " Product Category"
Change the TextAlign property to Center, and bump up the font size and boldness a bit. Your report layout should now look like this:
Figure 2. Creating the report layout
Click the Preview tab see what we have so far. You'll notice you don't get any data, since there is no default value specified for the @ProductCategoryID parameter. So let's go ahead and view the report to see what it actually looks like before we go any farther with the design.
Right-click the project, and select Properties. Make sure that the StartItem value is 'InventoryReport.rdl", the TargetFolder value is "InventoryReport", and the TargetServerURL value is "http://YourServerName/ReportServer". Click OK to close the dialog box.
On the Debug menu, select Start. The report will open with no data showing. Select a product category in the Product Category drop-down list in the upper left of the report window, then click the ViewReport button in the upper right of the report window.
Presto, you have a report to look at, albeit a very simple one. Next, close the report window. Let's fix up the design to make it a bit more attractive.
- Click the Layout tab, select the Detail and Header rows of the table, and set the BorderStyle property to solid.
- Select only the Header row, expand the BorderWidth property, and set the Bottom property to 2pt.
- Expand the Font property set, and set the FontWeight to Bold, and then set the BackgroundColor to AliceBlue.
- Select the Footer row and delete it.
- Select the textbox control and set its BackgroundColor property to SkyBlue.
Your report layout should now look like this:
Figure 3. More report design and layout improvements
Save your changes. On the Debug menu, click Start, select a product category, and then view the report to see how it looks now. Better—yes? As you can see, the formatting is straightforward and easy to pick up if you've worked with pretty much any kind of control before. Feel free to continue to play with the format if you prefer different fonts, colors, etc.
Let's go ahead and deploy this report, so we can access it via a URL in the notification e-mail. On the Build menu, click Deploy Solution. Once the deployment has completed, on the Debug menu, click Start to view the report once more. Note that this time the report comes up in a browser window.
Now that the report is created and deployed, let's add the proper URL for it to the notification formatting and get the notification application running.
The first thing we'll need to do here is add the report URL to the XSLT file that formats the notifications. Open Application.xslt (installed at C:\Example\Transform) in a text editor. Change the server name in the href attribute to your server name. Application.sxlt should now look as follows:
Figure 4. Adding the report URL to the XSLT file that formats the notifications
Take a look at the URL as it is specified in the XSLT file. You'll notice that it is not the same URL that you get when you open the report from the report designer and specify a product category in the user interface. Instead, it is specified in the format:
http://servername/vroot?path to the report &URL parameters&Report parameters
You can take a look at the Reporting Services documentation for more information on what each element in the path specifies. The one we really care about is the report parameter, ProductCategoryID=, which we set equal to the ProductCategoryID in the notification using ProductCategoryID=<xsl:value-of select='ProductCategoryID'/> in the XSLT file.
Next we add some test subscriber and subscription data. Normally you would do this by creating a Web form on top of the Notification Services subscription API. In the interest of expediency, however, we're going to use a script to submit subscriber, subscriber device, and subscription information to the system. Open the Subscriptions.vbs script (installed at C:\Example\AdditionalFiles) in a text editor. This script will create one subscriber with multiple scheduled subscriptions, so that it is easy to test the application. In production, clearly you'd have multiple subscribers with perhaps one or two weekly subscriptions. To receive test data, you'll want to go to the "Add an E-mail device for the subscriber" section and change the "nsSubscriberDevice.DeviceAddress = "firstname.lastname@example.org"" line to contain your e-mail address. Save the changes and close the script.
Now, go back to the Notification Service command prompt and type:
NSControl enable –name example
This enables the notification application to accept data, and allows it to start generating events once we set the Windows service running. It does not start the Windows service, though—we'll do that in a minute.
Now that the application is enabled, double-click Subscriptions.vbs to run it and populate the application with test data. The script will display an "Added Subscriptions" message box when it completes.
We're just about done. The final step is to start the Windows service for the notification application. In the Notification Service command prompt, type:
net start NS$example
That's it. Give it a few seconds, and you should start seeing (lots of) notification e-mails coming into your inbox.
When you are done playing with the application, you can disable it by running
NSControl disable –name example
net start NS$example
from the Notification Service command prompt.
I hope this short sample has illustrated how quick and easy it is to put together a simple application that leverages some of the technologies that you get as part of the SQL Server 2000 package. From the simple application that you have here, it is easy to add embellishments to customize the application for your preferences and environment. In the Notification Services application, for instance, you could add functionality to offer event-driven subscriptions that are delivered when inventory levels or sales numbers hit a certain threshold, rather than at a pre-set scheduled time. In the Reporting Services application, you can certainly create much more complex layouts than the bare bones format that I have introduced you to. Good luck and happy developing as you explore more of what Notification Services and Reporting Services have to offer you!