Using the Built-in Event Providers


SQL-NS provides three built-in event providers:

  • The FileSystemWatcherProvider An event provider that reads event data from XML files in the filesystem

  • The SQLProvider An event provider that runs a SQL query on a periodic interval to obtain event data

  • The AnalysisServicesProvider An event provider that runs dynamic multidimensional (MDX) queries to obtain event data from a cube.

All three are hosted event providers, so they run inside the SQL-NS engine. To use them in an application, you simply configure them in the ADF. When the SQL-NS engine starts, it reads the configuration information and loads the required event provider components.

This section covers two of the three SQL-NS built-in event providers: the FileSystemWatcherProvider and the SQLProvider. We look at what these event providers do and how to configure them for use in SQL-NS applications.

The third event provider, the AnalysisServicesProvider, is not covered here because its use involves multidimensional analysis, which is beyond the scope of this book. To learn about the AnalysisServicesProvider, consult the SQL-NS Books Online, which provides complete reference documentation, including code samples.

The FileSystemWatcherProvider

The FileSystemWatcherProvider works by monitoring a directory in the filesystem; when it sees a new XML file added to that directory, it opens the file and submits the event data in it. The FileSystemWatcherProvider is useful in cases where event data can be written to XML files either by the event source directly or by an intermediate process.

To use the FileSystemWatcherProvider, you configure it in a <HostedProvider> element in the ADF. Listing 8.2 shows the <HostedProvider> declaration we can add to the music store application's ADF to configure it to use the FileSystemWatcherProvider.

Listing 8.2. The FileSystemWatcherProvider Declaration in the ADF

 <Application> ...   <Providers>     <HostedProvider>       <ProviderName>SongAddedFSWProvider</ProviderName>       <ClassName>FileSystemWatcherProvider</ClassName>       <SystemName>%_NSServer_%</SystemName>       <Arguments>         <Argument>           <Name>WatchDirectory</Name>           <Value>%_ApplicationBaseDirectoryPath_%\EventsWatchDirectory</Value>         </Argument>         <Argument>           <Name>SchemaFile</Name>           <Value>%_ApplicationBaseDirectoryPath_%\SongAddedEventSchema.xsd</Value>         </Argument>         <Argument>           <Name>EventClassName</Name>           <Value>SongAdded</Value>         </Argument>       </Arguments>     </HostedProvider>     ...   </Providers> ... </Application> 

Within the <HostedProvider> element, the <ProviderName> element assigns a "friendly name" to the event provider that can be used for configuration, administration, and reporting purposes after the application is created. The <ClassName> indicates the event provider type (or class); in this case, FileSystemWatcherProvider. You can have many instances of the FileSystemWatcherProvider in a single application (each perhaps monitoring a different directory in the filesystem). For each instance, you would need to provide a <HostedProvider> declaration that specifies FileSystemWatcherProvider as the class name, but provides a unique provider name. The key point is that FileSystemWatcherProvider is the event provider type, not the event provider name.

The <SystemName> element specifies the machine on which the event provider should run. If you're using the Standard Edition of SQL-NS, all the components of your application must run on the same machine, but if you're using Enterprise Edition, you can configure components to run on different machines.

The <Arguments> element supplies values for the three arguments required by the FileSystemWatcherProvider:

  • WatchDirectory specifies the directory in the filesystem that the FileSystemWatcherProvider should monitor.

  • SchemaFile specifies the full path to an XSD schema file that describes the data in the event files that will be dropped in the watch directory.

  • EventClassName specifies the event class of the events the provider will submit (the files dropped in the watch directory must contain data corresponding to the fields of this event class).

These three arguments are required by the FileSystemWatcherProvider. If you don't supply them, the event provider will fail to start.

In the case of the event provider configuration shown in Listing 8.2, the watch directory specified is a subdirectory off the application's base directory. The event class name specified is SongAdded, the name of the only event class in the music store application. Based on this declaration, the event provider will expect to submit SongAdded events, so the event files that it picks up from the watch directory must contain SongAdded event data. The schema file specified describes the structure of the XML data in event files.

Event Files and Event File Schemas

The event files processed by the FileSystemWatcherProvider contain event data in XML form. In this section, we look at a sample event file containing a few SongAdded events and examine its structure. When we test the FileSystemWatcherProvider later in this section, we'll use this event file. On your system, you'll find it at C:\SQL-NS\Chapters\08\DataFiles\EventData.xml. Listing 8.3 shows the contents of this file.

