Sending Attachments Using Notification Services -- sql server notification services

Suyog
9/22/2004 4:19:02 AM

Do u want me to copy paste the code of my ADF file?
I did not get what do u mean by "ADF file that contains custom delivery
protocol class", I have just mentioned the protocol name in the
NotificationClass/Protocols/Protocol/ProtocolName.

Please Clarify.
Also tell me whether the approach mentioned by me in the first post right.

[quoted text, click to view]

Joe Webb
9/22/2004 7:02:13 AM

Hi Suyog -

Generally speaking your custom formatter should be documented like:

<ContentFormatter>
<ClassName>MyNamespace.MyClassName</ClassName>
<AssemblyName>C:\MyFolder\MyCustomFormatter.dll<AssemblyName>
<Arguments>
<Argument>
<Name>MyParameter1</Name>
<Value>10</Value>
</Argument>
<Argument>
<Name>MyParameter2</Name>
<Value>false</Value>
</Argument>
</Arguments>
</ContentFormatter>

If you copy and paste (or attach) the relevant section of the ADF, we
can give you better feedback to your question.

HTH...
Joe Webb

~~~
Get up to speed quickly with SQLNS
http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=6

[quoted text, click to view]

Joe Webb
9/22/2004 7:13:28 AM

Oops! I mis-read your posting and thought you were asking about a custom
content formatter. Please disregard my prior post.

Have documented the Protocol in the Instance Configuration File? The
ProtocolName provided in the ADF must be SMTP, File, or one that you
have documented in the Protocols node of the Instance Configuration
File. Here's a sample.

<Protocols>
<Protocol>
<ProtocolName>EnhancedSMTP</ProtocolName>
<ClassName>wtsProtocols.EnhancedSMTP</ClassName>
<AssemblyName>wtsProtocols.dll</AssemblyName>
</Protocol>
</Protocols>

ProtocolName is the name for the custom protocol. This will be the name
used in the Application Definition File to reference this custom
protocol. ClassName names the class. AssemblyName indicates the name of
the assembly that contains the class.

HTH...
Joe Webb

~~~
Get up to speed quickly with SQLNS
http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=6

[quoted text, click to view]

avnrao
9/22/2004 2:14:03 PM

can u post your ADF file that contains custom delivery protocol class??

[quoted text, click to view]

avnrao
9/22/2004 4:59:01 PM

Yes.. pls post your ADF file..also the CustomDelivery protocol class code..

you said that the attachments are generated by NS..are you using FileSystem
delivery protocol? how did you specify CustomDelivery protocol then?
Your approach of sending mails using CustomDelivery protocol is correct..but
what I am concerned is how are you generating the attachments?

[quoted text, click to view]

Suyog
9/22/2004 9:19:03 PM

here is my ADF code...

<EventClass>
<EventClassName>URFReportEvents</EventClassName>
<Schema>
<Field>
<FieldName>URFReportEventID</FieldName>
<FieldType>int</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>


<Chronicles>
<Chronicle>
<ChronicleName>URFReportEventsChron</ChronicleName>
<SqlSchema>
<SqlStatement>
IF EXISTS(SELECT name FROM dbo.sysobjects WHERE name =
'URFReportEventsChron')
DROP TABLE URFReportEventsChron
CREATE TABLE URFReportEventsChron
(
DistributionCompanyID int,
NotifyDate datetime
)
</SqlStatement>
</SqlSchema>
</Chronicle>
</Chronicles>
</EventClass>

------------- Subscription Class ---------------------

<SubscriptionClass>
<SubscriptionClassName>URFReportSubscriptions</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>DistributionCompanyName</FieldName>
<FieldType>nvarchar(100)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>

<EventRules>
<EventRule>
<RuleName>URFReportEventRule</RuleName>
<Action>
--event rule
SELECT dbo.URFReportNotificationsNotify(s.SubscriberId,
s.DeviceName,
s.SubscriberLocale,
l.DistributionCompanyName
)
FROM NSURFReportSubscriptionsSubscriptions s,
RapsNotification..URFReportEvents l
WHERE l.DistributionCompanyName = s.DistributionCompanyName
and l.URFReportEventID = ( select max(URFReportEventID) from
RapsNotification..URFReportEvents )
and l.DistributionCompanyID not in ( select DistributionCompanyID from
URFReportEventsChron )

