Andrei's Blog

New Life

2009-02-13T14:14:00.001-08:00

I am moving to another blog... Well, a friend of mine recently moved to another country... so I'm quite slow compare to him.

But I also decided to change my last name; I know that it was a challenge for most of you guys to pronounce it.:)

Well, I am also changing my first name... just for fun.

My new blog is here:
http://andre.thedovgals.com/blog

Christopher Alexander and Software Architecture

2006-11-11T12:27:00.000-08:00

Well, everybody knows the history. In mid-70s Christopher Alexander, an architect, famous for his buildings in Japan, USA, and other countries, published three books. A trilogy, in a sense. The books were "The Timeless Way of Building", "A Pattern Language", and "The Oregon Experiment". Actually, he was a co-author of these books with quite a few other authors.

In the beginning of 90s, Erich Gamma with other fellows involved in the object-oriented world of programming read Christoper Alexander's books. May be they just read "A Pattern Language", I don't know. However, in 1995 they published "Design Patterns: Elements of Reusable Object-Oriented Software". Later, on many occasions, they mentioned that the ideas of software patterns and the inspiration to write "Design Patterns" came from Christopher Alexander.

In 1996 Christopher Alexander even made a presentation at The 1996 ACM Conference on Object-Oriented Programs, Systems, Languages and Applications (OOPSLA).

Since then every software architect believed that Christopher Alexander is a father of modern software architecture.

Recently I had a chance to read "A Pattern Language" and "The Oregon Experiment". Well, here are my findings:

1. Christopher Alexander et al. wrote terrific books! I have nothing to do with building construction industry, I have never even had an IT project in this vertical, but I really enjoyed that reading. When I started reading these books, I feel obliged. Am I a software architect or what? However, after the first 30 minutes of reading I found out that the book (I started with "The Oregon Experiment") did not talk about cement, bricks, or Shukhov fourth order differential equations, as I expected. The book rather talked about people, environment, living in a society, bikes and shrubs, senior communities, different cultures, etc.

After a hundred more pages I understood why these books are still bestsellers (godzillions of copies are being sold every year). I could use them for my late night reading. They were easy to read, not intimidating, if you know what I mean.

I had a strange feeling that I was reading a book on Western feng shui as the main point of discussion was ~~right~~ enjoyable way of living.

The books have nothing to do with software design patterns as they were presented by Gamma et al.! Nothing! I should agree with tegusen's blog who wrote "The more I read about Christopher Alexander’s idea of patterns and pattern languages [...], the less I feel that software design patterns have anything to do with it."

Well, end of story? Surely, not!

As you may know, recently I [was] enrolled in the Microsoft Certified Architect program. There are different flavours of the program. One of them, exactly what I will be working on, is called "Solutions Architect Certification".

Who are solution architects? To answer this question, I need to dive deeper into the discussion "Who the Sofware/IT/Computer Industry/etc. Architects Are"...

to be continued...

What's new in SQL Server Notification Services 2005?

2006-06-15T21:00:00.000-07:00

Well, I went through the same exercise with SQL Server Notification Services 2005 as I did with SSNS 2000. Well, it was much easier. May be because I have done it with SSNS 2000.

A. First of all, everything works in the same way. I took my old examples, followed my document for SQL Server 2000, and voila! I had my notification services running.
B. Well, not EXACTLY the same way. You don't need to download and install Notification Services. They come with SQL Server 2005, which is good. And obviously, the installation diractory is different. So, you nscontrol.exe utility is now located in C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin. However, with this exception everything is the same.
C. SQL Server Management Studio now got some add-ons that simplify your work with Notification Services a little. Now in the Object Explorer, once you are connected to your SQL Server 2005, you can see the Notification Service folder. Right click on it, and you can manage your Notification Service Instances.
D. You still have to create a bunch of XML files. And you still have to write code to support subscriptions. Btw, there is a new namespace Microsoft.SqlServer.NotificationServices.Rules which did not exist in the previous version. However, maintaining these files, environment variables, and Windows services is much easier through the new interface.

Well, the bottom line is "Know a) the structure of the XML files and b) architecture of the Notification Services -- then use new tools."

To quickly start developing for SSNS 2005 I suggest to
1. Read Microsoft SQL Server Notification Services Technical Overview.
2. Read my previous article, Demystifying SQL Server Notification Services.
3. Install and run my examples.
4. Briefly read Notification Services Tutorial from SQL Server 2005 Books Online. Don't spend too much time on it, just get familiar with the environment.

