mail subject -- sql server notification services

Mostafa Salama
3/3/2005 7:51:03 AM

would you please tell me how to pass a field to a protocol, and how this
field OrderNum take value.

[quoted text, click to view]

Joe Webb
3/3/2005 9:24:05 AM

Sure. You can add a field to the subject of the email by referencing any
field that's being passed to the protocol. Care should be taken,
however, if you're using the digest delivery option.

See the following example:

<Protocols>
<Protocol>
<ProtocolName>SMTP</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>'Order# ' + OrderNum + '
has been processed'</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>'html'</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>

<SqlExpression>'orders@mycompany.com'</SqlExpression>
</Field>
<Field>
<FieldName>Priority</FieldName>
<SqlExpression>'Normal'</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>

<SqlExpression>DeviceAddress</SqlExpression>
</Field>
</Fields>
</Protocol>
</Protocols>

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]

Joe Webb
3/4/2005 7:01:03 AM

Fields in the notification table are available to the protocol by
default. You can make other fields available via user-defined functions.

Here's a snippet from BOL.

<FieldReference> or <SqlExpression> Element
If the header field value already exists in one of the notification
fields or computed fields, use the <FieldReference> element. The
<FieldReference> element specifies the name of the notification field
whose value should be used in this header field. Both regular and
computed notification fields can be used to provide this value.

For more information, see <FieldReference> Element.

If the header field value does not yet exist in a notification field,
use the <SqlExpression> element. The <SqlExpression> element contains
the Transact-SQL expression that computes the data for this field. Any
Transact-SQL expression that can be evaluated as part of a SELECT query
in the application database can be referenced in a <SqlExpression>
element. This includes expressions such as arbitrary constants and
making SQL function calls. If you use a string constant as a
<SqlExpression> value, it must be enclosed in apostrophes, which in turn
must be expressed as the entity reference ' in the application
definition file (ADF).

An <SqlExpression> element can also take an application parameter as a
value. Application parameters are defined in the <ParameterDefaults>
node of the ADF. For more information about using application
parameters, see Defining Application Parameters and <SqlExpression>
Element (/Protocol/Fields/Field).

Note There are several reserved characters in the Notification Services
XML vocabulary (>, <, ', ", &, %). If you need to use these characters
in your Transact-SQL expressions, you must use entity references to
replace them. For more information about using reserved characters in
the ADF, see Reserved Characters.

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]

Phaitour
5/12/2006 3:17:02 AM

Hi Joe, Shyam, and kate,

I've been a long time reader of your works and it's helped me through quite
a few tough spots, but I still have a couple of questions for you guys :P

1) What happens in digest delivery mode when you do something like:
<FieldName>Subject</FieldName>
<SqlExpression>'Order# ' + OrderNum + '
has been processed'</SqlExpression>

I'm interested because I want to put in a dynamic subject based on whether
or not the notification is going to be a digest. There will be times when
the email is for a single notification and thus the subject should be
singular, and times when the notification is actually a digest of multiple
event matches and thus the subject should be plural... Is there an easy way
to do this?

2) Is there a way to specify a "display name" for the sender email address
within the built in SMTP delivery protocol? What I mean is: it's straight
forward to send out an email from any email address, but that will show up at
the recepients inbox as coming from that email address; is there a way to
specify the "alias" for that from email address so in the inbox the user sees
a phrase or other text instead of the email address?

Thanks in advance to all who can help!!

T

[quoted text, click to view]

ramadu
5/12/2006 11:39:42 AM

Hi Phaitour,

Is OrderNum a field name in your NotificationClass? In that case, when
you call the dbo.[NotificationClassName]Notify() function you can add
all the OrderNum from your EventClass into one single string using SQL
and then pass into the function. When you have multiple notification
nodes for delivery, I don't believe SSNS will add the OrderNum fields
into a single string.

To answer your second question, you can set the SQLExpression for the
From FieldName as follows:

<SqlExpression>'Phaitour
<Phaitour@discussions.microsoft.com>'</SqlExpression>

If you are going to use the Windows 2000/XP SMTP service to deliver your
emails, you have to specify an email address in a valid format.

Hope that helps!

- ramadu

http://sriramvenkataramani.tripod.com/

:
[quoted text, click to view]

Phaitour
5/14/2006 1:10:01 PM

Thank you Ramadu for your reply! I'm stil a bit unsure as to how to make the
subject string dynamic. Let me elaborate:

In the system I have two notifications (both for user@host.com), but I
turned on digesting so the system will mail out both notificiations within
the same email. Now, the subject for a normal email might say, "you have a
message!"; but in this case, because there are two nofications contained
within the same email, I want the email to say "you have 2 messages!" or
perhaps "you have multiple messages".

Is there a way to make the subject line of the email dynamic that way?

[quoted text, click to view]

ramadu
5/14/2006 5:42:24 PM

Hi Phaitour,

My initial reply was based on your requirement to have the list of
OrderNum as part of the subject. If you need the subject to have the
number of messages, you can have one more field in the notification
class called OrderCount. When you populate this field, while calling the
dbo.[NotificationClassName]Notify() function in your SQL statement you
can put in the count() of the number of rows being selected.

Now, in the subject field you can format it as "You have " + OrderCount
+ " message(s)."

I hope this satisfies your requirement.

- ramadu

:
[quoted text, click to view]

Phaitour
5/17/2006 4:58:02 PM

Hi Ramadu,

It's still a bit unclear.

What is this dbo.[NotificationClassName]Notify() function that you're
refering to. When is it called? Who calls it? What can I do to modify it?
Where should I call it from?

Thanks in advance for your help!

T

[quoted text, click to view]

ramadu
5/18/2006 3:07:42 PM

Hi Phaitour,

The dbo.[NotificationClassName]Notify() function is what you will call
in order for your notification to be processed. You will find the value
in your adf xml.

- ramadu

:
[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 : mail subject