--insert into chronicles table
INSERT INTO URFReportEventsChron(DistributionCompanyID,NotifyDate)
SELECT l.DistributionCompanyID as DistributionCompanyID,NotifyDate = getdate()
FROM RapsNotification..URFReportEvents l
where l.DistributionCompanyID not in (Select DistributionCompanyID from
URFReportEventsChron)

--delete from events table
delete from NSURFReportEventsEvents
</Action>
<EventClassName>URFReportEvents</EventClassName>
</EventRule>
</EventRules>

<NotificationClass>
<NotificationClassName>URFReportNotifications</NotificationClassName>
<Schema>
<Fields>
<Field>
<FieldName>DistributionCompanyName</FieldName>
<FieldType>nvarchar(100)</FieldType>
</Field>
</Fields>
</Schema>


<ContentFormatter>
<ClassName>XsltFormatter</ClassName>
<Arguments>
<Argument>
<Name>XsltBaseDirectoryPath</Name>
<Value>%_BaseDirectoryPath_%\AppDefinition</Value>
</Argument>
<Argument>
<Name>XsltFileName</Name>
<Value>URFReport.xslt</Value>
</Argument>
</Arguments>
</ContentFormatter>
<MulticastDelivery>false</MulticastDelivery>


<Protocols>
<Protocol>
<ProtocolName>DeliveryProtocol</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>'User Rejected File Report Notification'+CONVERT
(NVARCHAR(30), GETDATE())</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>'html'</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>
<SqlExpression>'suyog_iswalkar@test.com'</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>
<Field>
<FieldName>Attachments</FieldName>
<SqlExpression>'C:\Program Files\Adobe\Acrobat
5.0\Help\ENU\MiniReader.pdf'</SqlExpression>
</Field>
</Fields>


<ProtocolExecutionSettings>
<RetrySchedule>
<RetryDelay>PT0H1M</RetryDelay>
<RetryDelay>PT0H1M</RetryDelay>
<RetryDelay>PT0H1M</RetryDelay>
<RetryDelay>PT0H1M</RetryDelay>
</RetrySchedule>
</ProtocolExecutionSettings>
</Protocol>
</Protocols>


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

--------------- code of custom delivery protocol class file
--------------------

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using Microsoft.SqlServer.NotificationServices;
using CDO;
using Microsoft.ApplicationBlocks.ExceptionManagement;