Listing 8.3. A Sample Event File Used by the FileSystemWatcherProvider

 <eventData>     <SongAdded>         <SongId>1</SongId>     </SongAdded>     <SongAdded>         <SongId>6</SongId>     </SongAdded>     <SongAdded>         <SongId>12</SongId>     </SongAdded>     <SongAdded>         <SongId>14</SongId>     </SongAdded> </eventData> 

The root element is <eventData> (note the lowercase "e"). The FileSystemWatcherProvider requires that all event files have <eventData> as the root element, but the contents within <eventData> vary per event class. There must be one XML element within <eventData> for each event to be submitted, and the structure of this element must be defined in the schema file referenced in the SchemaFile event provider argument.

In the example shown in Listing 8.3, each SongAdded event is defined in a <SongAdded> element. This element has a subelement for the SongId event field, called <SongId>. The outer element that encapsulates the event can have any name you choose. It's just a convention to give this element the same name as the event class, as this example does. However, the subelements of the event must be named the same as the event class fields that they correspond to.

The schema file referenced in the SchemaFile event provider argument in Listing 8.2 describes the structure of the <SongAdded> element in the event data file. The purpose of this schema file is to tell the FileSystemWatcherProvider how to map the XML definition of an event to the event fields defined in the ADF. You can find this schema file on your system at C:\SQL-NS\Samples\MusicStore\SongAlerts\SongAddedEventSchema.xsd. Listing 8.4 shows its contents.

Listing 8.4. Schema File Used by the FileSystemWatcherProvider to Process SongAdded Events

[View full width]

 <?xml version="1.0" encoding="utf-8" ?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn :schemas-microsoft-com:mapping-schema" >     <xsd:element name="SongAdded" sql:relation="SongAdded" >         <xsd:complexType>             <xsd:sequence>                 <xsd:element name="SongId" type="xsd:integer" />             </xsd:sequence>         </xsd:complexType>     </xsd:element> </xsd:schema> 

This schema is defined in XSD (a standard for XML schemas) and annotated with attributes defined by SQLXML. These attributes provide the mapping between the elements of the XML and SQL data. Schemas with these SQLXML attributes are called SQL-annotated schemas. This section covers only the parts of the schema that are relevant in the context of the FileSystemWatcherProvider. For complete information on SQL-annotated schemas, see the "SQLXML" section in the Microsoft MSDN Library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/anch_SQLXML.asp.

The schema shown in Listing 8.4 defines the structure of the <SongAdded> element that we saw in Listing 8.3. Note the use of the sql:relation attribute that specifies the name of the event class. Whenever you define a schema for event data used with the FileSystemWatcherProvider, you must provide this attribute on the root element, with a value that specifies the event class name.

Caution

The event schema file does not define the outermost <eventData> element in the event file. All event files must have <eventData> as the root element (like the one in Listing 8.3 does). The schema file just defines the structure of the elements that appear within <eventData>.


The schema defines a subelement for each field in the event class. Each subelement must have the same name as its corresponding event field and its declared data type must be equivalent to the event field's SQL data type (as specified in the <FieldType> element in the event class declaration in the ADF). In Listing 8.4, the schema defines an element for the SongId event field and specifies its type as xsd:integer.

The Runtime Behavior of the FileSystemWatcherProvider

When the FileSystemWatcherProvider starts, the SQL-NS engine passes it the argument values specified in the ADF. The FileSystemWatcherProvider validates these arguments and then begins monitoring the specified directory.

Any XML files in the watch directory that have not been processed at the time that the event provider starts are processed immediately. The FileSystemWatcherProvider opens these files, reads the XML data in them, and submits the corresponding event data to the SQL-NS application. It then waits for new files to appear in the watch directory. Whenever a new file appears, the FileSystemWatcherProvider processes it immediately.

All the events in a single XML event file are treated as a batch. Thus, the FileSystemWatcherProvider will create one event batch for each file it processes.

When the FileSystemWatcherProvider successfully processes a file, it renames that file according to the following pattern:

 <OriginalFileName>.<Timestamp>.done 


<OriginalFileName> represents the original name of the file; <Timestamp> is a timestamp that indicates when the file was processed. For example, if the original filename was Eventdata.xml, after processing, the filename might be

 EventData.xml.20040429-102956.31.done 


If the FileSystemWatcherProvider encounters an error while processing a file, it renames that file with a .err extension. Files with this extension are not picked up again.