And you are ready to go!

PS. The templates in this blog are killing me. However, if you follow the links above, you'll get to my site which is nicely formatted. :)

Demystifying SQL Server Notification Services

2006-06-13T20:40:00.000-07:00

Recently I was doing a small exercise setting up SQL Server 2000 Notification Services (SSNS) to deliver a few e-mails based on certain events in a system. Basically, when the system dropped an error message into the message log table, I wanted to send e-mail to the system administrator with the error description.

Not a big deal, right?

Since I had no manuals or books (fortunately I didn't, I'll explain later why I'm saying so), I looked into the online help. Wow! They had a help file with a lot of examples. I downloaded the examples, and was sure that I could set up my Notification Services using this help in no time.

However, I just made my task more complicated. The example I've chosen did not want to run. I started getting a feeling that I was missing something important. I suddenly stopped understanding what I was doing.

"Why do I need a VS solution to maintain a bunch of XML files?" "What is an NS application?" The number of questions increased. I decided to go over the examples and to extract only the code I really needed. Here is the result of this work, which I would like to share with you.

First of all, you have to install the SS Notification Services. There are two editions of the product: Standard and Enterprise. They both don't need a license additional to your SQL Server license. The Enterprise edition has a few more features: it allows running the Notification Services on the machine other than your SQL Server 2000 machine; it supports multicast delivery; it allows clustering of the Notification Services, etc.

Secondly, you need to create a user. Normally, you would create a domain user and use Windows authentication. Don't forget to create a SQL Server login for the user (or group). For my little exercise I created a local user, and it worked just fine. Your Windows/Database/System administrator may suggest setting up this user based on the company security rules. This should not be a problem.

Now you are ready. Let's start setting up an instance of the Notification Services. An instance in terms of the SSNS terminology is a unit of deployment. In other words, it corresponds with one system. To set up an instance, you need to use the NSControl utility that is a part of the SSNS package. Run Notification Services command prompt (Click “Start – All Programs – Microsoft SQL Server Notification Services – Notification Services Command Prompt) and enter the following command:

NSControl register -name INSTANCE_NAME -server SERVER_NAME -service -serviceusername USER_NAME -servicepassword PASSWORD

INSTANCE_NAME is the name that you will use in your scripts and XML files. The actual name as shown in the Windows list of services will be NS$INSTANCE_NAME. SERVER_NAME is the name of the box (assuming that your service is on the same box with SQL Server), and USER_NAME and PASSWORD are obviously the user name and the password. If you use Windows authentication for your SQL Server, you should not worry about anything else.

I have set up a user with the login name “NSUserOE” and password “SecretPassword” and added a corresponding login to my SQL Server 2000. The user needs to have local admin rights to be able to set up Windows services. To simplify my task, I logged as “NSUserOE”. For my Order Entry application I decided to call the NS instance OrderEntry. I ran SQL Server 2000 on the same computer where I wanted to set up Notification Services, called YVRV-BTS11.

To set up a new instance called OrderEntry, I ran the following command:

NSControl register -name OrderEntry -server YVRV-BTS11 -service -serviceusername NSUserOE -servicepassword SecretPassword

Once the NS instance is set up, you will notice that there is a new Windows service (NS$OrderEntry), which is not running. You have to start it or set it up to start automatically when the system is started, based on your needs and preferences.

Note: to unregister the instance you may use the following command:

NSControl unregister -name INSTANCE_NAME

The next step is developing a NS application. To understand what an NS application was, I probably spent most of my time when learning about Notification Services. And I found that the application was, in fact, a whole bunch of SQL Server tables that described events, notifications, and subscriptions. To make the issue even more convoluted, Microsoft examples show a Visual Studio solution that builds this application by creating the tables.

[Later I opened a 700-page SSNS book. To support Microsoft's idea about an SSNS “application”, the author even called XML a descriptive programming language. I liked the term, but it did not help me understanding SSNS. In fact, everything what I have explained so far, is also explained in that book. The difference is that it takes 172 pages to go through. Just to finish my point I want to add that the rest of the book is not more informative.]

Well, that's what you need:
1. Develop an instance configuration XML file.
2. Develop an “application” definition XML file.
2.5. [Possibly] develop an XSLT file to format the output.
3. Run the NSControl utility to create the tables, oh, well, build the “application”.

To develop the first two XML files, I took the examples, and modified them to my needs.

Instance Configuration XML File (OrderEntryNSConfig.xml):


<?xml version="1.0" encoding="utf-8"?>
<NotificationServicesInstance xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ConfigurationFileSchema">
   <InstanceName>OrderEntry</InstanceName>
   <SqlServerSystem>YVRV-BTS11</SqlServerSystem>

   <Applications>
      <Application>
         <ApplicationName>NSApplication</ApplicationName>
         <BaseDirectoryPath>C:\SSNS Example</BaseDirectoryPath>
         <ApplicationDefinitionFilePath>OrderEntryNSADF.xml</ApplicationDefinitionFilePath>
         <Parameters>
            <!-- These parameters are defined as environment variables or
                as command line arguments.  They are passed to the ADF file. -->
            <Parameter>
               <Name>_DBSystem_</Name>
               <Value>YVRV-BTS11</Value>
            </Parameter>
            <Parameter>
               <Name>_NSSystem_</Name>
               <Value>YVRV-BTS11</Value>
            </Parameter>
            <Parameter>
               <Name>_BaseDirectoryPath_</Name>
               <Value>C:\SSNS Example</Value>
            </Parameter>
            <Parameter>
               <Name>_EventsDir_</Name>
               <Value>C:\SSNS Example\Events</Value>
            </Parameter>
         </Parameters>
      </Application>
   </Applications>

   <DeliveryChannels>
      <DeliveryChannel>
         <DeliveryChannelName>FileChannel</DeliveryChannelName>
         <ProtocolName>File</ProtocolName>
         <Arguments>
            <Argument>
               <Name>FileName</Name>
               <Value>C:\SSNS Example\Notifications\OrderEntryNotification.txt</Value>
            </Argument>
         </Arguments>
      </DeliveryChannel>
      <DeliveryChannel>
         <DeliveryChannelName>EmailChannel</DeliveryChannelName>
         <ProtocolName>SMTP</ProtocolName>
         <Arguments>
            <Argument>
               <Name>SmtpServer</Name>
               <Value>mail.mycompany.com</Value>
            </Argument>
            <Argument>
               <Name>BodyEncoding</Name>
               <Value>utf-16</Value>
            </Argument>
         </Arguments>
      </DeliveryChannel>
   </DeliveryChannels>
</NotificationServicesInstance>

I marked values that are specific to my Order Entry system by green color.
If you use this example to set up your simple Notification Services solution, you may want to change these values.

This file specifies parameters for an NS instance and links this instance to one (or more) “application(s)”. If an instance is set up for one system and is a unit of deployment, an “application” is a unit of development. Each “application” is supposed to create a logical group of notifications relevant to one group of events in the system.

In general, all you have to remember, that you need to create a few SQL Server tables. First of all, there are tables that hold events. An event is a record in this table that will be placed there, when something happens. For instance, an order record can be placed there, when a customer submitted an order. Who does it? You do. You need to develop a mechanism (for example, write a scheduled stored procedure) that will update the table.

The next set of tables is subscription tables. (In my example you will find just one event table and one subscription table). The subscription table defines the records that will be matched with events. You also need to define a subscription rule. A subscription rule is a SQL Server statement that selects records in the event tables matching records in the subscription tables.

Notification tables define what data you want to use in your notifications. SS Notification Services enable several types of notification: sending e-mail, dropping a file on the network, or using HTTP. In my example I set up the first two types of notification, what you may notice in the Instance Configuration XML file.

All those tables and rules are set up using another XML file, Application Definition XML File (OrderEntryNSADF.xml):


<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.microsoft.com/MicrosoftNotificationServices
/ApplicationDefinitionFileSchema">

<Version>
   <Major>1</Major>
   <Minor>0</Minor>
   <Build>1</Build>
   <Revision>0</Revision>
</Version>
<EventClasses>
   <EventClass>
      <EventClassName>;OrdersE</EventClassName>
      <Schema>
         <Field>
            <FieldName>OrderID</FieldName>
            <FieldType>int</FieldType>
            <FieldTypeMods>not null</FieldTypeMods>
         </Field>
         <Field>
            <FieldName>Item</FieldName>
            <FieldType>varchar(100)</FieldType>
         </Field>
         <Field>
            <FieldName>Quantity</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
      </Schema>
      <IndexSqlSchema>
         <SqlStatement>CREATE INDEX DepartmentIndex ON OrdersE ( Department )
         </SqlStatement>
         <SqlStatement>CREATE INDEX PaymentStatusIndex ON OrdersE ( PaymentStatus )
         </SqlStatement>
      </IndexSqlSchema>
   </EventClass>
</EventClasses>

<SubscriptionClasses>
   <SubscriptionClass>
      <SubscriptionClassName>OrdersS</SubscriptionClassName>
      <Schema>
         <Field>
            <FieldName>DeviceName</FieldName>
            <FieldType>nvarchar(255)</FieldType>
            <FieldTypeMods>not null</FieldTypeMods>
         </Field>
         <Field>
            <FieldName>SubscriberLocale</FieldName>
            <FieldType>nvarchar(10)</FieldType>
            <FieldTypeMods>not null</FieldTypeMods>
         </Field>
         <Field>
            <FieldName>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
      </Schema>
      <EventRules>
         <EventRule>
            <RuleName>OrderEntrySubscriptionsEventRule</RuleName>
            <Action>
                           SELECT dbo.OrdersNNotify(
                               s.SubscriberId, s.DeviceName, s.SubscriberLocale,
                               e.OrderID, e.Item, e.Quantity, 
                               e.Department, e.PaymentStatus)
                           FROM OrdersE e INNER JOIN OrdersS s
                           ON e.Department = s.Department
                           WHERE LEFT(e.PaymentStatus, 1) = s.PaymentStatus
            </Action>
            <EventClassName>OrdersE</EventClassName>
         </EventRule>
      </EventRules>
   </SubscriptionClass>
</SubscriptionClasses>

<NotificationClasses>
   <NotificationClass>
      <NotificationClassName>OrdersN</NotificationClassName>
      <Schema>
         <Fields>
         <Field>
            <FieldName>OrderID</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Item</FieldName>
            <FieldType>varchar(100)</FieldType>
         </Field>
         <Field>
            <FieldName>Quantity</FieldName>
            <FieldType>int</FieldType>
         </Field>
         <Field>
            <FieldName>Department</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         <Field>
            <FieldName>PaymentStatus</FieldName>
            <FieldType>char(3)</FieldType>
         </Field>
         </Fields>
      </Schema>
      <ContentFormatter>
         <ClassName>XsltFormatter</ClassName>
         <Arguments>
            <Argument>
               <Name>XsltBaseDirectoryPath</Name>
               <Value>%_BaseDirectoryPath_%</Value>
            </Argument>
            <Argument>
               <Name>XsltFileName</Name>
               <Value>OrderEntry.xslt</Value>
            </Argument>
         </Arguments>
      </ContentFormatter>

      <Protocols>
         <Protocol>
            <ProtocolName>File</ProtocolName>
         </Protocol>
         <Protocol>
            <ProtocolName>SMTP</ProtocolName>
            <Fields>
               <Field>
                  <FieldName>Subject</FieldName>
                  <SqlExpression>'Order Entry Notification'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>BodyFormat</FieldName>
                  <SqlExpression>'html'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>From</FieldName>
                  <SqlExpression>'OrderEntryNotificationService@mycompany.com'
                  </SqlExpression>
               </Field>
               <Field>
                  <FieldName>Priority</FieldName>
                  <SqlExpression>'Normal'</SqlExpression>
               </Field>
               <Field>
                  <FieldName>To</FieldName>
                  <SqlExpression>DeviceAddress</SqlExpression>
               </Field>
            </Fields>

               <!-- Custom Protocol execution parameters omitted for now
               <ProtocolExecutionSettings>
               </ProtocolExecutionSettings>
                    -->
         </Protocol>
      </Protocols>

      <ExpirationAge>PT2H</ExpirationAge>
   </NotificationClass>
</NotificationClasses>

<Providers>
   <NonHostedProvider>
      <ProviderName>EventCreator</ProviderName>
   </NonHostedProvider>
</Providers>

<Generator>
   <SystemName>%_NSSystem_%</SystemName>
</Generator>

<Distributors>
   <Distributor>
      <SystemName>%_NSSystem_%</SystemName>
   </Distributor>
</Distributors>

<ApplicationExecutionSettings>
   <QuantumDuration>P0DT0H0M30S</QuantumDuration>
   <ChronicleQuantumLimit>0</ChronicleQuantumLimit>
   <SubscriptionQuantumLimit>0</SubscriptionQuantumLimit>
   <ProcessEventsInOrder>true</ProcessEventsInOrder>
</ApplicationExecutionSettings>

</Application>

Let’s take a close look at this file.
It consists of several sections that describe different parts of the Notification Services process. First important section defines event tables. The table names will derive from the OrdersE class name. The actual table names will be NSCurrentOrdersEEventBatches, NSCurrentOrdersEEvents, NSOrdersEEventBatches, and NSOrdersEEvents. The event tables will have some predefined fields plus the fields you define in the XML file. In my example, the event records consist of OrderID, Item, Quantity, Department, and PaymentStatus fields. <fieldtype> values must conform to SQL Server data types. <fieldtypemods> values are not really well defined in the SS notification Services Books Online; seems that one can use some keywords from the CREATE TABLE SQL statement. Remember that the XML just provides information to the NSControl utility to set up the event tables. You can always change the field descriptions after the tables are set up according to your needs.

You can also set up indexes using the <indexsqlschema> element.

Next two sections define subscription and notification tables in a similar way.

Before going any further, we need to understand how these tables work. An event, as you already realize, is a record in the event tables. How events are created entirely depends on your system. You have to develop a mechanism that will store the events in these tables based on the business logic of the system. We will discuss it later in more detail.

Once an event is created, the SS Notification Services match the event to subscriptions. Subscriptions, defined in the subscription table, describe which events actually somebody (a subscriber) is interested in.

The subscription table name will be derived from the OrdersS specified as a subscription class name. The actual table names will be NSCurrentOrderSSubscriptions and NSOrderSSubscriptions. The tables will have some predefined fields in addition to the ones defined in the application definition XML file. We will set up actual subscriptions a little later, now we just develop a foundation for holding them.

I want subscribers to receive messages based on the values of two fields in the event tables. The first field is called Department. I want subscribers to receive events related to their department only. The second field is called PaymentStatus. Payment statuses in the Order Entry system consist of “Success”, “In Progress”, “Failure” and different error statuses. The actual values in the event table are coded with one character (such as “S” for “Success”, “P” for “In Progress”, “F” for “Failure”, and “E” for errors) and (possibly) two digits. For example, error codes might be “E10”, “E20”, etc. For example, if a subscriber needs to receive all error messages, the WHERE LEFT(e.PaymentStatus, 1) = s.PaymentStatus clause will get correct records if the PaymentStatus field in the subscription table is set to “E”. If a subscriber needs to receive only messages with the status “S”, the PaymentStatus field in the subscription table needs to be set to “S”.

The rule, defined in the <action> element of the XML file, creates a notification every time when there is a record in the events table matching a record in the subscription table.

The SQL statement in the rule uses the OrdersNNotify user-defined function, which name is based on the name of the notification class, OrdersN. Actual notification table names, NSOrdersNNotifications and NSOrdersNNotificationBatches1, also derive from the notification class name.

The OrdersNNotify user-defined function assumes that the notification table will have a few predefined fields. Values for some of the fields (SubscriberID, DeviceName, and SubscriberLocale) will come from the subscription table. Notice that the SubscriberID field is not even defined in the XML. It will be created automatically.

I strongly suggest examining your tables after they are created just to get familiar with the structure of those.

Well, to make a long story short… The Order Entry system creates an event, which is a set of entries in the event tables. The SSNS matches the event to subscriptions and create corresponding records in the notification table for each match. You may notice that events and notifications are actually processed in batches, but this does not significantly change the described process.

A great description of the process you can find on the Microsoft site:
http://www.microsoft.com/technet/prodtechnol/sql/2000/evaluate/sqlnsto.mspx
The picture below is borrowed from that article.

An XSLT file formats the output for my notification e-mails. In fact, the one that I used for this example is quite trivial: it does nothing. Surely, if you have time and eager, you may develop something better. I didn't.

Now we are ready to do the actual magic. To extract it from the examples that have come with SSNS, I spent quite a bit of time. That is what I came up with:

NSControl create -in NS_CONFIG_FILE_NAME.xml

Yes! That is what they call "building a NS application", that’s what is hidden under hundreds of lines of command scripts. That is everything you need. (The NS instance config file specifies application definition files and they may specify XSLT files.) No surprise that we use the NSControl utility again.

The actual command that you need for our Order Entry example is the following:

NSControl create -in OrderEntryNSConfig.xml

Note: to delete the instance you may use the following command:

NSControl delete –force -name INSTANCE_NAME –s SERVER_NAME

If you look into your SQL Server tables, you will notice that after running this command you have a few more databases: one for the instance and one for each application. In my simple example I had only one application, so the NSControl utility created two databases for me. The instance database was called OrderEntryNSMain; the application database was called OrderEntryNSApplication. All tables we discussed above belong to the latter database.

Next step. Enable your NS solution (make sure that the Windows service is running:

NSControl enable -name INSTANCE_NAME

NSControl enable -name OrderEntry

for this example.

Now you can add subscribers and subscriptions.

There are several approaches to set up your subscriptions/subscribers. You can develop a Web application that will provide a nice user interface and allow changing the subscriptions on the fly or you can write simple VB scripts for this purpose. Regardless of the method you use, you will essentially do the same thing: create and update SQL Server tables. You can even do it directly using SQL Server Enterprise Manager if you like. To help you with setting up subscriptions, Microsoft comes with a COM interface that you can call from your programs.

I attached a few of VB script code snippets to the zip file with all examples. The code uses a few objects, which methods are quite self-explanatory. For example, using the methods you could a) add subscribers specifying unique subscriber IDs (that what the first snippet does); b) specify parameters for subscriber delivery channels such as e-mail addresses; c) specify actual values for the fields in the subscription table for each subscriber; these values will be used by the subscription rule.