namespace RapsNS
{
public class myDeliveryProtocol : IDeliveryProtocol
{
private bool multicast = false;
private NotificationStatusCallback notificationStatusCallback = null;

public myDeliveryProtocol()
{
//Add constructor logic here if required.
}

//Implement the IDeliveryProtocol.Initialize method.
public void Initialize(
StringDictionary channelArgs,
bool multicast,
NotificationStatusCallback notificationStatusCallback )
{
this.multicast = multicast;
this.notificationStatusCallback = notificationStatusCallback;
}

//Implement the IDeliveryProtocol.DeliverNotification method.
public void DeliverNotification(
NotificationHeaders[] notificationHeaders,
string body )
{
bool successfulDelivery = false;
Exception failureException = null;

try
{
//Get a connection to SQL Server.
// using (SqlConnection myConnection = new
SqlConnection(connectionString))

CDO.Message mail = new CDO.MessageClass();
CDO.Configuration iconfig;
ADODB.Fields oFields;
ADODB.Field oField;

iconfig = mail.Configuration;
oFields = iconfig.Fields;
oField =
oFields["http://schemas.microsoft.com/cdo/configuration/sendusing"];
oField.Value = 2;
oField =
oFields["http://schemas.microsoft.com/cdo/configuration/smtpserver"];
//oField.Value = "Punhjw20271";//"smarthost" ;
oField.Value = "192.168.170.20";
oFields.Update();
mail.Configuration = iconfig;

mail.To = "suyog_iswalkar@test.com";
mail.From = "suyog_iswalkar@test.com";
mail.Subject = "Test";
mail.TextBody = "testing testing";

//Execute the command.
mail.Send();

successfulDelivery = true;

}
catch (Exception ex)
{
failureException = ex;
//Handle any exceptions here; for instance,
//write exception information to the event log.
ExceptionManager.Publish(ex);

}
finally
{
SendStatus(notificationHeaders, successfulDelivery, body,
failureException);
}
}

//Builds up a statusInfo message, creates a
//NotificationStatus object, and calls
//the notificationStatusCallback delegate.
private void SendStatus(

Joe Webb
9/23/2004 6:43:50 AM

Have you defined the custom protocol in the Instance Config File? Below
are some excerpts from the Stocks example... Also see my prior post in
this thread concerning the Instance Config File.

ADF...
<Protocol>
<ProtocolName>HttpLogger</ProtocolName>
<Fields>
<Field>
<FieldName>escaping</FieldName>
<SqlExpression>'yes'</SqlExpression>
</Field>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>'Stock notification: '+CONVERT (NVARCHAR(30),
GETDATE())</SqlExpression>
</Field>
<Field>
<FieldName>SubscriberId</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field> </Fields>
</Protocol>

And the Instance Config File...
<Protocols>
<Protocol>
<ProtocolName>HttpLogger</ProtocolName>
<ClassName>HttpExtension</ClassName>
</Protocol>
</Protocols>

<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>HttpLoggerChannel</DeliveryChannelName>
<ProtocolName>HttpLogger</ProtocolName>
<Arguments>
<Argument>
<Name>ProtocolProviderClassName</Name>
<Value>NSSamples.HttpLoggerDeliveryProtocol</Value>
</Argument>
<Argument>

<Name>ProtocolProviderAssemblyName</Name>

<Value>%BaseDirectoryPath%\CustomDeliveryProtocol\bin\HttpLoggerDeliveryProtocol.dll</Value>
</Argument>
<Argument>
<Name>PostURL</Name>
<Value>http://localhost/NSSamples/Stock/HttpLogger.aspx</Value>
</Argument>
<Argument>
<Name>UserName</Name>
<Value>UserThatHasPermissionsToPost</Value>
</Argument>
<Argument>
<Name>Password</Name>
<Value>Password</Value>
</Argument>
<Argument>
<Name>Encoding</Name>
<Value>ISO-8859-1</Value>
</Argument>
<Argument>
<Name>ContentType</Name>
<Value>text/html</Value>
</Argument>
</Arguments>
</DeliveryChannel>

HTH...
Joe Webb

~~~
Get up to speed quickly with SQLNS
http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=6

[quoted text, click to view]

Shyam Pather [MSFT]
9/23/2004 11:39:11 AM

Suyog,
In your config file, declare your protocol in the <Protocols> element. You
need to specify a name for the protocol (any name you would like to use to
refer to it), a class name, and the assembly name (including full path).
Make sure you specify the class name in fully-qualified form i.e. with any
namespace prefix. For example, if your class is defined like this:

namespace MyNamespace
{
class MyProtocol : IDeliveryProtocol
{

}
}

then the class name you need to specify in the <Protocol> element of your
config file is MyNamespace.MyProtocol. If you leave off the namespace, NS
won't be able to find the protocol class in the assembly.

Then you need to declare one or more delivery channels that refer to the
name you gave your protocol. Then, in your ADF, you need to declare protocol
fields for your protocol in each notification class that will use it.

Hope this helps.
-shyam
--
Learn more about SQL-NS:
http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/
---------------------------------------------
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.
---------------------------------------------
[quoted text, click to view]

Suyog
9/23/2004 8:45:04 PM

hi shyam
this was what missing, it has started working now.
thanks

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server notification services

sql server notification services : Sending Attachments Using Notification Services