Tip

If the FileSystemWatcherProvider encounters errors, it will log detailed error messages to the Application Event Log. To see these messages, open the Event Viewer from Administrative Tools and look for messages in the Application log with NotificationServices as the event source name.


Note

Note that the FileSystemWatcherProvider only monitors for new file additions to the watch directory. It does not detect changes to existing files within that directory.


Permissions Required by the FileSystemWatcherProvider

As described in the "Event Provider Security" section (p. 236) earlier in this chapter, membership in the NSEventProvider database role is required to perform event submission operations in a SQL-NS database. In the case of the FileSystemWatcherProvider, filesystem permissions are also needed. Specifically, the Windows account under which the FileSystemWatcherProvider runs (usually the SQL-NS service account) needs both read and write permissions to the events watch directory. Write permissions are needed because the FileSystemWatcher renames the event files after it processes them (as described in the previous section).

Permissions on the events watch directory for the music store sample application are granted by the script C:\SQL-NS\Chapters\08\Scripts\setup.cmd, which was run during the setup steps when you first configured your development environment. When you create your own applications, you'll need to grant read and write permissions on any events watch directories to the service account yourself.

Tip

If the FileSystemWatcherProvider does not have sufficient privileges to read from or write to the events watch directory, event data will not be submitted to the SQL-NS application and the event files will not be renamed. If this occurs, it may appear that the FileSystemWatcherProvider is not doing anything. If you see this behavior, check the Application Event Log for error messages from the FileSystemWatcherProvider.


Testing the FileSystemWatcherProvider in the Music Store

Application In this section, we add the FileSystemWatcherProvider configuration (shown in Listing 8.2) to the music store application and then test it with the sample event file shown in Listing 8.3. The instructions in this section assume you've completed the steps in the "Starting from a Clean Instance" section (p. 241) earlier in this chapter. If you did not complete those steps earlier, do so now before proceeding.

Complete the following steps to get the music store application running with the FileSystemWatcherProvider:

1.

Add the code shown in Listing 8.2 to the music store application's ADF (C:\SQL-NS\Samples\MusicStore\SongAlerts\ApplicationDefinition.xml). You can either enter this code manually or copy over the supplementary ADF, ApplicationDefinition-7.xml, from the Chapter 8, "Event Providers," supplementary files directory, C:\SQL-NS\Chapters\08\SupplementaryFiles.

2.

From a Notification Services Command Prompt on your development machine, navigate to the music store sample's scripts directory by typing the following command:

 cd /d C:\SQL-NS\Samples\MusicStore\Scripts 


3.

Run disable.cmd.

4.

If the instance's Windows service is running, stop it by typing the following command:

 net stop NS$MusicStore 


5.

Run update_with_argument_key.cmd to update the instance. As before, nscontrol update summarizes the changes it will apply to the database and prompts for confirmation. Respond with y to accept the changes.

6.

Run enable.cmd.

7.

Start the instance by typing the following command:

 net start NS$MusicStore 


Note

Steps 2 to 7 are almost the same as the steps you used in previous chapters to update the music store instance. The only difference is that the script update_with_argument_key.cmd is used, instead of update.cmd. This is required because the instance now uses argument encryption. From now on, whenever you're instructed to update the instance, you should use these steps.

If you accidentally invoke the update.cmd script (which does not supply the -argumentkey parameter to nscontrol update) instead of update_with_argument_key.cmd, you'll receive an error message that reads:

 The EncryptArguments value is true; you must provide an argument key when you create or update the instance. 



The output from nscontrol enable (invoked in step 6 of the preceding instructions) should now list the new FileSystemWatcherProvider event provider as one of the components of the application. Using the following steps, copy an event data file into the directory being monitored by the FileSystemWatcherProvider to see it in action:

1.

From a Notification Services Command Prompt on your development machine, copy the sample event data file into the events watch directory with the following command:

 copy C:\SQL-NS\Chapters\08\DataFiles\EventData.xml C:\SQL-NS\Samples\ MusicStore\SongAlerts\EventsWatchDirectory 


2.

Look at the contents of the events watch directory by executing the following command:

 dir C:\SQL-NS\Samples\MusicStore\SongAlerts\EventsWatchDirectory 


Within a few seconds, you should see the event file you copied in (Eventdata.xml) renamed to the form Eventdata.xml. .<Timestamp>.done.

3.