Adding subscribers:


Dim nsInstance, nsApplication, nsSubscriber, nsSubscriberDevice

Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "OrderEntry"
        
Set nsSubscriber = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscriber")
nsSubscriber.Initialize nsInstance

nsSubscriber.SubscriberId = "CompDept_Delivery"
nsSubscriber.Add

nsSubscriber.SubscriberId = "BooksDept_Errors"
nsSubscriber.Add

Adding e-mail subscriptions:


Dim nsInstance, nsApplication, nsSubscriber, nsSubscriberDevice

Set nsInstance = WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSInstance")
nsInstance.Initialize "OrderEntry"

Set nsApplication = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.NSApplication")
nsApplication.Initialize nsInstance, "NSApplication"

' 
' Add an E-Mail Device for Each Subscriber
'
Set nsSubscriberDevice = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.SubscriberDevice")
nsSubscriberDevice.Initialize nsInstance
nsSubscriberDevice.DeviceTypeName      = "SMTP"
nsSubscriberDevice.DeviceName          = "myEmailDevice"
nsSubscriberDevice.DeliveryChannelName = "EmailChannel"

nsSubscriberDevice.SubscriberId        = "CompDept_Delivery"
nsSubscriberDevice.DeviceAddress       = "ComputersDelivery.TestMailbox@mycompany.com"
nsSubscriberDevice.Add

