Groups | Blog | Home
all groups > sql server notification services > january 2005 >

sql server notification services : Notification Email Setup


John Mc
1/13/2005 9:26:27 AM
Hi Everyone,

I was wondering if you could help me with an idiot proof
guide on how to configure NS to use SMTP.

I have performed the following steps:

1. Created a subscriber with a device of SMTP called
EmailChannel.

2. Included a SMTP Delivery Channel in the appConfig.xml file:
<DeliveryChannel>
<DeliveryChannelName>EmailChannel</DeliveryChannelName>
<ProtocolName>SMTP</ProtocolName>
<Arguments>
<Argument>
<Name>SmtpServer</Name>
<Value>HIZZSW01</Value>
</Argument>
<Argument>
<Name>BodyEncoding</Name>
<Value>utf-16</Value>
</Argument>
</Arguments>
</DeliveryChannel>

3. Included a SMTP protocol in the Notification class in
the addADF.xml:
<Protocol>
<ProtocolName>SMTP</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>&apos;The &apos; + ProjectName +
&apos; has been &apos; + Action</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>

<SqlExpression>&apos;notifications@home.com&apos;</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>
<Field>
<FieldName>Priority</FieldName>
<SqlExpression>&apos;Normal&apos;</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>&apos;html&apos;</SqlExpression>
</Field>
</Fields>
</Protocol>

So everytime the notification is generated, it's finishing
with an "undone" status. What am I doing wrong? How do I
get into the debug of the NS? What have I missed out?

Thanks,
Colin Meek [MSFT]
1/13/2005 9:47:23 AM
When the status code is 'undone', it is often because of one of the
following issues:

- The distributor is not enabled ('nscontrol status -n <instance name>' for
component status)
- If the <DeviceName, SubscriberId> fields in the notification are unknown,
it can cause a silent failure in notification delivery, because no
distributor element will pick up the notification. Compare the
NSSubscriberDeviceView in the instance database with the notification
produced in the application database to verify this hypothesis.

Finally, check for any error messages in the event log which could indicate
problems running the distributor.

If you are still unable to find the source of the problem, we can try some
other tacks!

- Colin Meek [MSFT]

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only.
--

[quoted text, click to view]

John Mc
1/14/2005 2:05:13 AM
Hi Colin,

Thanks for that, unfortunatley it all checks out ok. The
subscriber and device names are bother present and correct.
I'm convinced I've missed something out along the way.

I've looked in the Managment -> SQL Server Logs -> Current,
but theres nothing in there.

I have just found that in the NSProtocol table, that SMTP
has a null value for both the ClassName and AssemblyName
columns, is this correct?

This email notification is a extension of my original
notification system that delivers messages fine. So I can
prove the distributors are up and running by triggering an
event. The first notification is generated quite happily,
the second gets the "undone" status :(

A Device entry looks like this for email:

SubcriberId : Bob
Enabled : 1
Created : 13/01/2005 17:03:29
Updated : 13/01/2005 17:03:29
DeviceName : SMTP
DeviceTypeName : SMTP
DeviceAddress : Bob@here.com
DeliveryChannelName : EmailChannel

So whats next to try? :)
Thanks,
J


[quoted text, click to view]
John Mc
1/14/2005 3:57:45 AM
BTW, I forgot to say that we're using a Virtual SMTP
service to forward on the emails to our exchange server.

And that a subscriber will have two devices, one will go
out via email, the other uses the existing route that works.

Thanks,
John

[quoted text, click to view]
John Mc
1/14/2005 7:15:26 AM
After more poking around, I think the Deliver Status shown
by my "NSBobNotificationsSMTPNotifications" view is
actually "Delivery never attempted: first try pending", not
"undone".

Do I have to enable SMTP somehow? As I thought I only had
to give it a servername...

[quoted text, click to view]
Colin Meek [MSFT]
1/14/2005 9:03:21 AM
John --

Log entries for NS appear in the event log (accessed using eventvwr), not in
the SQL Server log.

Could you take a look at the NSDistributorWorkItems table also? If should
contain an entry for every notification batch created.

I am unsure about the impact of running a virtual SMTP server. Joe's advise
is good: see if you can succeed in e-mailing notifications from the local
server.

-Colin

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only.
--

[quoted text, click to view]
Joe Webb
1/14/2005 10:55:27 AM
Hi John -