In Management Studio, execute the following query to see the data in the events table:

 USE MusicStore SELECT * FROM [SongAlerts].[NSSongAddedEvents] 


In the event rows returned by the query in the last step, you should see the song IDs that were specified in the events file (shown in Listing 8.3). Also, notice that all the event rows have the same event batch ID. This is because the FileSystemWatcherProvider submits all the events in a file as a single batch.

The SQLProvider

The SQLProvider is the second built-in event provider that SQL-NS offers. Its purpose is to provide an easy way to submit event data into SQL-NS applications from event sources in SQL databases.

Note

In the SQL-NS Books Online and other documents, the SQLProvider is sometimes referred to as the SQL Server Event Provider.


The SQLProvider works by periodically executing an application-defined SQL query at a specified interval. The query reads data from one or more tables that serve as event sources. Whatever data the query returns, the SQLProvider submits as a batch of events. The execution of the query and the submission of its results as events both happen in the database. Thus, the SQLProvider facilitates the transfer of the event data from the source tables to the SQL-NS application, without that data having to leave the database.

To see why this is useful, consider how events originate in the music store application. When a song is added to the music store's inventory, a record for that song is added to the songs table in the music store catalog. An event containing the ID of that song then needs to be submitted to the SQL-NS application. Using an event provider that operates outside the database (such as FileSystemWatcherProvider), the song ID would have to be read from the music store catalog in the database, copied into the event provider's memory, and then copied back into the database to be inserted into the SQL-NS application's events table. Using the SQLProvider eliminates the copying of the event data into the event provider's memory. The SQLProvider can be configured with a query that returns the IDs of the new songs in the music store catalog, and it will execute this query in such a way that the results are submitted directly to the SQL-NS application's events table.

Using the SQLProvider

The SQLProvider is a hosted event provider, so you declare it in a <HostedProvider> element in your ADF. In the declaration of the SQLProvider, you specify the query that the event provider should run. This is referred to as the events query. You also specify the interval period on which the events query should be invoked. When the event provider starts, it invokes the events query regularly on this interval and treats whatever data the query returns as a batch of events.

To use the SQLProvider with the music store application, we need to design an events query that returns the appropriate event data. This will be a query over the songs in the music store catalog that returns the IDs of the new songs. We can define "new songs" to mean songs added since the previous time the query was run. If we keep track of the time at which the events query last ran, we can determine which songs are new by looking at the times they were added to the database. (The music store database already tracks the time each song was added.) The query should return only the IDs of songs added after the last time the events query ran.

Keeping track of the last time at which the events query was run requires a chronicle. Recall that a chronicle is a SQL table that can be used to keep application state. To support the events query for the SQL provider, we need to add a new chronicle to the music store ADF. Listing 8.5 shows this chronicle declaration.

Listing 8.5. The SQLProviderChronicle Declaration in the ADF

 <Application>   ...   <EventClasses>     <EventClass>       <EventClassName>SongAdded</EventClassName>       ...       <Chronicles>         ...         <Chronicle>           <ChronicleName>SQLProviderChronicle</ChronicleName>           <SqlSchema>             <SqlStatement>               --Handle update by dropping the table               --first if it exists.               IF EXISTS (                   SELECT so.name                   FROM sys.objects so                   JOIN sys.schemas sc ON so.schema_id = sc.schema_id                   WHERE so.name = 'SQLProviderRunTime'                       AND so.type = 'U'                       AND sc.name = 'SongAlerts'               )                   DROP TABLE [SongAlerts].[SQLProviderRunTime]             </SqlStatement>             <SqlStatement>               CREATE TABLE [SongAlerts].[SQLProviderRunTime]               (                   LastRunTime DATETIME NOT NULL               )             </SqlStatement>             <SqlStatement>               -- GRANT permissions on the table.               GRANT SELECT ON [SongAlerts].[SQLProviderRunTime] TO [NSEventProvider]               GRANT UPDATE ON [SongAlerts].[SQLProviderRunTime] TO [NSEventProvider]               GRANT SELECT ON [SongAlerts].[SQLProviderRunTime] TO [NSRunService]               GRANT UPDATE ON [SongAlerts].[SQLProviderRunTime] TO [NSRunService]             </SqlStatement>             <SqlStatement>               INSERT INTO [SongAlerts].[SQLProviderRunTime](LastRunTime)               VALUES (GETUTCDATE())             </SqlStatement>           </SqlSchema>         </Chronicle>       </Chronicles>       ...     </EventClass>   </EventClasses>   ... </Application> 