nsSubscriberDevice.SubscriberId        = "BooksDept_Errors"
nsSubscriberDevice.DeviceAddress       = "BooksAdmin.TestMailbox@mycompany.com"
nsSubscriberDevice.Add

' 
' Add E-mail Subscriptions
'
Set nsSubscription = 
    WScript.CreateObject("Microsoft.SqlServer.NotificationServices.Subscription")
nsSubscription.Initialize nsApplication, "OrdersS"
nsSubscription.SetFieldValue "DeviceName", "myEmailDevice"
nsSubscription.SetFieldValue "SubscriberLocale", "en-us"

nsSubscription.SubscriberId = "CompDept_Delivery"
nsSubscription.SetFieldValue "PaymentStatus", "S"
nsSubscription.SetFieldValue "Department", "CMP"
nsSubscription.Add

nsSubscription.SubscriberId = "BooksDept_Errors"
nsSubscription.SetFieldValue "PaymentStatus", "E"
nsSubscription.SetFieldValue "Department", "BKS"
nsSubscription.Add

After you run the scripts, you'll find your subscription related tables set up.
As a result of these subscriptions all event records having “S00” in the PaymentStatus field and “CMP” in the Department field will create notifications that will be e-mailed to the ComputersDelivery.TestMailbox@mycompany.com address. All event records having “Exx” in the PaymentStatus field (where xx – any two digits) and “BKS” in the Department field will result into notifications that will be e-mailed to the BooksAdmin.TestMailbox@mycompany.com address.