The ClassName and AssemblyName fields in the NSProtocols table of the
<InstanceName>NSMain database are used for custom protocols and the fact
that they are NULL for the File and SMTP protocols shouldn't be causing
you a problem.

Do you have the SMTP Service running on the box? If so, you can try
omitting the SmtpServer argument in the instance config file for the
EmailChannel. It should default to localhost and send the notification
that way. (You can also simply replace the existing value for the
SmtpServer with localhost) If it works, then you know it's a problem
with connecting to the email server. If not, then you'll know it's
somewhere in NS.

You can also try specifying an IP address in the SmtpServer argument
rather than a hostname - that'll help determine if it's a name
resolution issue.


HTH...
Joe Webb
SQL Server MVP

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811




[quoted text, click to view]
John Mc
1/15/2005 4:54:47 AM
Hi Joe,

Ok, I'll ignore the NSProtocols table :)

Unfortunatley, the SMTP Server is on a different box, so I
can't use localhost. I may try getting it installed on the
box to see if it makes any difference.

It shouldn't do though, as I wrote a small program that
will send emails to a specified SMTP server. I tried both
the name and IP address and both worked fine...

I'll let you know what the outcome is on Monday :)

Thanks,
John

[quoted text, click to view]
John Mc
1/15/2005 4:57:36 AM
Hi Colin,

I had a look at the eventviewer, it was full of
Notification messages :) But known of them were errors...

I'll do that on Monday, and let you know what the table
contains.

No were not sure if it the virtual SMTP server either... If
worse happens, I'll just write a custom delivery protocol
that'll write them out to a table and a C# service will
turn them into emails :) I'd prefer to get NS doing it
though :)

Thanks,
John

Thanks,
John
[quoted text, click to view]
John Mc
1/17/2005 2:39:58 AM
OK, I had a look at that table after I put an event into
the NS. This is what I found :-

1. Both the standard delivery notification and email
delivery notification were in the same notification batch
(there id was the same)

2. The NSDistributorWorkItems table contained one entry
that listed the standard delivery channel. No mention was
made of the email delivery channel.

Should there have been two entriesd in this table? One for
standard delivery channel, the other for email channel?

A single entry was created
[quoted text, click to view]
Colin Meek [MSFT]
1/17/2005 8:05:08 AM
John --

No work item has been created for the e-mail channel. Do the notifications
contain the appropriate device name and subscriber id? I know you have
looked into this before, but please double-check (case-sensitivity, etc.)
Could you attach a copy of your instance configuration file and application
definition file, so that I can take a look?

Thanks,

- Colin Meek [MSFT]

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only.
--

[quoted text, click to view]
John Mc
1/17/2005 8:30:19 AM
Colin,

Have you an email I can send directly too? I'm not too
keen on putting company IP on the internet.

Thanks,
John

[quoted text, click to view]
John Mc
1/18/2005 2:02:52 AM
Colin,

I've removed the sensitive bits from the config. Here is
the appADF.xml file:

<?xml version=3D"1.0" encoding=3D"utf-8" ?>
<!-- Provides the root element of an application definition
file (ADF). Contains all elements that describe a single
Notification Services application -->
<Application xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema"
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance"
xmlns=3D"http://www.microsoft.com/MicrosoftNotificationServices/Applicati=
onDefinitionFileSchema">
<!-- Provides a parent location for defining the event
classes used by this application -->
<EventClasses>
<!-- Provides a location for defining one event class -->
<EventClass>
...
</EventClass>
</EventClasses>
<!-- Provides a parent location for defining the
subscription classes used by this application -->
<SubscriptionClasses>
<!-- Provides a location for defining one subscription
class -->
<!-- Subscription class for a Project -->
<SubscriptionClass>
=09
<SubscriptionClassName>ProjectSubscriptions</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>ItemId</FieldName>
<FieldType>uniqueidentifier</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>Action</FieldName>
<FieldType>nvarchar(50)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>
<!-- Provides a location for all of the subscription
event rule definitions for one subscription class -->
<EventRules>
<EventRule>
...
</EventRule>
</EventRules>
</SubscriptionClass>
</SubscriptionClasses>
<!-- Provides a parent location for defining the
notification classes used by this application -->
<NotificationClasses>
<!-- Provides a location for defining one notification
class -->
<!-- Notification class for a Project -->
<NotificationClass>
=09
<NotificationClassName>ProjectNotifications</NotificationClassName>
<Schema>
<Fields>
...
</Fields>
</Schema>
<ContentFormatter>
<!-- Specifies the class name of the class that
provides the content formatter functionality -->
<ClassName>XsltFormatter</ClassName>
<Arguments>
<Argument>
<Name>XsltBaseDirectoryPath</Name>
<Value>%_BaseDirectoryPath_%\AppDefinition</Value>
</Argument>
<Argument>
<Name>XsltFileName</Name>
<Value>Application.xslt</Value>
</Argument>
</Arguments>
</ContentFormatter>
<DigestDelivery>false</DigestDelivery>
<Protocols>
<Protocol>
<ProtocolName>StoredProcedure</ProtocolName>
<Fields>
<Field>
<FieldName>UserId</FieldName>
<SqlExpression>SubscriberId</SqlExpression>
</Field>
<Field>
<FieldName>Header1</FieldName>
<FieldReference>ProjectName</FieldReference>
</Field>
<Field>
<FieldName>Header2</FieldName>
<FieldReference>Action</FieldReference>
</Field>
</Fields>
</Protocol>
<Protocol>
<ProtocolName>SMTP</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>'The ' + ProjectName + ' has been ' +
Action</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>
=09
<SqlExpression>'notifications@somewhere.com'</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>
<Field>
<FieldName>Priority</FieldName>
<SqlExpression>'Normal'</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>'html'</SqlExpression>
</Field>
</Fields>
</Protocol>
</Protocols>
</NotificationClass>
</NotificationClasses>
<!-- Provides a parent location for defining the event
providers used by this application -->
<Providers>
<!-- Provides a location for specifying information about
one hosted event provider used by this application -->
<HostedProvider>
<!-- Specifies the user-defined name of the hosted event
provider -->
<ProviderName>SQL</ProviderName>
<!-- Specifies the class name of the class that provides
the hosted event provider functionality -->
<ClassName>SQLProvider</ClassName>
<!-- Specifies the name of the server on which the
hosted event provider runs -->
<SystemName>%_DBSystem_%</SystemName>
<!-- Specifies the schedule for a hosted event provider -->
<Schedule>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
...
</Arguments>
</HostedProvider>
</Providers>
<!-- Provides a parent location for defining information
about the generator used by this application -->
<Generator>
<SystemName>%_DBSystem_%</SystemName>
</Generator>
<!-- Provides a parent location for defining information
about the distributors used by this application -->
<Distributors>
<Distributor>
<SystemName>%_DBSystem_%</SystemName>
<!-- Defines the length of time for a distributor
quantum, which determines how frequently the distributor
function attempts to process work -->
<QuantumDuration>PT15S</QuantumDuration>
</Distributor>
</Distributors>
<!-- Provides a parent location for defining the execution
settings for this application -->
<ApplicationExecutionSettings>
<!-- Defines the length of time for a generator quantum,
which determines how frequently the generator function
attempts to process work -->
<QuantumDuration>PT15S</QuantumDuration>
<!-- Provides a location for specifying vacuuming
information for the application -->
<Vacuum>
<RetentionAge>P1DT00H00M00S</RetentionAge>
<VacuumSchedule>
<Schedule>
<StartTime>01:00:00</StartTime>
<Duration>P0DT02H00M00S</Duration>
</Schedule>
</VacuumSchedule>
</Vacuum>
<!-- Specifies the number of events that can be processed
during a single generator quantum -->
<EventThrottle>0</EventThrottle>
<!-- Specifies the number of subscriptions that can be
processed during a single generator quantum -->
<SubscriptionThrottle>0</SubscriptionThrottle>
<!-- Specifies the number of notifications that can be
processed during a single generator quantum -->
<NotificationThrottle>0</NotificationThrottle>
</ApplicationExecutionSettings>
</Application>

And Here is the appConfig.xml:

<?xml version=3D"1.0" encoding=3D"utf-8"?>
<!-- Is the top node of the configuration file -->
<NotificationServicesInstance
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema"
John Mc
1/18/2005 1:38:47 PM
Hi Colin,

Have you managed to find anything worng with the files I
supplied? Should I be looking into implementing my work
around?

Many thanks,
John

[quoted text, click to view]