This chronicle consists of a table with a single column, LastRunTime, that holds the last time at which the SQLProvider's events query was run. The first <SqlStatement> in the chronicle definition drops the table if it already exists. (As in previous examples, this is needed to handle updating the instance.) The second <SqlStatement> creates the table.

The next <SqlStatement> grants SELECT and UPDATE permissions on the chronicle table to the NSEventProvider and NSRunService roles. This is needed because for security reasons, the events query runs in a low-privilege context. The user running the events query (this is always the account with which the SQL-NS engine connects to the database) must be explicitly granted any permissions needed by the events query. As you'll see shortly, our events query both selects from and updates the chronicle table, so SELECT and UPDATE permissions are needed. Because the SQL-NS engine's account will always either be a member of NSRunService (the consolidated role that covers all operations handled by the engine) or NSEventProvider (the role specific to event submission), it's sufficient to grant these roles the needed permissions.

The final <SqlStatement> in the chronicle definition initializes the table with the current UTC time. This ensures that on the first execution of the events query, it picks up only the songs added after the chronicle was created.

Notice that we have not defined any event chronicle rules for this chronicle. Even though this chronicle is defined as part of the SongAdded event class, it is not updated by event-triggered chronicle rules. Rather, this chronicle will be updated as part of the events query that the SQLProvider runs.

Let's take a look at the <HostedProvider> declaration for the SQLProvider, which includes the definition of the events query. This is shown in Listing 8.6.

Listing 8.6. The SQLProvider Declaration in the ADF

 <Application>   ...   <Providers>     ...     <HostedProvider>       <ProviderName>SongAddedSQLProvider</ProviderName>       <ClassName>SQLProvider</ClassName>       <SystemName>%_NSServer_%</SystemName>       <Schedule>         <Interval>P0DT00H00M30S</Interval>       </Schedule>       <Arguments>         <Argument>           <Name>EventsQuery</Name>           <Value>             -- Declare variables used to store the last and current             -- run times.             DECLARE @lastRunTime    DATETIME             DECLARE @currentRunTime DATETIME             -- Select the last run time from the chronicle table.             SELECT  @lastRunTime = LastRunTime             FROM    [SongAlerts].[SQLProviderRunTime]             -- Obtain the current run time.             SELECT  @currentRunTime = GETUTCDATE()             -- Update the chronicle, setting the current run time             -- as the last run time.             UPDATE  [SongAlerts].[SQLProviderRunTime]             SET     LastRunTime = @currentRunTime             -- Select all the songs added between the last run time             -- and the current run time.             SELECT  sd.SongId             FROM    [Catalog].[SongDetails] sd             WHERE   sd.DateAdded &gt; @lastRunTime             AND sd.DateAdded &lt;= @currentRunTime           </Value>         </Argument>         <Argument>           <Name>EventClassName</Name>           <Value>SongAdded</Value>         </Argument>       </Arguments>     </HostedProvider>     ...   </Providers>   ... </Application> 

This declaration assigns a provider name and specifies the provider class as SQLProvider. As with previous event provider declarations you've seen, the <SystemName> element specifies the machine on which the event provider should run.

The <Schedule> element specifies the schedule on which the events query should be invoked. The <Schedule> element can actually have two subelements, <StartTime> and <Interval>, although only <Interval> is shown here. (<StartTime> is optional.)

Note

The <StartTime> element often causes confusion, and I've seldom seen it used in real applications. It does not specify a start time for the events query, as you might assume from its name, but it is used to calculate an offset from the event provider's start time that determines when the events query is first run. Refer to the SQL-NS Books Online for more information about this element.


The <Interval> element specifies the periodic interval on which the events query is run. The value of the <Interval> element is specified in the syntax of the XML duration data type. This syntax is explained in the sidebar "The XSD duration Data Type" (p. 157) in Chapter 5. In Listing 8.6, the interval value is given as

 P0DT00H00M30S 