And that's it. Your SSNS “application” is ready. To test it you have to drop a few records into the event table. I have also provided a sample SQL stored procedure to do that. Enjoy.

You can download the code examples from http://www.andreiko.com/DesignPatterns/SSNSExample.zip

May 2006

Three-Dimensional Thinking and Job Problem

2006-05-23T18:43:00.000-07:00

.www.andreiko.com
.andreiko.livejournal.com

Earlier this year Mike Hilton, President of Sunaptic Solutions, presented to us different challenges in problem solving process. One of them was related to the limitations we create during the process.
There is a well-know cliché, “think outside the box”. In the last few years that phrase became annoying, and almost nobody has been taking it seriously. The first thought which I personally have hearing that phrase is “think outside the bun”, coming from Taco Bell advertisements.
Instead of “thinking outside the box”, Mike suggested another term, “three-dimensional thinking”. Mike’s presentation inspired me to write this short article.

Chopsticks and triangles

To illustrate the three-dimensional thinking I would like to suggest one well-known old puzzle.
First, one is asked to put together a triangle using three chopsticks, what is a trivial task. Then the person is asked to make two triangles using five chopsticks, what is a rather trivial task too. However, the next task is to make four triangles using six chopsticks without breaking them. Each side of an equilateral triangle should be made of one chopstick. To solve this puzzle one has to move the triangles from the flat surface to the three-dimensional space and make a tetrahedron. The tetrahedron has four triangular sides, and six chopsticks are needed to put it up.
Don’t try to take the “three-dimensional thinking” term literally. It can be four- and five-dimensional, and the dimensions do not necessarily represent space dimensions. Below is an example of another puzzle.

