Browse DevX
Sign up for e-mail newsletters from DevX


Letting Java in on SQL Server Notifications : Page 2

SQL Server's Notification Services let you instruct the database to let your applications or your users know when data changes or reaches critical thresholds. Furthermore, standard notification delivery methods ensure that notifications aren't limited to .NET or Windows clients.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Creating a Notification Service Instance
Next, you need to create a notification service instance that uses the configuration files described in the previous section. To do that, start the SQL Server Workbench. Select an instance of SQL Server, and then select "Notification Services" from the object browser. Click "New Notification Services Instance…" and you'll see the dialog shown in Figure 1.

Fill out the dialog as shown in Figure 1—but start by setting the configuration file. Click "Browse…" to find the InstanceConfig.xml file and then update the dialog settings to match your system. Be sure to check the "Enable Instance" checkbox at the bottom.

Figure 1. Creating a New Notification Services Instance: To set up the sample notification, fill out the dialog as shown.
Register the Instance
Now you need to register your new notification service instance with SQL Server. To do this, find the notification service instance that you just created in the object browser, right click it and select "Register" from the "Tasks" menu.

Before going to the next step, you should create a new Windows user that Notification Services will use to access the database. In this example I created a standard user called NServices. Using the security node on the object browser, you can then select the "Logins" node and right click it. Select the "New Login" option. From this dialog, select the account that you just created and click "OK."

Now you'll see that the account (i.e. NServices) has become a login to SQL Server, listed under the Logins node. For the StockInstanceNSMain and StockInstanceStock databases you should have the NSRunService role checked (see Figure 2 for details). This example notification service writes notifications to a file in a specific directory (the Notifications directory in the sample application folder in this case). It also reads events from a file in a specific directory (the Events directory in the same folder). Consequently, you need to ensure that the process has sufficient access rights to the file system folders used to drop notification events and delivered notifications.

Figure 2. Configuring Security: On the Login Properties dialog, configure the databases as shown, adding the NSRunService role to both.
To do that, browse to the directory C:\Program Files\Microsoft SQL Server 2005 Samples\Notification Services\Stock using Windows Explorer, right click the directory name, and then click the Security tab. Give the windows account you created earlier (i.e. NServices) file read and write access. Running the Notification Service
Finally, you need to run the Notification Services applications. There are three of those, and you'll find them all in subdirectories of the C:\Program Files\Microsoft SQL Server 2005 Samples\Notification Services\Stock directory. Depending on which language you chose for the sample (VB.NET or C#) the locations of these three notification services differ, but as you browse through the directories you'll find the applications easily. All three are in directories with the same name as the app in question.

First, run HostableExecutionEngine.exe. This application runs in a console and asks you to press the Enter key to exit. Don't exit it yet! You'll use this application to launch your notification service instance. Second, run the AddSubscribers.exe application, which adds subscribers to the services.

Third, and lastly, run the AddSubscriptions.exe application. This, as you can imagine, sets up the subscription parameters for your subscribers. Now that your notification services are up and running, you can test them by finding the EventData.xml file in the root folder of the sample application, and copying it to the Events folder. Notification Services will detect the file drop, and do its thing. After a short time, it will create a new file in the Notifications folder containing the details of the notification.

Applications can then monitor the Notifications folder for updates to this file, and handle them appropriately. In this case, the file contains notifications of when stock prices reach their desired thresholds. An application monitoring this directory could then (for example) send an instant message to subscribers informing them of this fact. Getting SMTP and HTTP Notifications
Notification Services aren't limited to delivering notifications via files. You can also configure notification service instances to deliver notifications on an SMTP channel, so if you have an e-mail server set up that they can access, they can e-mail subscribers for you. In addition, Notification Services support an HTTP channel that your application can extend to allow (among others), SOAP messages to be posted to a Web service, instant messages to be posted through an HTTP gateway, or any other permutations that you can think of.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date