This specifies a period of 30 seconds (the days, hours, and minutes are zero). The year and month components are omitted entirely. (It's legal to omit components that have zero values.)

Note

The <Schedule> element is not specific to the SQLProvider. It is part of the general <HostedProvider> element and is used to specify a schedule for any scheduled, hosted event provider. This same <Schedule> element is used when we configure the custom, scheduled, hosted event provider described in the "Building a Scheduled Custom Hosted Event Provider" section (p. 281).


The <Arguments> element in Listing 8.6 specifies the events query and the event class of the events that the provider will submit. The events query can be a single statement, or a batch of statements, as long as the result contains only a single rowset. In Listing 8.6, the events query is a batch of statements that ultimately selects a set of song IDs.

Note

The SQLProvider also accepts another argument: PostQuery, not shown in Listing 8.6. This argument specifies a query that is run after the events query, to do cleanup or update state information. In this example, the PostQuery is not needed, so it's omitted. The SQL-NS Books Online provide more information on the PostQuery argument.


The first part of the events query declares two local variables: one to hold the last time the events query was run, the other to hold the current time. The last run time value is read from the chronicle table, and the current time is obtained by calling the built-in SQL function GETUTCDATE(). The next statement updates the chronicle table, setting the last time the events query was run to the current time. Finally, the query selects the song IDs from the music store catalog's SongDetails view, where the timestamp is between the last and current runtimes.

The columns returned by the events query must exactly match the fields in the event class. In this case, the SongAdded event class contains only a SongId field, so the query just returns the song IDs of the new songs.

Although this particular events query is specific to the music store, the pattern is fairly common among queries used with the SQLProvider. Because the SQLProvider is periodic in nature, the events query is typically written to return data about events that have occurred between the last time the events query was run and the current time. The semantics of how the times are tracked, and how event data is filtered based on the time intervals, are specific to each application.

When writing this type of events query, you must be careful to avoid race conditions. Remember that while the query itself is executing, new data can arrive in the source tables from which the query reads. In the case of the music store application, this means that new song records could be added to the music store database in the middle of a particular invocation of the events query. It's important that this data be handled correctly. It should be processed, but only once, either during the current invocation of the query, or the next one. The data should never be skipped or processed during both the current and the next invocation.

In the events query shown in Listing 8.6, notice that the value for the current time is obtained once at the beginning of the query and stored in a local variable. This provides an unchanging reference point that can be used throughout the rest of the query. The query will return only the IDs of songs whose timestamps occur before this current time value. Even if songs are added while the query is executing, after the current timestamp is obtained, they will be processed by the next invocation of the query.

Testing the SQL Provider in the Music Store Application

To see the SQLProvider in action, you need to add the code in Listings 8.5 and 8.6 to the ADF. As with previous code additions, you can either manually type the code into C:\SQL-NS\Samples\MusicStore\SongAlerts\ApplicationDefinition.xml, or you can use the supplemental ADF, ApplicationDefinition-8.xml. After adding the code to your ADF, update the music store instance using the same steps you used before (for reference, see steps 27 in the "Testing the FileSystemWatcherProvider in the Music Store Application" section, p. 251).

The SQLProvider looks at the music store catalog to find new songs, so to test it, we need to add a few songs to the music store catalog using the AddSongs program described earlier. The event provider should pick up these new song records and submit them as events.

Use the following steps to test the SQLProvider:

1.

Start the AddSongs program that you built earlier (see "The AddSongs Program," p. 243).

2.

Enter album information and a few song titles (the values you use do not matter, as long as you don't enter songs or albums that are already in the databasejust use the information from your favorite album).

3.

Click the Add to Database button (make sure the Submit Events for Songs Added box is not checked). This writes the song records to the database.

4.

Execute the following query in Management Studio to see the song records in the music store catalog, including the ones you just added:

 USE MusicStore SELECT * FROM [Catalog].[SongDetails] 


When the SQLProvider's events query runs, it will detect the new songs and submit their song IDs as events. Wait at least 30 seconds (recall that the SQLProvider runs the events query every 30 seconds) and then run the following query to see the event data in the events table:

 USE MusicStore SELECT * FROM [SongAlerts].[NSSongAddedEvents] 


You should see rows in the events table containing the song IDs of each of the songs you just added.

If you look at the event batches table for the SongAdded event class ([SongAlerts]. [NSSongAddedEventBatches]), you should see several event batches with event counts of zero. These empty event batches exist because the SQLProvider creates an event batch every time it runs the events query, even if the query returns no data. Except for the extra space the batch records occupy in the event batches table, these empty event batches have no effect on the system: The generator simply ignores them.




Microsoft SQL Server 2005 Notification Services
Microsoft SQL Server 2005 Notification Services
ISBN: 0672327791
EAN: 2147483647
Year: 2006
Pages: 166
Authors: Shyam Pather

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net