Switches and Light Bulbs

You got 3 light bulbs in a room and 3 switches outside the room on another floor. Each switch is connected to one and only one light bulb. Turning the switches on or off, you cannot see which bulb is going on or off. However, you know exactly which position of the switches means “on” and which one means “off”. You can exit the room and re-enter once. How can you match up the switches to the bulbs?
My first impression of the task when I heard it was “That’s impossible!” I tried to consider all different combinations of switch positions trying to match them with possible combinations of the light bulb states. The number of combinations was not even eight; it was four, since I could not distinguish between the bulbs. All bulbs were the same. What were the possible states? All switches are on, all switches are off, two switches are on and one switch is on, one switch is on and two switches are off. In any of these state combinations there were always at least two switches (or two bulbs) that were undistinguishable.
Well, let’s look at the puzzle from the other angle, I thought. How many positions does a switch have? Only two (off and on). If we match a switch in a selected position with a bulb, we would be able to match two switches with two bulbs only. However we have three bulbs. So far it seems impossible to solve the task.
Think, think… We are trying to consider different states of the bulbs. Well, it is not just the two mentioned above. A bulb can be broken, that’s another state; or can be removed, that’s one more state. How can we break a bulb from outside the room?
I found a solution quite soon. The formal process of thinking of another person could be different though. However, after a number of attempts to think about different states of the switches and bulbs, one finds a state of bulbs that can be achieved using the switches. It is “recently turned off”!
If you turn a bulb on, then wait for a little while, then turn it off and enter the room, the bulb will be off but still warm. That’s the solution! Assuming that all bulbs are off in the beginning, one can turn two switches on, wait a little, and then turn one of them off. One bulb will be off (and cold), one bulb will be on, and the third one will be off but warm.
The task that seemed to have no solution is solved. To do it, we added one more dimension, in fact, just a second one, into a one-dimensional (and discrete) system. In addition to the “on/off” dimension we have added the “cold/warm” dimension.

