Confused about encoding for SMTP -- sql server notification services

sql server notification services : Confused about encoding for SMTP

Brent Horine
8/29/2005 4:56:50 PM

I'm running "NS 2.0 SP1 Std en" on Windows 2003 Server. I'm sending alerts
via the SMTP channel. I got the basics working by adding the Mail Server
role to the server using Manage Your Server.
Now I'm getting mail but the subject line is in Kanji when opened in
Outlook, but the text is in english. When I use webmail, the whole body is
in Kanji. Help! What encoding should I be using?

My appConfig file look like

<DeliveryChannel>
<DeliveryChannelName>EmailChannel</DeliveryChannelName>
<ProtocolName>SMTP</ProtocolName>
<Arguments>
<Argument>
<Name>SmtpServer</Name>
<Value>localhost</Value>
</Argument>
<Argument>
<Name>BodyEncoding</Name>
<Value>utf-8</Value>
</Argument>
</Arguments>
</DeliveryChannel>

My appADF file looks like:

<Protocol>
<ProtocolName>SMTP</ProtocolName>
<Fields>
<Field>
<FieldName>Subject</FieldName>
<SqlExpression>Alert: '+CONVERT (NVARCHAR(30),
GETDATE())</SqlExpression>
</Field>
<Field>
<FieldName>BodyFormat</FieldName>
<SqlExpression>'html'</SqlExpression>
</Field>
<Field>
<FieldName>From</FieldName>
<SqlExpression>'alerts@ventureGPS.com'</SqlExpression>
</Field>
<Field>
<FieldName>Priority</FieldName>
<SqlExpression>'Normal'</SqlExpression>
</Field>
<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>
</Fields>
</Protocol>

My XSLT file looks like:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="notifications">
<html>
<head><title>Alert</title></head>
<body>
<xsl:apply-templates/>
Alert provided by VentureGPS. 
</body>
</html>
</xsl:template>

<xsl:template match="notification">
<xsl:value-of select="VehicleName" /> was speeding at
<xsl:value-of select="Speed" /> MPH
at <xsl:value-of select="EventDateTime" /> at <xsl:value-of
select="Address" />!
 

</xsl:template>

</xsl:stylesheet>

Andy
8/30/2005 6:24:58 AM

I do not have the exact answer, but just to confirm from your snippet below,
are you using ' or '?

Have you checked to ensure that your timezone is set properly on the SMTP
server and NS server?

This may not be any answers, but thought I would share what popped into my
mind.

[quoted text, click to view]

Brent Horine
8/30/2005 9:12:09 AM

Thanks for the suggestions Andy.

I changed all of the ' to ' but found no change. The Timezone is set
to GMT and regional settings are set to English US.

If I save the email in Outlook as .txt and open it in Notepad with the
encoding set to ANSI, my subject line looks like

Subject:
敖瑮牵[...snip...]
Blah Blah Blah

Any clues?
TIA
Brent

[quoted text, click to view]

kate
8/30/2005 3:49:27 PM

if you stop the local smtp server that you are sending thru and get the
raw email file from the pickup folder in mailroot, can you post what is
in the raw email file when you open it in text editor?

the only time we have seen this issue is when the body encoding has
been missed off and you clearly have that one.

Kate MBCS

Alert Technologies
http://www.alert-technologies.co.uk
Get your notification services implementation going in minutes not
weeks ......

kate
8/31/2005 5:05:44 AM

so the mail server has been given a 'To' of

To: bhorine at edited .fm

and it has looked up the address from this name. I assume it is getting
the 'To' field from the deviceaddress. eg in teh ADF

<Field>
<FieldName>To</FieldName>
<SqlExpression>DeviceAddress</SqlExpression>
</Field>

What does a sample subscriber device for this subscription look like ?
(see NSSubscriberDevices table in main) what is in the field
deviceaddress ?

Kate MBCS