SSIS Challenge

Let’s consider a multi-functional health care computer system with a significantly large database. The database holds millions of records of patient information that was collected from different sources over a number of years. Apparently some patient names and other demographics could be entered incorrectly; short names such as Bill for William are also possible. However, in general, the information is correct, since the data has been used for years and constantly corrected.
Searching for a patient in this system may not bring all relevant results if the exact match is used and the patient name was misspelled. To provide more relevant results it was suggested to use MS SQL Server 2005 Integration Services (SSIS) for search. SSIS has a feature called “fuzzy lookup” that matches the words based on common misspellings or variants.
The architecture of the search looked fine on paper, and the suggested solution was being implemented. However, during the development it was immediately found that the SSIS package was slow. This might not be a problem for a batch task, and, in fact, the solution worked quite fine for those, but it was not acceptable for using with searches initiated from the user interface. The searches were taking 10 seconds and more, which was definitely not an option. Convincing the future users that the slow behavior is something what they should live with was not even considered.
The development team started looking into fixing this problem. Eventually it was found that SSIS is designed to perform well on big volumes, and was not really designed for speed. What could the team suggest?
Well, improving user experience could have been done in a few different ways. Firstly, there were other areas of the code that could have been improved such as the business layer or the UI. However, this would not help in a major way, the best result that could have been theoretically achieved was still not close to the response time that would satisfy users. The other solution was to improve the hardware performance that could possibly result in additional cost for the client. It could be definitely an option to re-write the code without using SSIS, but that would take a lot of development time, and the timeframe was tough.
The solution was found (as you may expect) when the team looked at the problem from a new dimension. The problem was not really the fact that the search speed was slow, rather that the user experienced inconvenience. To improve user experience it was suggested to give users a choice of selecting a search based on exact matching or based on fuzzy lookups. In most cases the user would choose the exact matching search what would return an instant response. In case of the “fuzzy lookup”, the choice would be made consciously and therefore the user frustration would be minimal.