Alert Technologies
http://www.alert-technologies.co.uk
Get your notification services implementation going in minutes not
weeks ......

Brent Horine
8/31/2005 7:30:42 AM

Thanks Kate.

If I stop the SMTP server, the notifications fail with a DeliveryStatusCode
of 4 and nothing appears in mailroot\pickup.

I sent the notification to an IMAP email account and saved it as a text file
from Outlook 2002.
From: alerts@edited.com
Sent: Wednesday, August 31, 2005 7:18 AM
To: bhorine at edited .fm
Subject:
敖瑮牵䝥卐匠数摥䄠敬瑲
Speed Limit Violation Alert Brent Horine was speeding at 115 MPH at
8/30/2005 2:58:00 PM at Over There! Speed Limit violation alert provided by
VentureGPS..

This was sent using the following configurations:
appConfig:
<DeliveryChannel>
<DeliveryChannelName>EmailChannel</DeliveryChannelName>
<ProtocolName>SMTP</ProtocolName>
<Arguments>
<Argument>
<Name>SmtpServer</Name>
<Value>localhost</Value>
</Argument>
<Argument>
<Name>BodyEncoding</Name>
<Value>utf-16</Value>
</Argument>
</Arguments>
</DeliveryChannel>

..xslt: (I've stripped out most of the html to try to reduce chance of
errors)
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="notifications">

Speed Limit Violation Alert
<xsl:apply-templates/>
Speed Limit violation alert provided by VentureGPS.
</xsl:template>

<xsl:template match="notification">
<xsl:value-of select="VehicleName" /> was speeding at <xsl:value-of
select="Speed" /> MPH
at <xsl:value-of select="EventDateTime" /> at <xsl:value-of
select="Address" />!
</xsl:template>

</xsl:stylesheet>

[quoted text, click to view]

Brent Horine
8/31/2005 9:22:02 AM

I included my ADF file in my first post. I have since removed the +
CONVERT(nvarchar(30), GETDATE()) from the subject line to try to get this to
work..

SELECT * FROM NSSubscriberDevices (actual email address modified to prevent
spam from newsgroup scavangers)
SubscriberId DeviceName DeviceTypeName DeviceAddress
DeliveryChannelName
Brent Horine Fastmail Account SMTP
bhorine_at_fastmail.fm EmailChannel
Brent Horine myDevice SMTP
bhorine_at_vehiclewatch.com EmailChannel

I just added a temp Yahoo account. Each one seems to behave differently,
presumably how the web app formats the page. Goto www.yahoo.com (you may
have to make sure you get directed to the US version) and click mail. Login
with id of bhorine2 and a password of tempaccount. You'll notice that the
body of the alert just consists of "<" but if you click the link "Save
Message Text" and read it in you see the message body but with spaces
between each letter. This is the case in Notepad with ANSI or utf-8
encoding. With Unicode you get funny characters again. I'm really
confused.

Thanks
Brent

[quoted text, click to view]

Andy
8/31/2005 9:57:09 AM

Stopping the service should not cause a problem, I do it all the time, but I
am running this on a Server not XP.

Have you tried to make your entry:
<Name>SmtpServer</Name>
<Value></Value>

By default it will look at localhost.

I don't think this would cause you a problem, but you may want to try and
set your settings like:
<ProtocolName>SMTP</ProtocolName>
<Arguments>
<Argument>
<Name>SmtpServer</Name>
<Value></Value>
</Argument>
<Argument>
<Name>BodyEncoding</Name>
<Value>utf-8</Value>
</Argument>
</Arguments>

Your XSL is declaring the utf-8, but you setting is utf-16 in the
instanceconfig, based on what you sent.

[quoted text, click to view]

Brent Horine
8/31/2005 10:54:06 AM

The change to utf-8 in the appConfig fixed it. Thanks Andy & Kate!
Brent

[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

home · blog · groups

Questions? Comments? Contact the dn