The Job Problem

A few years ago I was having a discussion with a pastor from Minneapolis. The discussion was taking place on one of the Internet forums that provided us with a luxury of unhurried pace of thinking. The topic of discussion was the controversy in the Book of Job, which had puzzled me for many years. Everyone knows the narrative of the book. Job was an Arabian patriarch who lived in the land of Uz in great prosperity. Job was a sincere and an upright man who feared God. Satan challenged Job’s integrity, and to prove him wrong God gave Job into Satan’s hand. Job lost his children, wealth, and was afflicted with a malignant ulcer.
Job had hard time keeping his integrity. The problem that he faced was a contradiction of his system of the world axioms with the reality. He believed that he did not do anything wrong, he did not even say anything against God, and he could not understand why God punished him. Part of his belief was that his suffering must have been a result of a punishment.
The vast part of the Book of Job is the discussion that Job was having with his friends. And the discussion seemed to reach the dead end since there was no reasonable solution of the problem. If the punishment really was a result of Job’s sins then Job is insincere liar. If Job was not such a sinner, then the punishment is unfair, what Job would never expect from God. Job and his friends considered all possible aspects of the problem, looked at it from all possible angles, and none of them satisfied Job.
The pastor I had a chat with on that Internet forum called this situation the Job problem. To find a solution of the problem Job had to start thinking beyond that system of axioms he used. He had to look at the problem not even from a different angle but from another dimension. When he realized that he could not judge God and provide human reasons for God actions, he found the solution of the problem.
The Job problems in our live can be solved using three-dimensional thinking, which, in fact, does not always break the system of axioms and assumptions we make, but often extends the axioms and assumptions into another dimension.
In 1970-s the pastor lived in the USSR and was solving his personal Job problem. As you may know, the Communist party in the former USSR was an essential part of the government, state, and power. Becoming a communist was mandatory to the young man who wanted to make his career in a government organization. To the young man that meant having enough money to support his family and even prosper. On the other hand, being a communist meant being involved in activities that the young man considered insincere. For instance, he could not vote in favor of idiotic decisions that communist bosses quite often made. Being in the opposition could cost a career, job, and sometimes freedom.
A joke of those years was that one could not be sincere, clever, and a communist at the same time. A sincere clever man would never become a communist, a clever communist is insincere, and a sincere communist is a fool.
The young man was not a fool, and he wanted to make a career to support his family, but he did not want to become a communist. What to do? He faced his personal Job problem, and the solution was not simple. It may sound simple today, but to make it he needed to move to another dimension of his model of the world. He decided to leave the USSR.
He immigrated to the US and became a pastor.

Conclusion

Decision making process is a part of our life. We make decisions every day, and very often we experience a Job problem. To solve the problem we have to realize that our assumptions often create limits beyond which we cannot move. We need to reconsider the assumptions; we have to move to the third (and to the fourth if needed) dimension to solve the problem.
1. Chopsticks and triangles.The assumption was that we had to construct triangles on a flat surface. Once we decided that we didn’t have to, we found a solution.
2. Switches and Light BulbsThe assumption was that the bulbs could be in only one of the two states: turned off or turned on. Once we decided that we could determine more that two states (such as recently turned off, for example), the puzzle was solved.
3. SSIS ChallengeThe assumption was that users would always need to perform a “fuzzy lookup” when searching for patient records. Once we decided that the users might have a choice of how they wanted to search the database, we solved the problem.
4. The Job ProblemJob’s assumption was that God’s rules could be judged and understood by a man. He had to realize that he could not approach God’s actions with human measurements.
5. The Pastor’s dilemmaPastor was trying to avoid becoming a communist, but he could not manage it in the USSR. He had to leave the USSR to solve the dilemma. Interesting enough that trying to solve the problem in two dimensions quite often we hit a limitation. Like a two-dimensional creature that cannot move from inside a circle on a flat surface, we are not able to find a solution before we move into another dimension.

test

2006-02-17T10:50:00.000-08:00

test